How to Highlight Blank Cells in Excel (in less than 10 seconds)

Watch Video – How to Highlight Blank Cells in Excel

When I get a data file from a client/colleague or I download it from a database, I do some basic checks on the data. I do this to make sure there are no missing data points, errors or duplicates that may lead to issues later.

One such check is to find and highlight blank cells in Excel.

There are many reasons that can result in blank cells in a dataset:

  • The data is not available.
  • Data points accidentally got deleted.
  • A formula returns an empty string resulting in a blank cell.

While it’s easy to spot these blank cells in a small dataset, if you have a huge one with hundreds of rows and columns, doing this manually would be highly inefficient and error prone.

In this tutorial, I will show you various ways to find and highlight blank cells in Excel.

Highlight Blank Cells Using Conditional Formatting

Conditional Formatting is a great way to highlight cells based on its value when the given condition is met.

I am going to showcase these examples with a small dataset. However, you can use these same techniques with large datasets as well.

Suppose you have a dataset as shown below:

Highlight Blank Cells in Excel - Dataset

You can see there are blank cells in this dataset.

Here are the steps to highlight blank cells in Excel (using conditional formatting):

  • Select the data.Highlight Blank Cells in Excel - select data
  • Go to the Home tab.Highlight Blank Cells in Excel - Home
  • In the Conditional Formatting drop down, click on New Rule.Highlight Blank Cells in Excel - New Rule
  • In the ‘New Formatting Rules’ dialog box, select ‘Format only cells that contain’.Highlight Blank Cells in Excel - New formatting rule
  • Select ‘Blanks’ from the drop down (as shown below):Highlight Blank Cells in Excel - CF Blanks
  • Specify the formatting (in which you want to highlight blanks).Highlight Blank Cells in Excel - format
  • Click OK.Highlight Blank Cells in Excel - OK

This would highlight all the blank cells in the dataset.

Highlight Blank Cells in Excel - highlighted

Note that conditional formatting is dynamic. This means that if conditional formatting is applied and you delete a data point, that cell would get highlighted automatically.

At the same time, this dynamic behavior comes with an overhead cost. Conditional Formatting is volatile, and if used on large data sets, may slow down your workbook.

Select and Highlight Blank Cells in Excel

If you want to quickly select and highlight cells that are blank, you can use the ‘Go to Special’ technique.

Here are the steps to select and highlight blank cells in Excel:

  •  Select the data.
  • Press the F5 key. It will open the Go To dialog box.
  • In the Go To dialog box, click on the Special button.Highlight Blank Cells in Excel - special
  • In the Go To Special dialog box, select Blanks.Highlight Blank Cells in Excel - GTS blanks
  • Click OK. This will select all the blank cells in the dataset.Highlight Blank Cells in Excel - GTS OK
  • With all the blank cells selected, highlight these by giving it a cell color.

As mentioned, this method is useful when you want to quickly select all the blank cells and highlight it. You can also use the same steps to select all the blank cells and then fill 0 or NA or any other relevant text in it.

Note that unlike conditional formatting, this method is not dynamic. If you do it once, and then by mistake delete a data point, it will not get highlighted.

Using VBA to Highlight Blank Cells in Excel

You can also use a short VBA code to highlight blank cells in a selected dataset.

This method is more suitable when you need to often find and highlight blank cells in data sets. You can easily use the code below and create an add-in or save it in your personal macro workbook.

Here is the VBA code that will highlight blank cells in the selected dataset:

'Code by Sumit Bansal (https://trumpexcel.com)
Sub HighlightBlankCells()
Dim Dataset As Range
Set Dataset = Selection
Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed
End Sub

Here are the steps to put this VBA code in the backend and then use it to highlight blank cells in Excel:

  • Go to the Developer tab and click on Visual Basic (or press ALT + F11).Highlight Blank Cells in Excel - VB
  • In the Vb Editor, within the Project Explorer, right-click on any of the sheet names (if you don’t see Project Explorer, press CONTROL + R).
  • Go to Insert and click on Module.Highlight Blank Cells in Excel - module
  • In the Module code window, copy and paste the VBA code.Highlight Blank Cells in Excel - code
  • Close the VB Editor.

How to Run the VBA code (Macro)?

Once you have copy pasted this macro, there are a couple of ways you can use this macro.

Using the Macro Dialog Box

Here are the steps to run this macro using the Macro dialog box:

  1. Select the data.
  2. Go to the Developer tab and click on Macros.Highlight Blank Cells in Excel - run - macros
  3. In the Macro dialog box, select the ‘HighlightBlankCells’ macro and click on Run.Macro dialog box to run macro

Using the VB Editor

Here are the steps to run this macro using the VB Editor:

  1. Select the data.
  2. Go to the Developer tab and click on Visual Basic.Highlight Blank Cells in Excel - Visual Basic
  3. In the VB Editor, click anywhere within the code.
  4. Click on the Green triangle button in the toolbar (or hit the F5 key).Highlight Blank Cells in Excel - play button

As I mentioned, using VBA macro to highlight blank cells is the way to go if you need to do this often. Apart from the ways shown above to run the macro, you can also create an add-in or save the code in the Personal macro workbook. This will allow you to access this code from any workbook in your system.

You May Also Like the Following Excel Tutorials:

  • Ogundepo Ezekiel Adebayo

    Wonderful. Thank you.