To ignore blank cells when applying conditional formatting in Excel, you can use the Manage Rules option under the Conditional Formatting dropdown menu within the Home tab.
The following example shows how to use this option in practice.
Example: How to Ignore Blank Cells in Conditional Formatting in Excel
Suppose we set up a conditional formatting rule in Excel to highlight all values in the range B2:B11 that have a value less than 20:
Notice that the value in cell B10 is also highlighted in green even though it contains a blank cell.
To ignore this blank cell when using conditional formatting, we need to add a new rule that applies no conditional formatting to blank cells.
To do so, highlight the range B2:B11, then click the Conditional Formatting dropdown menu on the Home tab, then click Manage Rules:
In the new window that appears, click the New Rule button:
Then click Format only cells that contain, then choose Blanks, and leave the formatting blank:
Once you click OK, the Conditional Formatting Rules Manager will appear where you can see all of the conditional formatting rules.
Check the box next to Stop If True for the first rule:
Then click OK.
You’ll notice that the blank value is no longer highlighted in cell B10:
By creating this new rule, we are able to apply no conditional formatting to blank cells and ignore all other conditional formatting rules since we checked the box next to Stop if True.
The following tutorials explain how to perform other common operations in Excel: