Excel conditional formatting for blank cells

Excel conditional formatting: a complete guide to empty cells

It seems simple, but using conditional formatting to highlight empty cells is quite tricky. The main reason is that people's understanding of empty cells is not always consistent with Excel's understanding. As a result, empty cells may be formatted incorrectly and vice versa. This tutorial will take a closer look at the scenarios, share some useful information about what is happening behind the scenes, and show how to make the conditional formatting of empty cells work exactly as you wish.

- Why does conditional formatting highlight empty cells?

• Why doesn't conditional formatting highlight empty cells?
• How to highlight empty cells
• Conditional formatting formula for empty cells
• If the cell is empty, stop conditional formatting
• Ignore empty cells using conditional formatting formula
• If the cell is empty, highlight rows
• If the cell is not empty, highlight rows
• Conditionally format 0, but not empty cells

Why does conditional formatting highlight empty cells?

Summary : Conditional formatting highlights empty cells because it does not distinguish between empty cells and zero-value cells. More details are as follows.

In Excel internal system, empty cells are equal to zero value . Therefore, when you create a conditional format for cells smaller than a certain number (such as 20), empty cells are also highlighted (because 0 is less than 20, for empty cells the condition is TRUE).

Another example is highlighting dates smaller than today. As far as Excel is concerned, any date is an integer greater than zero, which means that empty cells are always smaller than today's date, so the condition also holds for empty cells.

Solution : Create a separate rule to stop conditional formatting of empty cells, or use formulas to ignore empty cells.

Why doesn't conditional formatting highlight empty cells?

There may be several reasons why empty cells are not formatted:

• There are rules with higher priority that prevent conditional formatting of empty cells.
• Your formula is incorrect.
• Your cell is not absolutely empty.

If your conditional formatting formula uses the ISBLANK function, please note that it only recognizes truly empty cells , i.e. cells that absolutely contain nothing: no spaces, tabs, carriage returns, empty strings, etc.

For example, if a cell contains a zero-length string ("") returned by another formula, the cell is not considered an empty cell:

Solution : If you want to highlight visually empty cells containing zero-length strings, apply a preset empty cell conditional format or create a rule using one of the following formulas.

How to highlight empty cells in Excel

Excel conditional formatting has a predefined empty cell rule that makes it easy to highlight empty cells in any dataset:

1. Select the area where you want the empty cells to be highlighted.
2. On the Start tab, in the Style group, click Conditional Format > New Rule.
3. In the New Format Rule dialog box that opens, select the Format Inclusions only rule type, and then select an empty cell from the Format Inclusions only drop-down menu:
4. Click the "Format..." button.
5. In the Format Cells dialog box, switch to the Fill tab, select the desired fill color, and then click OK.
6. Click OK again to close the previous dialog window.

All empty cells in the selected area will be highlighted:

hint. To highlight non-empty cells , select Format Include Only > Non-empty cells.

Notice. The built-in empty cell conditional formatting also highlights cells containing zero-length strings (""). If you just want to highlight absolutely empty cells, you can create custom rules using the ISBLANK formula as shown in the following example.

Conditional formatting of empty cells using formulas

For greater flexibility when highlighting empty cells, you can set your own rules based on formulas. The detailed steps to create such rules are as follows: How to create a conditional format using formulas. Below, we will discuss the formula itself.

To highlight only truly empty cells (cells that absolutely do not contain anything), use the ISBLANK function.

For the following dataset, the formula is:

=ISBLANK(B3)=TRUE

Or simplify it to:

=ISBLANK(B3)

Where B3 is the upper left cell of the selected area.

Remember that for cells containing an empty string (""), ISBLANK will return FALSE, so those cells will not be highlighted. If this is not the behavior you want, then:

Check for empty cells including zero-length strings:

=B3=""

Or check if the string length is equal to zero:

=LEN(B3)=0

In addition to conditional formatting, you can also use VBA to highlight empty cells in Excel.

If the cell is empty, stop conditional formatting

This example demonstrates how to exclude empty cells from conditional formatting by setting special rules for empty cells.

Suppose you use built-in rules to highlight cells between 0 and 99.99. The problem is that empty cells are also highlighted (as you remember, in Excel conditional formatting, empty cells equal to zero value):

To prevent empty cells from being formatted:

1. Create a new conditional formatting rule for the target cell by clicking Conditional Format > New Rule > Format Include Only > Empty Cells .
2. Click OK without formatting.
3. Open Rule Manager (Conditional Format > Manage Rules), make sure the Empty Cell rule is at the top of the list, and select the Stop if true check box next to it. For detailed instructions, see How to stop conditional formatting of empty cells.
4. Click OK to save the changes and close the dialog box.

