Learn how to highlight blank cells in Excel if you often generate incorrect data analytics and insights based on datasets that contain interspersed blank cells or zero-length strings.

When you download a large dataset from a public database or get a database from a collaborator, you must audit it for blank cells or cells containing zero-length strings. It’s highly probable to find a few blank cells in managed or unmanaged datasets, especially downloaded from a CRM.

When the worksheet is tiny, you might be able to easily locate the blank cells by taking a closer look. If the worksheet is gigantic, you’ve got no way but to use semi-automatic and programmatic methods to select blank cells.

Find below several proven and common methods to achieve this challenging task.

Using Go To Special

This is the most common and easy-to-use method to select all the blank cells in a worksheet. The tool will select all the zero-value cells whether contiguous or noncontiguous. It gives the same result as you manually select blank cells one after another while pressing the Ctrl button on the keyboard.

On the worksheet where you suspect there could be some blank cells, press Ctrl + G to bring up the Go To dialog box.

Click on the Special button to access the Go To Special dialog.

Select the radio button for Blanks.

Click OK to apply the settings.

Excel will instantly select all the blank cells in the active worksheet.

Go To Special will only select such cells. This tool doesn’t automatically highlight the blank cells.

To highlight these cells, click on the Fill Color bucket icon in the Font commands block of the Home tab.

Choose a color from the context menu you get.

Excel applies the fill color you’ve chosen instantly.

The downside of this method is that it’s a fully static technique. Once you have highlighted all the blank cells in a worksheet and later on someone deleted more values from the worksheet, the new blank cells won’t be automatically highlighted.

Using Find and Replace

Click anywhere on the target worksheet and press Ctrl + F to bring up the Find and Replace tool.

Go to the Find tab and click on the Options button to expand its configuration menu.

Do the following in the expanded dialog box:

• Find what: Keep it empty.
• Within: Choose Sheet to select blank cells in the active worksheet. Or, select Workbook to select all blank cells in the whole Excel file.
• Look in: Change it to Values.

Once you’re done configuring the Find and Replace tool as explained above, hit the Find All button.

The Find and Replace tool will list all the blank cells of the sheet or workbook in the expanded area below the tool.

At the bottom of the list of cell addresses, you’ll also see how many blank cells have been identified.

Click on any of the search results within the Find and Replace tool.

Press Ctrl + A to select all the results.

You’ll see that Excel has also selected the same cells on the worksheet.

Use the Fill Color tool to pick a color to highlight the blank cells from the Font commands block.

Using Sort & Filter

Suppose, you’d like to highlight all the blank cells in a column by rows. You can do that easily using the Sort & Filter tool.

Click on the column header of the dataset.

Press Ctrl + Shift + L to activate the Sort & Filter tool.

Click on the Filter drop-down arrow and uncheck Select All.

Now, checkmark Blanks in the Filter dialog and click OK to apply the changes.

Excel will filter the entire dataset and only show the blank cells in the rows of the selected column.

You can now select all these filtered blank cells and choose a highlight color from the Fill Color dialog box in the Home tab.

Now, if you clear the Filter command, you’ll see that Excel has highlighted the respective blank cells with the color you’ve chosen.

Using Conditional Formatting

All of the methods explained so far are static. You can highlight a batch of blank cells once. You save the file. When someone else work on the same workbook and new blank cells arise, those aren’t highlighted automatically.

You can use Conditional Formatting rules in Excel to create dynamic conditions that will automatically highlight zero-value cells within a selected cell range.

The only downside of this method is the dynamic action takes place within the specified cell range only. You can include new cell ranges to the existing condition to automatically highlight new blank cells. However, if new blank cells come up outside the rule area, the method won’t be effective.

Highlight All Blank Cells in a Range

Select the cell range you want to include in the Conditional Formatting rule.

Click on the Conditional Formatting drop-down menu in the Styles commands block of the Home tab.

Choose New Rule from the context menu.

On the New Formatting Rule dialog box, click on the Format only cells that contain rule.

Click on the Format only cells with drop-down and choose Blanks.

Click the Format button and choose the cell fill color from the Fill tab.

You’ve configured your Conditional Formatting rule. Click OK to apply the rule in the selected cell range.

You’ll see that Excel has highlighted the blank cells with the fill color you’ve selected from the Format Cells dialog.

Highlight Rows by Blank Cells

For instance, you’ve got the above dataset and you want to find blank cells and highlight the entire row below a specific column, Traffic, which is column C.

Select the whole dataset including the column header.

Open the New Formatting Rule dialog box from the Conditional Formatting drop-down in the Styles commands block.

Select the Use a formula to determine which cells to format option.

Enter the following formula into the Format values where… field:

