How to Highlight Blank Cells in Excel?

by

Many times, when you download a data set from the web or from a database, it would have blank cells in it.

It is often required to handle these blank cells by either highlighting them or replacing them with something more meaningful.

While it would be easy to do this manually with a small data set, in case you have hundreds and thousands of rows of data, doing this manually would not be feasible,

In this tutorial, I will show you a couple of simple methods you can use to quickly highlight blank cells in Excel.

3 Ways to Highlight Blank Cells in Excel

Let’s have a look at three ways to quickly highlight blank cells in Excel!

Note: While I will be using a small data set to show you how to highlight the blank cells in it, the same methods can also be used for any large data set

Highlight Blank Cell Using Conditional Formatting

Conditional formatting is a quick and easy way to quickly highlight empty cells in Excel.

Conditional formatting works by analyzing each cell against the specified condition, and if the specified condition is met, then the formatting is applied, else it’s not applied.

In this case, since we need to highlight blank cells, the condition we need to check would be whether the cell is blank/empty or not.

Below, I have a data set where I have some cells that are blank, and I want to highlight these using Conditional Formatting:

Dataset to highlight blank cells in Excel

Here are the steps to highlight blank cells using Conditional Formatting:

  1. Select the cells where you want to highlight the blank cells
  2. Click the ‘Home’ tab
Click the Home tab
  1. In the Styles group, click on ‘Conditional Formatting’
Click on Conditional Formatting
  1. In the options that show up in the drop-down, click on ‘New Rule’. This will open the ‘New Formatting Rule’ dialog box
Click on New rule
  1. Click on the ‘Use a formula to determine which cells to format’ option
Select Use a formula to determine which cells to format
  1. In the formula field, enter the below formula:
=B2=""
Enter the formula in the field
  1. Click on the Format button
Click the Format button
  1. In the Format Cells dialog box that opens up, select the ‘Fill’ tab
  2. Select the color with which you want to highlight the blank cells and click on Ok
Select the color with which you want to highlight empty cells
  1. Click OK
Click OK to apply the conditional formatting

The above steps would instantly highlight all the blank cells in the selected data set (as shown below).

Blank cells have been highlighted

Now let me quickly explain what happens here.

In step 6, we specified a formula that needs to be assessed for all the cells in the selection.

The formula (=B2=””) checks every cell in the selection and analyzes whether it’s blank or not (i.e., it does not have anything in it).

If this condition is met, then the Conditional Formatting that we specified (in Steps 8 and 9) would be applied, and in case the condition is not met, then nothing happens.

Note that while we have used the formula B2=””, Conditional Formatting will go through each cell and check for this condition.

Since we are using the relative cell reference (B2, without any dollar sign before B or 2), this reference is automatically adjusted as Conditional Formatting analyses different cells.

For example, when it’s checking cell B2, it uses the formula =B2=””, and when it’s checking cell B3, it automatically adjusts the formula and uses =B3=””

Here are a couple more things you need to know when using conditional formatting to highlight blank cells:

  • Conditional formatting is dynamic, and if you make any changes in the cells on which it is applied, it would recheck all the cells and change the formatting if required. For example, if you enter any data in any of the blank cells, these would stop being highlighted. Or if you delete the content of any of the cells, conditional formatting would highlight these
  • For this to work, the cell needs to be completely empty. In case you have space characters in the cell, although it may appear that it’s blank, conditional formatting would not consider it as blank, and hence it would not be highlighted
Also read: Highlight Cells with Formulas in Excel

Highlight Blank Cell Using Go To Special Cells

Another quick way to highlight blank cells in Excel is by using the ‘Go-To Special’ technique.

In this method, we use the ‘Go To Special’ functionality to first select all the blank cells in the data set.

Once all the blank cells are selected, you can easily highlight them using the fill color option in Excel.

Below I have a data set where I want to highlight all the blank cells in column B.

Dataset to highlight blank cells in Excel

Here are the steps to do this:

  1. Select all the cells where you want to highlight the blank cells
  2. Press the F5 key on your keyboard. This will open the Go-To dialog box (you can also do this using Home –> Find & Select –> Go to)
  3. Click on the ‘Special’ button
Click the Special button
  1. In the ‘Go To Special’ dialog box that opens up, select the ‘Blanks’ option
Select Blanks
  1. Click Ok. This will select all the blank cells in the selected range
Blank cells in the dataset are selected
  1. Click the Home tab and use the Fill Color option to highlight these selected blank cells
Select the color to highlight blank cells

Note that, unlike the Conditional Formatting method, this method is not dynamic.

This means that if you make any changes in the original data set (i.e., you feel any of the blank cells or you delete the content of any cell and make it empty), you will have to repeat the process to highlight blank cells again.

In this example, we used the Go To Special technique to select blank cells and then highlight these. Once the blank cells are selected, you can also do other things such as fill some text or 0 or dash in it, or delete rows that have the blank cells.

Also read: How to Highlight Dates Before Today in Excel?

Highlight Blank Cell Using VBA

And finally, you can use a simple VBA code to quickly highlight all the blank cells in the selected range.

While we would still be referring to the ‘Go-To Special’ option in Excel to select and highlight the blank cells, with VBA you can do this with a single click (if you add the VBA code to the Quick Access Toolbar) or run it using a keyboard shortcut.

So if you need to quickly highlight blank cells often in your work, it would be a good idea to create the VBA code and add it to your quick access toolbar (so that it can be easily reused)

Below is the VBA code that would highlight all the blank cells in the selected range.

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub Highlight_Blank_Cells()
Selection.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed
End Sub

In the above code, I have used VBRed to highlight all the empty cells in red color. You can specify any other inbuilt color code of VBA (such as VBGreen or VBYellow, or even the RGB notation).

Now there are multiple ways you can run this code in your workbook.

You can add this code to a new module, and then run this code from there, in which case it would only be available in the workbook where this code has been added.

Alternatively, if you need to use this code quite often, you can save this in your personal macro workbook, where it would become available on all the Excel files on your system.

Personally, since I need to use this quite often, I have kept it in my Personal Macro Workbook, and have also added this macro to my Quick Access Toolbar.

This way, I have access to this code in any workbook that I opened on my system, and I can quickly highlight all the blank cells by simply selecting the data and clicking on the macro icon in the Quick Access Toolbar.

Here is a detailed guide on how to add any VBA code to your Personal Macro Workbook.

And here is a detailed guide on how to add macros to the Quick Access Toolbar, so that you can run it with a single click.

So these are three easy ways that you can use to quickly highlight blank cells in Excel.

You can use Conditional Formatting in case you want the highlighting to be dynamic (so that it updates in case there are any changes in the original data set) or you can use the Go To special method or the VBA code if you want to quickly highlight blank cells just once.

I hope I was able to answer your question with this tutorial and that it was helpful.

Other Excel tutorials you may also find helpful:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment