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:
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.