The results are exactly what you expect:

hint:

• You can also exclude empty cells by creating a conditional formatting rule that uses the formula that checks for empty cells and selecting the "Stop if true" option for it.
• You may also be interested in watching a video that shows how to apply conditional formatting when another cell is empty.

Conditional formatting formulas used to ignore empty cells

If you've already used conditional formatting formulas, you don't actually need to create separate rules for empty cells. Instead, you can add another condition to an existing formula, i.e.:

• Ignore absolutely empty cells (cells that do not contain anything): NOT(ISBLANK(A1))
• Ignore visually empty cells including empty strings: A1""

Where A1 is the leftmost cell of the selected area.

In the dataset below, assume that you want to highlight values ​​less than 99.99. This can be done by creating rules using the following simple formula:

=$B2

To highlight values ​​less than 99.99 and ignore empty cells, you can use the AND function to perform two logical tests:

=AND($B2"", $B2

=AND(NOT(ISBLANK($B2)), $B2

In this case, both formulas ignore cells containing empty strings because the second condition (

If the cell is empty, highlight rows

To highlight an entire row where cells in a specific column are empty, you can use any empty cell formula. However, there are some tips you need to know:

• Apply rules to the entire dataset , not just a column in which you are searching for empty cells.
• In the formula, lock the column coordinates by using a mixed cell reference with absolute columns and relative rows.

This may sound complicated on the surface, but when we look at the examples, it will be much simpler.

In the example dataset below, assume that you want to highlight rows with empty cells in column E. To do this, follow these steps:

1. Select your dataset (A3:E15 in this case).

2. On the Start tab, click Conditional Format > New Rule > Use formulas to determine the cell to format.

3. In the Format Values ​​When This Formula is True box, enter one of the following formulas: To highlight an absolutely empty cell :

   =ISBLANK($E3)

   To highlight empty cells including empty strings :

   =$E3=""

   where $E3 is the top cell in the key column to check for empty cells. Note that in both formulas we locked the column with the $ symbol.

4. Click the Format button and select the fill color you want.

5. Click OK twice to close both windows.

As a result, if the cells in a specific column are empty, conditional formatting highlights the entire row.

If the cell is not empty, highlight rows

Excel conditional formatting, if the cell in a specific column is not empty, the row is highlighted as follows:

1. Select your dataset.

2. On the Start tab, click Conditional Format > New Rule > Use formulas to determine the cell to format.

3. In the Format Values ​​When This Formula is True box, enter one of the following formulas: To highlight a non-empty cell containing anything (value, formula, empty string, etc.):

   =NOT(ISBLANK($E3))

   To highlight non-empty cells that do not contain empty strings :

   =$E3""

   where $E3 is the topmost cell in the key column that checks for non-empty cells. Again, to make the conditional format work correctly, we locked the column with the $ symbol.

4. Click the Format button, select the fill color you like, and then click OK.

As a result, if the cells in the specified column are not empty, the entire row will be highlighted.

Excel conditional formatting: format 0, but not empty cells

By default, Excel conditional formatting does not distinguish between 0 and empty cells, which is really confusing in many cases. To solve this problem, there are two possible solutions:

• Create 2 rules: one for empty cells and the other for zero-value cells.
• Create 1 rule to check these two conditions in a single formula.

Create separate rules for empty and zero-value cells

1. First, create a rule to highlight the zero value. To do this, click Conditional Format > New Rule > Format Inclusion Only and set Cell Value equal to 0 as shown in the following figure. Click the Format button and select the desired color. This conditional format applies if the cell is empty or zero :

   

2. Create an empty cell rule that does not format. Then, open Rule Manager, move the Empty Cell rule to the top of the list (if it is not already at the top), and select the Stop if true check box next to it. For detailed instructions, see How to stop conditional formatting of empty cells.

As a result, your conditional formatting will include zero values, but ignore empty cells . Once the first condition is met (cell is empty), the second condition (cell is zero) will never be tested.

Create a single rule to check if a cell is 0, not an empty cell

Another way to conditionally format 0 but not include empty cells is to create a rule that uses formulas to check these two conditions:

=AND(B3=0, B3"")

=AND(B3=0, LEN(B3)>0)

Where B3 is the upper left cell of the selected area.

The result is exactly the same as before - conditional formatting highlights zero values, but ignores empty cells.

This is how to use the conditional formatting of empty cells. Thank you for reading and look forward to meeting you again next week.

Exercise workbook download

Excel empty cell conditional formatting - Example (.xlsx file)

The above is the detailed content of Excel conditional formatting for blank cells. For more information, please follow other related articles on the PHP Chinese website!