Excel conditional formatting for blank cells

Why does conditional formatting highlight blank cells?

Summary: conditional formatting highlights blank cells because it makes no difference between blanks and zeros. More details follow below.

In the internal Excel system, a blank cell equals a zero value. So, when you create a conditional format for cells less than a certain number, say 20, blank cells get highlighted too (as 0 is less than 20, for empty cells the condition is TRUE).

Another example is highlighting dates less than today. In terms of Excel, any date is an integer greater than zero, meaning an empty cell is always less than today's day, so the condition is satisfied for blanks again.

Solution: Make a separate rule to stop conditional formatting if cell is blank or use a formula to ignore blank cells.

Why aren't blank cells highlighted with conditional formatting?

There may be different reasons for blanks not being formatted such as:

• There is the first-in priority rule that stops conditional formatting for empty cells.
• Your formula is not correct.
• Your cells are not absolutely empty.

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

For example, if a cell contains a zero-length string ("") returned by some other formula, that cell is not considered as blank:

Solution: If you want to highlight visually empty cells that contain zero-length strings, apply the preset conditional formatting for blanks or create a rule with one of these formulas.

How to highlight blank cells in Excel

Excel conditional formatting has a predefined rule for blanks that makes it really easy to highlight empty cells in any data set:

1. Select the range where you wish to highlight empty cells.
2. On the Home tab, in the Styles group, click Conditional Formatting > New Rule.
3. In the New Formatting Rule dialog box that opens, select the Format only cells that contain rule type, and then choose Blanks from the Format only cells with drop down:
4. Click the Format… button.
5. In the Format Cells dialog box, switch to the Fill tab, select the desired fill color, and click OK.
6. Click OK one more time to close the previous dialog window.

All the blank cells in the selected range will get highlighted:

Conditional formatting for blank cells with formula

To have more flexibility when highlighting blanks, you can set up your own rule based on a formula. The details steps to create such a rule are here: How to create conditional formatting with formula. Below, we will discuss the formulas themselves

To only highlight truly empty cells that contain absolutely nothing, use the ISBLANK function.

For the below dataset, the formula is:

=ISBLANK(B3)=TRUE

Or simply:

=ISBLANK(B3)

Where B3 is the upper-left cell of the selected range.

Please keep in mind that ISBLANK will return FALSE for cells containing empty strings (""), consequently such cells won't be highlighted. If that behavior is not want you want, then either:

Check for blank cells including zero-length strings:

=B3=""

Or check if the string length is equal to zero:

=LEN(B3)=0

Aside from conditional formatting, you can highlight blank cells in Excel using VBA.

Stop conditional formatting if cell is blank

This example shows how to exclude blank cells from conditional formatting by setting up a special rule for blanks.

Suppose you used an inbuilt rule to highlight cells between 0 and 99.99. The problem is that empty cells get highlighted too (as you remember, in Excel conditional formatting, a blank cell equals a zero value):

To prevent empty cells from being formatted, do the following:

1. Create a new conditional formatting rule for the target cells by clicking Conditional formatting > New Rule > Format only cells that contain > Blanks.
2. Click OK without setting any format.
3. Open the Rule Manager (Conditional Formatting > Manage Rules), make sure the "Blanks" rule is at the top of the list, and tick the Stop if true check box next to it.
4. Click OK to save the changes and close the dialog box.

The result is exactly as you would expect:

Conditional formatting formula to ignore blank cells

In case you already use a conditional formatting formula, then you do not really need to make a separate rule for blanks. Instead, you can add one more condition to your existing formula, namely:

• Ignore absolutely empty cells that contain nothing:

  NOT(ISBLANK(A1))

• Ignore visually blank cells including empty strings:

  A1<>""

Where A1 is the leftmost cell of your selected range.

In the dataset below, let's say you wish to highlight values less than 99.99. This can be done by creating a rule with this simple formula:

=$B2<99.99

To highlight values less than 99.99 ignoring empty cells, you can use the AND function with two logical tests:

=AND($B2<>"", $B2<99.99)

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

In this particular case, both formulas ignore cells with empty strings, as the second condition (<99.99) is FALSE for such cells.

If cell is blank highlight row

To highlight an entire row if a cell in a specific column is blank, you can use any of the formulas for blank cells. However, there are a couple of tricks you need to know:

• Apply the rule to a whole dataset, not just one column in which you search for blanks.
• In the formula, lock the column coordinate by using a mixed cell reference with an absolute column and relative row.

This might sound complicated on the surface, but it's a lot simpler when we look at an example.

In the sample dataset below, suppose you wish to highlight rows that have an empty cell in column E. To have it done, follow these steps:

1. Select your dataset (A3:E15 in this example).
2. On the Home tab, click Conditional formatting > New Rule > Use a formula to determine which cells to format.
3. In the Format values where this formula is true box, enter one of these formulas:

   To highlight absolutely empty cells:

   =ISBLANK($E3)

   To highlight blank cells including empty strings:

   =$E3=""

   Where $E3 is the upper cell in the key column that you want to check for blanks. Please notice that, in both formulas, we lock the column with the $ sign.

4. Click the Format button and choose the fill color you want.
5. Click OK twice to close both windows.

As a result, conditional formatting highlights a whole row if a cell in a specific column is empty.

Highlight row if cell is not blank

Excel conditional formatting to highlight the row if a cell in a particular column is not blank is done in this way:

1. Select your dataset.
2. On the Home tab, click Conditional formatting > New Rule > Use a formula to determine which cells to format.
3. In the Format values where this formula is true box, enter one of these formulas:

   To highlight non-empty cells that contain anything: value, formula, empty string, etc.

   =NOT(ISBLANK($E3))

   To highlight non-blanks excluding cells with empty strings:

   =$E3<>""

   Where $E3 is the topmost cell in the key column that is checked for non-blanks. Again, for the conditional formatting to work correctly, we lock the column with the $ sign.

4. Click the Format button, choose your favorite fill color, and then click OK.

As a result, an entire row gets highlighted if a cell in a specified column is not empty.

Excel conditional formatting for zeros but not blanks

By default, Excel conditional formatting does not distinguish between 0 and blank cell, which is really confusing in many situations. To resolve this predicament, there are two possible solutions:

• Create 2 rules: one for the blanks and the other for zero values.
• Create 1 rule that checks both conditions in a single formula.

You may also be interested in

• How to use conditional formatting in Excel
• Excel icon sets
• Excel data bars
• How to conditionally format dates in Excel
• How to change the row color based on a cell's value in Excel
• Change background color based on cell value