Forum Discussion

Legio5556's avatar
Legio5556
Copper Contributor
May 06, 2020

Custom format blank cell

Hi All   I would like to custom formatting a blank cell.  I know... 🙂 But there is some reason. The cost column contains positive numbers(sometimes negative numbers) and zero and empty cells. Z...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 06, 2020

Legio5556 

You could 'fake' it if you think it worthwhile.  Insert the formula

= IF( ISBLANK(numbers), "N/A", "" )

in the column immediately to the left of your numbers.  Then reduce the width of the column to one or two pixels (zero hides the content).  Increase the indent to move the displayed "N/A" into the blank cells of the numbers column.

 

  • mtarler's avatar
    mtarler
    Silver Contributor
    May 06, 2020
    PeterBartholomew1 I like the creativity. But do you even need the formula? why not just type " N/A" and fill down and make column narrow and then if there is a value to the right that cell gets clipped down to the " " and blank cells will show it.
    Legio5556 That all said wouldn't conditional formatting highlighting the blank cells be good enough instead of having the "N/A" text?
    • Legio5556's avatar
      Legio5556
      Copper Contributor
      May 11, 2020

      Hi mtarler 

      The aim is to keep blank because of further use but shown as "N/A".

      Certainly, everyone could list situations where 

      - "N/A" is not equal to number 0 or blank cell.

      - text by hand is not automatic solution.

      - function in a cell could be overwritten ...

       

      Thanks to all 🙂

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      May 07, 2020

      mtarler 

      I have no particular commitment to the idea but it did seem to achieve the goal.  Where the formula

      = IF(ISBLANK([@VALUE]), "  Missing value", "")

      worked better, was in the context of a Table since a formula expands to new rows.

       

      There were downsides; if you wish to use VLOOKUP, the formula is not obvious because the column count is not apparent.  Also any attempt by the user to resize the column to the left can create an unexpected result.

Resources