Conditional Formatting is the quickest and easiest way to add visual impact to any dataset but a Conditional Formatting Rule can be prone to its simplicity and might not be doing what you want it to do.
Our problem at hand, for now, is Conditional Formatting also highlighting blank cells when we would rather have them un-highlighted (and no one should be in the mood to do that manually. That's just wrong). But what's right is that there are two very simple solutions to this problem.
See this problem face to face:
We're taking an example of quality scoring (out of 20) of various products. The ones left blank are the scores we are yet to receive. To highlight which products had scored less than 10 exclusively, we applied a "less than 10" Conditional Formatting rule which also highlighted blanks.
Why has that happened? Conditional Formatting sees blanks as a value being less than 10. The rule is posed to highlight all cells that have a value of less than 10 and while 0 is right to be considered less than 10, we don't want the blanks to be highlighted because technically, those products aren't even scored yet. Hence, it would be nice not to have our attention flitting to blanks unnecessarily.
Blank cells can be ignored in a Conditional Formatting rule with the addition of another rule that will disregard blanks. The rule can use the preset option of formatting blanks or we can use a formula with the ISBLANK function, either works just as well. You will also need to bump up the rule with the preference of the Stop If True option applied. Find out more about that later.
Also note that you are to use either of the 2 methods for ignoring blank cells in this tutorial if you want the cells with the number 0 to be highlighted. We will also talk later about a couple of fixes if you also want to avoid highlighting the 0-value cells.
This is probably not a good lead anywhere else in life but…
Let's get ignoring!
Table of Contents
The position we're sitting at right now is the one shown above; with the "less than 10" rule applied which has also highlighted the blank cells. We'll carry that forward as our case example and see what we can do to fix the highlighting of blank cells.
Our first method makes use of the Format only cells that contain rule in Excel's Conditional Formatting feature. We can set this rule to format or deformat only blank cells with the Stop If True option. With this option selected for a Rule, the other Rules will be "stopped" if a condition in this Rule is met.
Conceptually with us trying to ignore blank cells, if Stop If True is activated for the Rule we will create to deformat blank cells, the other Less Than Rule will be stopped if a cell is blank, to make way for the deformatting first. Further details and steps for this method are given below:
The rule regarding blank cells will be applied:
As can be noted, our problem of highlighted blank cells has been solved! The explanation is that when Conditional Formatting will pick up on a blank cell, it will inhibit the highlighting of the "less than 10" rule. When a cell isn't blank, Conditional Formatting will continue with the less than rule.
The second method also involves creating a new rule but this time we're using a formula to stop blank cells from finding their way into the highlighting criteria with the ISBLANK function. The ISBLANK function returns TRUE if a cell is blank and FALSE if it isn't. Now let's test it in a Conditional Formatting rule to see if it does the job of ignoring blank cells.
We are again assuming that we applied the "less than 10" rule that has ended up with the blank cells highlighted. Follow the steps ahead to enter a new rule with the ISBLANK function to ignore blank cells in Conditional Formatting:
In the text box given in the Rule Description section, copy and paste this formula:
= ISBLANK (C3)The ISBLANK function will deformat the highlighted blank cells. That is because we have not set a format for the blank cells in the new rule. This rule will work to change the blank cells to this unhighlighted format, consequently deformatting the blank cells.
Use the ISBLANK function with a relative cell reference and keep the rule free from added formatting.
And we should have the blank cells clear of Conditional Formatting highlights:
That's all well and good but are you detecting a potential problem here? While we got Conditional Formatting to overlook blank cells, let's now suppose we don't require cells containing the number 0 to be highlighted either. That is because some users have entered unscored products as 0 and some have left them blank. There's an easy way to mend this so here's our suggestion.
You can set up a rule to deformat the cells that carry zero value, which will simultaneously work on blank cells too. The reason is that if it's zero itself that you want to leave out, blank cells, that Excel considers as zero will also be left out. To demonstrate the suggestion, let's snap back to our starting point of today; highlighted blank cells and 0 value cells with the "less than 10" rule applied to the range. Carry on with these steps:
You will find that the 0 value and blank cells have been ignored in the highlighting rule:
Tip: If applicable to your data, you can also edit the existing "less than 10" rule into highlighting values between 1 and 9 if you want to ignore blank and zero-value cells. This change will highlight numbers less than 10 and those greater than and equal to 1.
We're done with the ignoring part for today now that we're sure you've gotten a well enough idea of leaving out blank cells in Conditional Formatting. And even cells with 0 if required. While you're very busy with the ignoring part of things, we'll switch modes to enlightenment and hunt another Excel facet you won't be able to ignore!
