How to Highlight Blank Cells in Excel (4 Fruitful Ways)

When we work with Excel, we may sometimes find that, no data in the cells or blank cells. Sometimes the cells have some hidden objects. In this article, we will discuss how to highlight the blank cells in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Methods to Highlight Blank Cells in Excel

We applied 4 different methods here to highlight blank cells. In the data set, we took the names of students studying in different standards.

Data set for Highlight Blank Cells in Excel

1. Highlight Blank Cells Using Conditional Formatting

We can use Conditional Formatting in different ways to highlight blank cells.

1.1 Highlight All Blanks in a Range

We can highlight blank cells by Conditional Formatting with customizing the fill color.

Step 1:

  • First, select the range where we will search the blanks and highlight them.
  • We can select an entire range by selecting the upper-left cell and pressing Ctrl+Shift+End.

Highlight Blank Cells Using Conditional Formatting

Step 2:

  • Then, go to the Home tab.
  • Go to the Conditional Formatting option.
    We will get a drop-down menu after selecting Conditional Formatting.
  • Select More Rules from the Highlight Cells Rules.
    We will get a new window named New Formatting Rule.

Highlight Blank Cells Using Conditional Formatting

Step 3:

  • Choose Format only cells that contain as Rule Type.
  • Select Blanks as Format only cells with.
  • Now, click on Format.

Highlight Blank Cells Using Conditional Formatting

Step 4:

  • Select color for the Fill field.
  • Then click OK.

Step 5:

  • Now, we will see the Preview.

Highlight Blank Cells Using Conditional Formatting

Step 6:

  • Finally, press OK.

Here, we see that the blank cells are highlighted as per our chosen color.


1.2 Highlight the Rows That Have Blank Cells (Specific Column)

In this section, we will highlight the rows that contain blank cells based on a specific column. If any cell of the specific column is blank, then that row will be highlighted. We will apply the ISBLANK function here.

Step 1:

  • Select all the datasets first. (First, select the upper-left cell, and then press Ctrl+Shift+End)

Step 2:

  • Go to the Home tab.
  • Select New Rule from the Conditional Formatting command.

Highlight Blank Cells Using Conditional Formatting

Step 3:

  • Now, choose the option Use a formula to determine which cells to format from the Rule Type.
  • Write the formula on the marked box of the below image.
=ISBLANK($B5)
  • Then, click Format.

Highlight Blank Cells Using Conditional Formatting

Step 4:

  • Chose the desired color for the Fill tab.
  • Then press OK.

Highlight Blank Cells Using Conditional Formatting

Step 5:

  • We will get a Preview of the operation.

Step 6:

  • After pressing OK, we will get the final return.

Here we can see the 8th row is highlighted as cell B8 is blank and we compared based on column B.

An Alternative to ISBLANK:
We can also use the LEN function to perform this operation. We have to modify the formula and change the color format for that.
The formula will look like the following:

=LEN($B5)=0

Highlight Blank Cells Using Conditional Formatting

After inputting the formula, press OK.

We see that this LEN function is also performing the same operation.


1.3 Highlight the Rows That Have Blank Cells (Any Column)

In this section, we will use the CONUNTBLANK function with conditional formatting. This operation will highlight the rows that contain blank cells in any column.

Step 1:

  • Write the COUNTBLANK using conditional formatting as shown before. The formula will be:
=COUNTBLANK($B5:$D5)

Highlight Blank Cells Using Conditional Formatting

Step 2:

  • Set the Format field and see the Preview window.

Step 3:

  • Now, press OK.

Highlight Blank Cells Using Conditional Formatting

Here, we see that the rows are highlighted which contains any blank cell at any column.

Turn off Conditional Formatting for Blank Cells:

If we want to turn off the Conditional Formatting, we can simply do this.

Select Clear Rules from the Conditional Formatting drop-down. We will get two options now. If we want to clear rules from the Selected Cells or the Entire Sheet.

