How to select and highlight blank cells in Excel

Svetlana Cheusheva by , updated on

The article shows how to find and highlight blanks in Excel with the help of conditional formatting and VBA. Depending on your needs, you can color only truly blank cells or those that contain zero-length strings as well.

When you receive an Excel file from someone or import it from an external database, it's always a good idea to check the data to make sure there are no gaps or missing data points. In a small dataset, you can easily spot all the blanks with your own eyes. But if you have a huge file containing hundreds or even thousands of rows, pinpointing empty cells manually is next to impossible.

This tutorial will teach you 4 quick and easy ways to highlight blank cells in Excel so that you can visually identify them. Which method is the best? Well, that depends on the data structure, your goals and your definition of "blanks".

Select and highlight empty cells with Go To Special

This simple method selects all blank cells in a given range, which you can then fill with any color of your choosing.

To select blank cells in Excel, this is what you need to do:

  1. Select the range where you want to highlight blank. To select all cells with data, click the upper-left cell and press Ctrl + Shift + End to extend the selection to the last used cell.
  2. On the Home tab, in the Editing group, click Find & Select > Go to Special. Or press F5 and click Special… .
    Go To Special in Excel
  3. In the Go To Special dialog box, select Blanks and click OK. This will select all empty cells in the range.
    Selecting blank cells in Excel
  4. With the blank cells selected, click the Fill Color icon on the Home tab, in the Font group, and pick the desired color. Done!
    Fill the selected blanks with the desired color.

Tips and notes:

  • The Go To Special feature only selects truly blank cells, i.e. cells that contain absolutely nothing. Cells containing empty string, spaces, carriage returns, non-printing characters, etc. are not considered blank and are not selected. To highlight cells with formulas that return an empty string ("") as the result, use either Conditional Formatting or VBA macro.
  • This method is static and is best to be used as a one-time solution. Changes that you make later won't be reflected automatically: new blanks won't be highlighted and former blanks that you fill with values will stay colored. If you are looking for a dynamic solution, you'd better use the Conditional Formatting approach.

Filter and highlight blanks in a specific column

If you do not care about empty cells anywhere in the table but rather want to find and highlight cells or the entire rows that have blanks in a certain column, Excel Filter can be the right solution.

To have it done, carry out these steps:

  1. Select any cell within your dataset and click Sort & Filter > Filter on the Home tab. Or press the CTRL + Shift + L shortcut to turn on auto-filters.
  2. Click the drop-down arrow for the target column and filter blank values. For this, clear the Select All box, and then select (Blanks).
  3. Select the filtered cells in the key column or entire rows and choose the Fill color that you want to apply.

In our sample table, this is how we can filter, and then highlight the rows where the SKU cells are empty:
Filter and highlight blank cells in a specific column.

Notes:

  • Unlike the previous method, this approach regards formulas that return empty strings ("") as blank cells.
  • This solution is not suitable for frequently changed data because you would have to clean up and highlight again with each change.

How to highlight blank cells in Excel with conditional formatting

Both techniques discussed earlier are straightforward and concise, but they do have a significant drawback - neither method reacts to changes made to the dataset. Unlike them, Conditional Formatting is a dynamic solution, meaning you need to set up the rule just once. As soon as an empty cell is populated with any value, the color will immediately go away. And conversely, once a new blank appears, it will get highlighted automatically.

Example 1. Highlight all blank cells in a range

To highlight all empty cells in a given range, configure the Excel conditional formatting rule in this way:

  1. Select the range in which you want to highlight blank cells (A2:E6 in our case).
  2. On the Home tab, in the Styles group, click New Rule > Use a formula to determine which cells to format.
  3. In the Format values where this formula is true box, enter one of the below formulas, where A2 is the upper-left cell of the selected range:

    To highlight absolutely blank cells that contain nothing:

    =ISBLANK(A2)

    To also highlight seemingly blank cells that contain zero-length strings ("") returned by your formulas:

    =LEN(A2)=0

    or

    =A2=""

  4. Click the Format button, switch to the Fill tab, choose the background color you want and click OK.
  5. Click OK to save the rule and close the main dialog window.
    Conditional formatting rule to highlight blank cells in Excel.

For the detailed steps, please see Create a formula-based conditional formatting rule in Excel.

Example 2. Highlight rows that have blanks in a specific column

In situation when you want to highlight the entire rows that have empty cells in a particular column, just make a small change in the formulas discussed above so that they refer to the cell in that specific column, and be sure to lock the column coordinate with the $ sign.

For example, to highlight rows with blanks in column B, select the whole table without column headers (A2:E6 in this example) and create a rule with one of these formulas:

To highlight absolutely blank cells:

=ISBLANK($B2)

To highlight blanks and cells containing empty strings:

=LEN($B2)=0

or

=$B2=""

As the result, only the rows where an SKU cell is empty are highlighted:
Highlight rows with blanks in a specific column.

For more information, please see Excel conditional formatting for blank cells.

Highlight if blank with VBA

If you are fond of automating things, you may find useful the following VBA codes to color empty cells in Excel.

Macro 1: Color blank cells

This macro can help you highlight truly blank cells that contain absolutely nothing.

To color all empty cells in a selected range, you need just a single line of code:

Sub Highlight_Blank_Cells() Selection.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 181, 106) End Sub

To highlight blanks in a predefined worksheet and range (range A2:E6 on Sheet 1 in the below example), this is the code to use:

Sub Highlight_Blank_Cells() Dim rng As Range Set rng = Sheet1.Range("A2:E6") rng.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 181, 106) End Sub

Instead of an RGB color, you can apply one of the 8 main base colors by typing "vb" before the color name, for example:

Selection.SpecialCells(xlCellTypeBlanks).Interior.Color = vbBlue

Or you can specify the color index such as:

Selection.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6

Macro 2: Color blanks and empty strings

To recognize visually blank cells containing formulas that return empty strings as blanks, check if the Text property of each cell in the selected range = "", and if TRUE, then apply the color.

Here's the code to highlight all blanks and empty strings in a selected range:

Sub Highlight_Blanks_Empty_Strings() Dim rng As Range Set rng = Selection For Each cell In rng If cell.Text = "" Then cell.Interior.Color = RGB(255, 181, 106) Else cell.Interior.ColorIndex = xlNone End If Next End Sub

How to insert and run macro

To add a macro to your workbook, carry out these steps:

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. In the Project Explorer on the left, right-click the target workbook, and then click Insert > Module.
  3. In the Code window on the right, paste the VBA code.

To run the macro, this is what you need to do:

  1. Select the range in your worksheet.
  2. Press Alt + F8 to open the Macro dialog.
  3. Chose the macro and click Run.
    Running a macro to highlight blank cells in Excel

For the detailed step-by-step instructions, please see:

That's how to find, select and highlight blank cells in Excel. I thank you for reading and hope to see you on our blog next week!

Available downloads

Highlight blanks with Conditional Formatting (.xlsx file)
VBA macros to color empty cells (.xlsm file)

Table of contents