how to highlight blank cells with conditional formattingIt’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.

how to highlight blank cells with conditional formatting data table

Now, let’s say we want to highlight these blank cells.

Here are the steps.

Download this file to follow along.

  • Select your data table.

how to highlight blank cells with conditional formatting select data

  • Go to -> Home tab -> Conditional Formatting.

how to highlight blank cells with conditional formatting goto home tab

  • In conditional formatting options, select “highlight cell rules” and click ” more rules”.

how to highlight blank cells with conditional formatting select more rules

  • Now, from rule description select the “Blank” from the drop menu.

how to highlight blank cells with conditional formatting select blank from drop down

  • After that, click on format button to open formatting option.

how to highlight blank cells with conditional formatting click format button

  • From formatting options, select the color you want to use for highlighting.

how to highlight blank cells with conditional formatting select color

  • 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.

how to highlight blank cells with conditional formatting with formula

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”.

how to highlight blank cells with conditional formatting in pivot table

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.

Sample File

how to highlight blank cells with conditional formatting

Last Words

I believe using conditional formatting to highlight blank cells is the smart way.

I can save you a ton of time.

And, you don’t have to do it again and again.

Over To You

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.