How to skip conditional formatting blank cells in Microsoft Excel
Conditional formatting is applied based on a single criteria. A rule will only check whether a cell’s value matches one condition and if it does, the formatting will be applied. Fortunately, you can apply multiple conditional formatting rules and you can use this to skip conditional formatting blank cells. Here’s how.
Skip conditional formatting blank cells
Open the Excel file that you’ve applied or intend to apply conditional formatting to. In the screenshot below, conditional formatting is applied to the C column so that all values that are less than 500 are highlighted in red.
Select the column, or rows that you intend to apply the conditional formatting to. Go to Conditional Formatting>Manage Rules.
Click the New Rule button in the rules manager and from the list of conditions, select ‘Format only cells that contain’ and select ‘Blank’ under the ‘Format only cells with’ dropdown. Click OK.
You will return to the rule manager window. The new rule you added will be listed there. Select the ‘Stop if true’ option. After that you can add additional formatting conditions. If you have other formatting condition rules already in the list, make sure the one you created for blank cells appears at the top.
To move it, select the rule and use the up arrow button to move it to the very top. Click Apply and all conditional formatting rules will skip blank cells within the column you selected.
There’s no condition on when you should add the rule to skip blank cells. You can add it before or after adding other rules. The only thing you need to make sure of is that the blank cell rule appears at the very top of the list and don’t forget to check the Stop if true option.
By default, this will keep empty cells free of all formatting however if you need the blank cells to be highlighted as well, you can give it a format when you create the rule. Click the ‘Format’ button next to the large ‘No Format Set’ box and select the fill color for the cell.
This is a great way to highlight empty fields in your data and to have them formatted as soon as they’re filled up. You can also use the order that the rules are applied in to apply more complex conditions and format cells accordingly e.g., you can apply a rule to highlight cells if their value is greater than a certain number, but less than another, or just find duplicate values.