Highlight Blank Cells Using Conditional Formatting

Read More: How to Find Blank Cells in Excel (8 Easy Ways)


2. Select and Highlight Blank Cells with Go To Special

Step 1:

  • First, we will select all the data cells.

Step 2:

  • Then press F5 or Ctrl+G.
  • A new window named Go To will appear.
  • Click Special from that window.

Select and Highlight Blank Cells with Go To Special

Step 3:

  • After clicking Special, we will get Go To Special
  • Select Blanks from there.

Select and Highlight Blank Cells with Go To Special

Step 4:

  • Finally, press OK.

Here, we see that blank cells are marked.

Note:

  • This method selects pure blank cells. Those cells containing spaces, empty string, non-printing characters are not considered as blank.
  • This is a one-time solution. Also, static. It means if we change the data, it will not further reflect the changes.

Read More: How to Fill Blank Cells in Excel with Go To Special (With 3 Examples)


Similar Readings


3. Filter & Highlight Blank Cells in Specific Column

The AutoFilter command will help to detect blank cells based on columns. But we cannot highlight cells by the AutoFilter only. We have gone a few more steps for that.

Step 1:

  • First, select the heading of each column.

Step 2:

  • Then, go to the Home tab.
  • From Editing command get Sort & Filter tool.
  • Then select Filter.
  • Or we can simply press Ctrl+Shift+L.

Filter & Highlight Blank Cells in Specific Column

Step 3:

  • We see that the Filter option is activated.

Step 4:

  • Click the drop-down and select Blanks.

Filter & Highlight Blank Cells in Specific Column

Step 5:

  • Then, press OK.

We can see the blank cells based on Column B are showing. You can now highlight the blanks manually by fill the color option.
We can show the blank columns of other cells also.

Read More: How to Delete Empty Cells in Excel (6 Methods)


4. Use of VBA Macros to Highlight Blank Cells in Excel

In this section, we will apply the VBA codes to highlight blank cells in Excel. We will perform with VBA Macros for perfectly blank cells, which means they do not have any content at all no space/empty string; and for apparently blank cells that have the empty string in truth.

4.1 Highlight Real Blank Cells

Step 1:

  • This is our data set. We will apply the VBA code here.

Step 2:

  • Go to the Home tab.
  • Then choose the Developer tab.
  • Click on the Macros from the commands.

Using VBA to Highlight Blank Cells in Excel

Step 3:

  • Get a new window.
  • Set the Macro name as Hightlight_Blank.
  • Then press Create.

Using VBA to Highlight Blank Cells in Excel

Step 4:

  • We get the command window of VBA.
  • Write down the below VBA code.
Sub Highlight_Blank()
Dim Dataset As Range
Set Dataset = Range("B5:D9")
Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 181, 106)
End Sub

Using VBA to Highlight Blank Cells in Excel

Step 5:

  • Finally, press F5 to run the code.

We can see that the blank cells are highlighted.


4.2 Highlight Cells with Empty Strings

Step 1:

  • First, we modify the data set. Add one space in a cell.

Step 2:

  • Create a new Macro named
  • Then press OK.

Step 3:

  • Now, write the below on the command module of the VBA.
Sub Highlight_Empty_String()
Dim Dataset As Range
Set Dataset = Range("B5:D9")
For Each cell In Dataset
If cell.Text = "" Then
cell.Interior.Color = RGB(255, 181, 110)
Else
cell.Interior.ColorIndex = xlNone
End If
Next
End Sub

Using VBA to Highlight Blank Cells in Excel

Step 4:

  • Finally, press F5 to run the code.

Using VBA to Highlight Blank Cells in Excel

We see that the cell containing space is not highlighted, but the rest blank cells are highlighted.

Read More: How to Fill Blank Cells with Value Above in Excel VBA (3 Easy Methods)


Conclusion

In this article, we showed some methods to highlight blank cells in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo