How to highlight blank cells in Microsoft Excel ?

Introduction 

It’s hard to recognize blank cells in large data because a blank cell is just a white cell without any value. In most 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 is more than one way for this and you can use any of these ways to get your work done. All of these methods are unique.

So today, in this post, we’d like to share with you three different methods which you can use to highlight the blank cell in Microsoft Excel. So let’s get started.

Highlight Blank Cells with Go To Special

If you want to apply color to all the blank cells from a range, go to a 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. Below are the steps you need to follow to highlight all the blank cells.

• First of all, select your entire data.

• Go to Home Tab  ➤ Find & Select  ➤ Click on “Go To Special”.

• From Go To Special window, select “Blank” and click OK. (It will select all the blank cells from the table.)

• After that, go to the home tab and apply the color to highlight the cells.

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

Conditional Formatting to Highlight Blank Cells

• First of all, select the data table.

• Go to  ➤ Home tab  ➤ Conditional Formatting.

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

• Now, from the rule description select the “Blank” from the drop menu.
  
• After that, click on the “format” button to open the formatting option.

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

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

Note : 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.

VBA Code to Highlight Blank Cell

For your unconditional love for VBA, we have a macro code to apply color to all the blank cells from a selected range.

Sub ColorBlankCells()

Dim rng As Range

Set rng = Selection

rng.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed

End Sub

When you run this code it will check each cell from the range and then apply red color if a cell is blank.

Conclusion

So these are the three methods that you can use to highlight blank cells. You can use any of these methods which you think is perfect for you. The best method that we often use for our work is the conditional formatting method.