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.

Zero means the cost is 0 but empty means missing information.

I would like to display "N/A" in these cells.

I didn't find the way how to do it dynamically...

 

thanks in advance

Andor

excel
Formulas and Functions

5 Replies

  • 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 🙂

Resources