Sep
7

Excel conditional formatting for blank cells

by | updated on

Everything you need to know about conditional format for empty cells in Excel

As simple as it may sound, highlighting blank cells with conditional formatting is quite a tricky thing. Basically, it's because a human understanding of empty cells does not always correspond to that of Excel. As a result, blank cells may get formatted when they shouldn't and vice versa. This tutorial will take a close look at various scenarios, share some useful bits on what is happening behind the scenes and show how to make conditional format for blanks work exactly the way you want.

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. Conditional formatting highlights blank cells.

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: Blank cells aren't highlighted with conditional formatting.

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. Creating a new conditional formatting 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: Choose to format only blank cells.
  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. Select the desired fill color.
  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 to highlight empty cells.

Tip. To highlight non-empty cells, select Format only cells that contain > No blanks.

Note. The inbuilt conditional formatting for blanks also highlights cells with zero-length strings (""). If you only want to highlight absolutely empty cells, then create a custom rule with the ISBLANK formula as shown in the next example.

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 Excel conditional formatting for blank cells with formula

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): Conditional formatting highlights blank cells.

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. Create a conditional formatting rule for empty cells.
  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. Stop conditional formatting if cell is blank.

The result is exactly as you would expect: Empty cells are not formatted.

Tip. You can also exclude blanks by creating a conditional formatting rule with a formula that checks for blank cells and selecting the Stop if true option for it.

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. Conditional formatting formula to ignore blank 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. Conditional formatting to highlight the row if a certain cell 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. 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.

Make separate rules for blanks and zeros

  1. First, create a rule to highlight zero values. For this, click Conditional Formatting > New Rule > Format only cells that contain, and then set Cell value equal to 0 like shown on the screenshot below. Click the Format button and select the desired color.

    This conditional formatting applies if a cell is blank or zero: Conditional formatting if a cell is blank or zero

  2. Make a rule for blanks with no format set. Then, open the Rule Manager, move the "Blanks" rule to the top of the list (if it isn't already there), and tick the Stop if true check box next to it. For the detailed instructions, please see How to stop conditional formatting on blank cells.

As a result, your conditional formatting will include zeros but ignore blanks. As soon as the first condition is met (the cell is empty), the second condition (the cell is zero) is never tested. Conditional formatting rules to highlight zeros but not blanks

Make a single rule to check if cell is zero, not blank

Another way to conditionally format 0's but not blanks is to create a rule with a formula that checks both conditions:

=AND(B3=0, B3<>"")

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

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

The result is exactly the same as with the previous method - conditional formatting highlights zeros but ignores empty cells. Conditional formatting formula to highlight zeros and ignore blank cells.

That's how to use conditional format for blank cells. I thank you for reading and look forward to seeing you next week.

Practice workbook for download

Excel conditional formatting for blank cells - examples (.xlsx file)

Table of contents