Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

How to Highlight Blank Cells in Excel

It’s hard to recognize blank cells in a large data. Because a blank cell is just a white cell without any value.

In most of the cases, blank cells represent some sort of gap, missing data, or a formula returns an empty string resulting in a blank cell.

And that’s why it’s recommended to highlight these cells with a color so that we can recognize them.

You might be wondering:

Which is the best method to find and highlight cells that are blank?

But here’s the kicker:

It depends on you as there are more than one ways for this and you can use any of these ways to get your work done.

And, all of these methods are unique.

So today, in this post, I’d like to share with you 3 different methods which you can use to highlight blank cell in Excel.

So let’s get started. 

highlight blank cells data table

1. Select and Highlight Blank Cells with GoTo Special

If you want to apply color to all the blank cells from a range, go to special option can be an easy to use way.

This method is simple ➤ select all the blank cells and apply cell color. Yes, that’s it.

Here are the steps.

  • First of all, select your entire data.
how to highlight blank cells select data
  • Go to Home Tab -> Find & Select -> Click on “Go To Special”.
select goto to highlight blank cells in excel
  • From Go To Special window, select “Blank” and click OK. (It will select all the blank cells from the table.)
select blank to highlight blank cells in excel
  • After that, go to Home Tab and apply the color to highlight the cells.
apply color to highlight blank cells in excel

Important Note: If you want to use a dynamic method, you apply conditional method, see the next method.

2. Apply Conditional Formatting to Highlight Blank Cells

  • First of all, select the data table.
how to highlight blank cells select data
  • Go to -> Home tab -> Conditional Formatting.
how to highlight blank cells  goto home tab
  • In conditional formatting options, select “highlight cell rules” and click ” more rules”.
how to highlight blank cells select more rules
  • Now, from rule description select the “Blank” from the drop menu.
how to highlight blank cells  select blank from drop down
  • After that, click on Format button to open formatting option.
how to highlight blank cells click format button
  • From formatting options, select the color you want to use for highlighting.
how to highlight blank cells click format button
  • Click OK.
use red color to highlight blank cells in excel

And, the best part is, if you have blank cells with a formula it will also highlight it and if you have pivot table instead of a normal table, the steps will be same.

Quick Tip: It’s a one-time setup, you don’t have to apply it again and again. Use a table in your data and conditional formatting will extend when you add new data to the table.

3. Using a VBA Code to Highlight Blank Cell

Conclusion

So these are the 3 methods which you can use to highlight blank cells. You can use any of these methods which you think is a perfect for you.

And, if you ask me I always prefer to use conditional formatting because it’s dynamic, so you don’t need to apply it again and again.

I hope these methods with help you in your daily work.

Now tell me one thing.

Do you have any other method to apply color to blank cells?

Please share with me in the comment section, I would love to hear from you. And, please don’t forget to share this tip with your friends.