Empty cells and zero values

Use CONDITIONAL FORMATTING to display empty cells and zero values.

If you regularly import data from an external source (for instance SAP or Oracle), there may be empty cells or zero values in between. This distinction is often not easy to make, nor can you see it.With Conditional Formatting you can visualize whether the contents of a cell are empty or whether a cell contains a zero value. How does it work?

To indicate whether a cell is empty: Select the range of cells with values. In this example, that is D6:D10

    • From the Home tab choose > Styles > Conditional Formatting
    • Choose > New rule …
    • Choose > Use a formula to determine which cells to format
    • At Format values where this formula is true enter: =ISEMPTY(D6)
    • Click the Format … button to add a Layout Style
    • Click the OK button to add the new Formatting Rule

To indicate whether a cell contains a zero value: Select the range of cells with values. In this example, that is D6:D10

    • From the Home tab choose > Styles > Conditional Formatting
    • Choose > New rule …
    • Choose > Use a formula to determine which cells to format
    • At Format values where this formula is true enter: =D6=0
    • Click the Format … button to add a Layout Style
    • Click the OK button to add the new Formatting Rule

You now have two conditional formats in the form of a formula!

How it works.

When the content, or value from one of the cells in range D6:D10 is updated, Excel checks whether that content meets a condition. In these cases, Excel checks whether the cell is empty or whether the cell contains a value zero. If this is the case, a format, the so-called conditional format, is applied to that cell.

Two things are essential when checking a condition in the form of a formula!

First, the answer to the formula must be TRUE or FALSE. The first condition is whether cell D6 is empty. The formula reads =ISEMPTY(D6). The answer can only be YES (TRUE) or NO (FALSE).

Secondly, Excel checks the condition in its internal memory. The formula you enter relates to the first cell of the range, but because it is conditional formatting, Excel will check all of the cells in that range in the same way. This is expressed in the fact that you base the formula on cell D6. The formula reads =ISEMPTY(D6). Because you have selected range D6:D10, cells D7 to D10 are also included in the check!

 

Click here for a Dutch version of this tip.

Download workbook