It’s hard to recognize blank cells from a large data table.
Because a blank cell is just a white cell without any value.
In most of the cases, blank cells represent some sort of gap or missing data.
It’s recommended to highlight these cells with a color so that we can recognize them.
And, I found that conditional formatting is the best way to highlight blank cells.
It’s quick, easy, and simple.
But, if you use the manual method for highlighting blank cells, It could drive you nuts.
In this post, you will learn to highlight blank cells with conditional formatting. And, after that, I will show you that why using conditional formatting is the better way.
So let’s get started.
Steps To Highlight Blank Cells With Conditional Formatting
We have this data table with monthly production of products. And, there are some blank cells where we have no production.
Now, let’s say we want to highlight these blank cells.
Here are the steps.
- Select your data table.
- Go to -> Home tab -> Conditional Formatting.
- In conditional formatting options, select “highlight cell rules” and click ” more rules”.
- Now, from rule description select the “Blank” from the drop menu.
- After that, click on format button to open formatting option.
- From formatting options, select the color you want to use for highlighting.
- Click OK.
This will highlight all the blank cells in the data table.
And, the best part is, if you have blank cells with a formula it also will highlight it.
So you don’t have to use different formulas in conditional formatting.
One more thing, if you have pivot table instead of a normal table, the step will be same.
Just make sure to select the third option to from “apply the rule to”.
This will apply formatting to the entire pivot table.
Learn more about using conditional formatting in pivot tables.
Why Use Conditional Formatting?
No. 1 reason I have to use conditional formatting, It’s dynamic.
It’s a one-time setup, you don’t have to apply color to blank cells again and again. You can also use go to special to select blank cells.
But the thing is conditional formatting is all dynamic. And, if you have formulas, it will work with no problem.
I believe using conditional formatting to highlight blank cells is the smart way. You can save you a ton of time. And, you don’t have to do it again and again.
I hope you found this tip useful.
But tell me one thing. Have you ever used this method before? Or you are using any other method. Please share with me in the comment box.