How to Highlight Blank Cells in Excel (In 4 Easy Ways)

You're probably thinking that it is no big deal or maybe you're not, since you're here reading this guide. But of course, when the task is to highlight blank cells amidst hundreds of rows of a dataset, no one’s laughing now. The Excel gods like your smile and have shined down some easy peasy Excel tricks to sift and highlight blank cells from your data in no time.

In this tutorial, you will learn about "how to highlight blank cells in Excel using Conditional Formatting, filters, the Go To Special feature, and VBA Macro.

Why would anyone need the blank cells highlighted? The blank cells can indicate missing or deleted data or an empty text string resulting from formulas. Blank cells need to be highlighted so they can either be filled or their pertaining data be deleted.

This brings us to an important point. Some cells appear blank but may not really be blank. Find out more below.

Difference between Blank Cells and Cells that Appear Blank

Blank cells are cells that don’t contain any data. Cells that contain a formula to return empty text (denoted by double-double quotes "" in formulas) or cells that contain a space character may appear blank but are not really blank. The problem with cells that appear blank is that some formulas and features will not treat them as blank cells. For our case, if the cells are not treated as blank, they won’t be highlighted.

The first two methods of highlighting cells in this guide will also work for cells that appear blank while the other two methods will not.

Method #1 – Using Conditional Formatting

Works for cells that are not blank but appear blank? Yes.

Conditional Formatting is a feature that formats cells according to cell values and as per the provided condition. There are various in-built conditions that you can make use of and there is also the alternative to set a custom condition. We will go with the latter route and specify our own condition to highlight the blank cells from the selected range. Find the complete steps below to highlight blank cells using Conditional Formatting:

• Select the range containing the blank cells.
• In the Home tab, select the Conditional Formatting button in the Styles section. From the menu, hover over Highlight Cells Rules to further expand the menu and select More Rules…

• The New Formatting Rule dialog box will open.
• Click on the drop-down arrow of the field reading Cell Value and select Blanks from the menu.

• The dialog box options will lessen to only formatting options. Click on the Format button to set the color fill for the highlighted cells.

• This will lead to a separate window for choosing the format. In the new Format Cells window, go to the Fill tab and select the color for highlighting the blank cells.
• Click on OK when done.

• The preview shows the selected color fill.
• Click on the OK button in the New Formatting Rule

Here are the highlighted blank cells using Conditional Formatting:

Pros: Being a dynamic feature, Conditional Formatting will automatically adjust with changes to the data. E.g. if you add a value to a blank cell, it would not remain highlighted. Likewise, if you delete a value, the cell will become blank and highlighted.

Cons: While Conditional Formatting is dynamic, it is also volatile; used on large amounts of data, it will slow the file down.

Method #2 – Using Filtering Feature

Works for cells that are not blank but appear blank? Yes.

This method of filtering to highlight blank cells works best for tabular data. We will use sorting filters on the dataset. The filter will bunch the blank cells from one column together which can then be selected and highlighted. If more than one column contains blank cells, you can take turns with the columns, filtering and highlighting the blank cells but if you do have more columns, the other methods of highlighting blank cells will be quicker for you.

As mentioned, this method works for cells that appear blank. In the example below, you can see from the formula bar that the cells are not truly blank as the formula is returning an empty text string.

• Select any cell in the dataset and then go to Home tab > Editing section > Sort & Filter button > Filter command or press the Ctrl + Shift + L keys to add columnar filters to the dataset.

• Here, the filters have been added to the dataset as shown from the little arrows in the headers:

• Click on the filter of the column containing the blank cells. Uncheck the Select All checkbox and select only the Blanks checkbox.

• Click on the OK. All the blank cells in the column will group up like so:

• Select all the blank cells that have been filtered and highlight them with a color of choice from the Fill Color

• Now it should look something like this:

• Press the Ctrl + Shift + L keys again to clear the filters. All the data will snap back into place, leaving the blank cells highlighted:

Method #3 – Using 'Go to Special' Feature

Works for cells that are not blank but appear blank? No.

This method utilizes a mechanical selection of the blank cells so they can be highlighted together. For the mechanical selection, the Go To Special feature will automatically select the blank cells from the dataset. After that, all you have to do is highlight the cells using color fill. Below are the steps to highlight blank cells by selecting them using Go To Special:

• Select the cells with the blanks.

• Press the Ctrl + G keys or the F5 key to launch the Go To dialog box.
• Select the Special button in the dialog box to open the Go To Special dialog box. (You can also find the Go To Special option in Home tab > Editing group, > Find & Select menu.)

• Select the Blanks radio button and then the OK

• From the selection of cells made in the first step, only the blank cells will remain in selection now.
• Use the Fill Color button in the Home tab > Font group to highlight the selected blank cells.

Be careful not to click anywhere else on the active worksheet; it will change the selection of the cells.

The blank cells will be highlighted/color-filled in the chosen color:

Method #4 – Using VBA

Works for cells that are not blank but appear blank? No.

For those with VBA as their right hand, you can also highlight blank cells with a VBA code. VBA allows user-defined functions to automate tasks in Microsoft applications. The task we will assign to VBA is highlighting the blank cells from the selected cells in the color yellow. This task will be assigned via a code that will be fed as a macro using the VB editor. These are the steps to highlight blank cells using VBA:

• Select the cells including the blank cells in the dataset.
• Select the Visual Basic button from the Code section in the Developer tab if you have the tab enabled or you can press the Alt + F11.
• This will open the Visual Basic (VB) editor. Here is what you’ll see when you open the VB editor:

• Click on the Insert tab and select Module from the list to launch a Module

• Copy and paste the following code to the Module

Sub HighlightBlankCells()
Dim Dataset As Range
Set Dataset = Selection
Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbYellow
End Sub

This is the code we are using to highlight the blank cells in the dataset from the cells that are in selection. Like the rest of this guide, we have gone with yellow as our choice of color fill but you can edit the code to add your color preference.

• If you want to run the code right away, press the F5 key and close the VB editor and the cells will be highlighted.

OR

• For running the code later, close the VB editor now. Make sure you select the cells in the dataset before running the code. To run the code, you need to open the Macro. Click on the Macros button in the View tab’s Macro section or press the Alt + F8 keys.

• Select the relevant Macro and click on the Run command button.

The Run button will run the code (no surprises there). The blank cells from the selection of cells will be highlighted in the defined color:

Not that we’re blank on what to do next but this is the end of our tutorial. We quickly outlined and detailed easy methods of highlighting blank cells that you can come back and have a look at when you’re blank on how to highlight so many of them. There will be more teaching from us and here’s your chance to learn everything Excel at point-blank range, so you know you’re not filling all the blanks on your own. Head back when you’re drawing a blank on an Excel predicament! The End (of the tutorial and the saturation of blank puns).