=$C2="" 

Click on the Format button and choose the cell fill color you need.

Click OK on the New Formatting Rule dialog to apply the newly created rule to the selected column.

Now you see that Excel has highlighted the whole row for cells that are below column C and also empty.

Using Excel VBA

You can use VBA in Excel to select and highlight blank cells in various ways by executing simple VBA macros. You can export the automation to other workbooks as well.

If you already know how to create a VBA macro using a script, continue with the scenarios mentioned below.

If you’re new to Excel VBA and don’t know how to create a VBA macro, read the following Excel tutorial first:

📒 Read More: How To Use The VBA Code You Find Online

Highlight Blank Cells in the Whole Worksheet

The following VBA script will let you highlight all blank cells in the entire worksheet:

Sub HighlightBlankCells()

    ' Define color choices
    Dim colorChoices As Variant
    colorChoices = Array("Red", "Blue", "Green", "Yellow", "Orange")

    ' Show input box with color choices
    Dim colorChoice As String
    colorChoice = InputBox("Please enter a color choice (Red, Blue, Green, Yellow, Orange):")

    ' Validate color choice
    If IsError(Application.Match(colorChoice, colorChoices, 0)) Then
        MsgBox "Invalid color choice. Please run the script again and choose a valid color."
        Exit Sub
    End If

    ' Define color codes
    Dim colorCodes As Variant
    colorCodes = Array(RGB(255, 0, 0), RGB(0, 0, 255), RGB(0, 255, 0), RGB(255, 255, 0), RGB(255, 165, 0))

    ' Get color code for chosen color
    Dim colorCode As Long
    colorCode = colorCodes(Application.Match(colorChoice, colorChoices, 0) - 1)

    ' Get reference to active sheet
    Dim ws As Worksheet
    Set ws = ActiveSheet

    ' Loop through each cell in active sheet
    Dim rng As Range
    For Each rng In ws.UsedRange
        ' If cell is blank, highlight it with chosen color
        If rng.Value = "" Then
            rng.Interior.Color = colorCode
        End If
    Next rng

End Sub

After creating the VBA macro HighlightBlankCells, press Alt + F8 to bet the Macro dialog box.

Click on the HighlightBlankCells macro and hit the Run button.

Excel will show an input box so you can choose a color coding for blank cell highlighting.

Once you choose the color code, Excel will highlight all the blank cells.

⚠️ Warning: Create a backup copy of your workbook before running any VBA scripts. When you make any changes to the worksheet using macros, you can’t use the Excel undo features to revert the worksheet to its previous state. All VBA-based changes are permanent.

Highlight Blank Cells in All Worksheets

If you want to highlight all the blank cells in the workbook, use this script:

Sub HighlightBlankCells()

    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim colorChoice As String

    ' Show an input box to choose a color
    colorChoice = InputBox("Enter a color from these choices: Red, Green, Blue, Yellow, Orange")

    ' Define color based on the choice
    Dim colorCode As Long
    Select Case colorChoice
        Case "Red"
            colorCode = RGB(255, 0, 0)
        Case "Green"
            colorCode = RGB(0, 255, 0)
        Case "Blue"
            colorCode = RGB(0, 0, 255)
        Case "Yellow"
            colorCode = RGB(255, 255, 0)
        Case "Orange"
            colorCode = RGB(255, 165, 0)
        Case Else
            MsgBox "Invalid color choice. Please run the script again and choose a valid color."
            Exit Sub
    End Select

    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        Set rng = ws.UsedRange

        ' Loop through each cell in the range
        For Each cell In rng
            ' If the cell is blank, highlight it with the chosen color
            If IsEmpty(cell) Then
                cell.Interior.Color = colorCode
            End If
        Next cell
    Next ws

End Sub

You’ll get an input box where you can choose the cell highlighting color when you execute this VBA macro.

Highlight Blank Cells in a Column

Use the following script to create a VBA macro that’ll highlight all the empty cells in the selected column:

Sub HighlightBlankCells()

    Dim rng As Range
    Dim cell As Range
    Dim colorIndex As Integer

    ' Get the selected range
    Set rng = Selection

    ' Show an input box to get the color choice
    colorIndex = InputBox("Enter a number from 1 to 5 for color choices:" & vbCrLf & _
                          "1 - Yellow" & vbCrLf & _
                          "2 - Green" & vbCrLf & _
                          "3 - Blue" & vbCrLf & _
                          "4 - Red" & vbCrLf & _
                          "5 - Orange", "Color Choices")

    ' Map the input to color index
    Select Case colorIndex
        Case 1
            colorIndex = 6 ' Yellow
        Case 2
            colorIndex = 4 ' Green
        Case 3
            colorIndex = 5 ' Blue
        Case 4
            colorIndex = 3 ' Red
        Case 5
            colorIndex = 46 ' Orange
        Case Else
            MsgBox "Invalid choice. Defaulting to Yellow."
            colorIndex = 6 ' Default to Yellow
    End Select

    ' Loop through each cell in the selected range
    For Each cell In rng
        ' If the cell is blank, highlight it
        If IsEmpty(cell) Then
            cell.Interior.ColorIndex = colorIndex
        End If
    Next cell

End Sub

First, select a column from your dataset.

Execute the macro.

You’ll get an input box to choose a cell fill color.

Excel highlights the blank cells instantly.

Highlight Blank Cells in Non-Contiguous Ranges

If you need to highlight blank cells in Excel for those cell ranges that are non-contiguous, use this script:

Sub HighlightBlankCells()
    Dim rng As Range
    Dim cell As Range
    Dim colorChoice As String
    Dim colorIndex As Integer
    Dim inputRanges As String
    Dim splitRanges() As String
    Dim i As Integer

    ' Prompt for cell ranges
    inputRanges = InputBox("Enter cell ranges separated by semicolons:", "Cell Ranges")
    splitRanges = Split(inputRanges, ";")

    ' Prompt for color choice
    colorChoice = InputBox("Choose a color from: Red, Green, Blue, Yellow, Orange", "Color Choice")

    ' Map color choice to color index
    Select Case colorChoice
        Case "Red"
            colorIndex = 3
        Case "Green"
            colorIndex = 4
        Case "Blue"
            colorIndex = 5
        Case "Yellow"
            colorIndex = 6
        Case "Orange"
            colorIndex = 46
        Case Else
            MsgBox "Invalid color choice. Please run the script again."
            Exit Sub
    End Select

    ' Loop through each range and highlight blank cells
    For i = 0 To UBound(splitRanges)
        Set rng = ThisWorkbook.Sheets(1).Range(splitRanges(i))
        For Each cell In rng
            If IsEmpty(cell) Then
                cell.Interior.colorIndex = colorIndex
            End If
        Next cell
    Next i

    MsgBox "Blank cells highlighted successfully!"
End Sub

When you execute this VBA macro, an input box asks you to enter non-contiguous cell ranges separated by semicolons. You can also enter contiguous cell ranges in the input box.

You’ll also see another input box to choose a cell fill color from 5 common choices.

Once you provide valid inputs in the above pop-up boxes, Excel locates and highlights blank cells in a flash.

Highlight Zero-Length Strings

Apostrophe (‘) and double quotation marks (“”) are zero-length strings. You might find these characters scattered among your dataset especially when you download the file from a third-party source like a POS software or CRM.

Use this script to create a VBA macro that accurately detects all such unwanted characters in Excel cells:

Sub HighlightZeroLengthStrings()

    Dim rng As Range
    Dim cell As Range
    Dim colorChoice As String

    ' Prompt the user to choose a color
    colorChoice = InputBox("Enter a color from the following options: Red, Green, Blue, Yellow, Cyan")

    ' Set the color index based on the user's choice
    Dim colorIndex As Integer
    Select Case colorChoice
        Case "Red"
            colorIndex = 3
        Case "Green"
            colorIndex = 4
        Case "Blue"
            colorIndex = 5
        Case "Yellow"
            colorIndex = 6
        Case "Cyan"
            colorIndex = 8
        Case Else
            MsgBox "Invalid color choice. Please run the script again and choose a valid color."
            Exit Sub
    End Select

    ' Set the range to the used range of the active sheet
    Set rng = ActiveSheet.UsedRange

    ' Loop through each cell in the range
    For Each cell In rng
        ' If the cell contains a zero-length string, highlight it
        If cell.Value = "" Then
            cell.Interior.colorIndex = colorIndex
        End If
    Next cell

End Sub

As soon as you execute the VBA macro, Excel asks you to enter the fill color code.

Find above an example showing highlighted blank cells that actually contain zero-length strings.

Conclusions

So far, you’ve learned how to highlight blank cells in Excel in different scenarios with various approaches. I’ve included methods suitable for Excel users of all levels.

You can use semi-automatic methods like the Go To or Find and Replace tools to highlight emnpty cells in small to medium length worksheets. You’ve also experienced how dynamic methods like Conditional Formatting works to identify and highlight blank cells.

Finally, I’ve introduced you to the fully automatic methods with single-click solution based on Excel VBA. I’ve simplified the VBA solutions in a way that you can use these codes even if you’ve never created a VBA macro before.

If the article helped or you want to share any critical feedback, don’t forget to comment.