How To Highlight Blank Cells In Excel

by

Whenever we receive data from someone or an external source, it is important to check the data for accuracy. 

One common challenge many of us face is dealing with blank cells. Users may keep blank cells to add information later. However, this can create confusion and potential errors.

In a small dataset, the task of identifying blank cells may seem minor, but as the dataset grows in size, so does the challenge! 

Thus, in this article, we will explore various methods for highlighting blank cells in Excel. By doing so, you can reduce the risk of data-related mishaps by visually identifying them. 

Method 1: Highlighting blank cells using ‘Go To Special’ method

Go To command is used to quickly find and select all cells containing specific types of data. In this case, we will use it to highlight blank cells in Excel.

We are going to use the data shown below as a reference to learn the techniques.

Here are the steps to follow:

Step 1: Select the range of cells where you want to highlight blank cells

Select the range of cells where you want to highlight blank

Step 2: Under the Home tab. Click on ‘Find and Select. Click on the drop-down option

Under the Home tab. Click on ‘Find and Select. Click on the drop-down option

Step 3: Select ‘Go to Special’ from the drop-down as highlighted

Select ‘Go to Special’ from the drop-down as highlighted

Step 4: Select ‘Blanks from the options and click ‘OK’ as shown in the screenshot below

Select ‘Blanks from the options and click ‘OK’ as shown in the screenshot below

Step 5: As an output, all the blank cells will be greyed

As an output, all the blank cells will be greyed

Step 6: You can choose any color of your liking or as per the organization’s policy

You can choose any colour of your liking or as per the organisation's policy
You can choose any colour of your liking or as per the organisation's policy

Things to Note while using the ‘Go to Special’ option

  • The Go To Special feature is safe to use as it does not change data, only allows selection
  • It is not dynamic, any changes made to the data require manual formatting of that cell. For example, if the data is added in place of Blank, you will need to change the color for that cell manually.
Also read: How to Filter by Color in Excel?

Method 2: Highlighting Empty cells using ‘Sort and Filter’ method

Like the previous method, the FILTER feature allows you to filter a range of data based on criteria you define.

However, before we apply the filter, the function needs to be applied to the data. Let’s see how it can be done in a step-by-step method.

Step 1: Select all the cells in the table as shown below

Select all the cells in the table as shown below

Step 2: From the ‘Home’ tab, select ‘Data’ and click on the ‘Filter’ option under ‘Data’. Refer to the screenshot for guidance.

From the ‘Home’ tab, select ‘Data’ and click on the ‘Filter’ option under ‘Data’

Step 3: With that selection, an inverted triangle will appear on the header of each column. They are called ‘Filter’.

With that selection, an inverted triangle will appear on the header of each column. They are called ‘Filter’.

Step 4: Click on the Filter or the inverted triangle and it will open the list of drop-down numbers under column C.

Click on the Filter or the inverted triangle and it will open the list of drop-down numbers under column C

Step 5: By default, all numbers and blanks are selected. Click on the tick against ‘Select All’

By default, all numbers and blanks are selected. Click on the tick against ‘Select All’

Step 6: Now, manually check the box against ‘Blanks’ as shown in the screenshot below. Click ‘OK’ once you’ve made the selection.

Now manually check the box against ‘Blanks’ as shown in the screenshot below. Click ‘OK’ once you’ve made the selection.

Step 7: All the blanks in column C will be automatically selected

All the blanks in column C will be automatically selected

Step 8: You can color the blanks so it is easy to identify them in the future. Please note this is an optional step.

You can colour the blanks so it is easy to identify it in the future. Please note this is an optional step.

You can color them as per your choice or as per the organization’s policy.

You can colour the blanks so it is easy to identify it in the future. Please note this is an optional step.

Add-On Method: As an additional step, we can combine all consecutive empty rows for the ease of identification and filling of the blanks in the future.

For this, we will use the ‘SORT’ function. The SORT function sorts the contents of a range based on the command.

Step 1: Select the entire table where we want to apply the SORT function.

Select the entire table where we want to apply the SORT function.

Step 2: From the ‘Home’ tab in the Excel Ribbon select ‘Data’ and click on the ‘Sort’ feature. Refer to the screenshot below.

select ‘Data’ and click on the ‘Sort’ feature

Step 3: Once we click the ‘SORT’ button, a new window will appear, asking us which column we want to use to sort the data. In this case, since the blank cells are in column C, which is labelled as ‘Contact’, we will choose ‘Contact’ from the drop-down menu under the ‘Sort By’ option.

Click ‘OK’ to confirm the selection.

choose ‘Contact’ from the drop-down menu under the ‘Sort By’ option.

Step 4: The data will be sorted based on column C. To check if the data is sorted, look for an arrow near the filter (encircled).  Now, all blank cells are sorted together.

Now, all blank cells are sorted together.

Things to Note while using the ‘Sort and Filter’ option

Many times, cells appear blank but are not really blank. It happens in cases where a cell contains a formula and returns empty text – this is denoted by double-double quotes “” in formulas. 

Or cells that contain a space character.

In such cases, the cells appear blank but are not really blank.

The advantage of using the method of ‘Sort and Filter’ is that it can identify the cells that are blank and also ‘appear’ blank.

Not to mention, this is the simplest, easiest, and quickest method to differentiate blanks. 

It is important to exercise caution when using this feature. Make sure to check that the data in all other columns are also rearranged or sorted accordingly. 

In the last picture, for example, if you check column A, you will notice that the serial numbers have been shifted according to the sorting of column C. 

Also read: How to Remove Dollar Sign in Excel

Method 3: Highlighting blank cells using ‘Conditional Formatting’ method

Conditional formatting makes it easy to highlight certain values or make particular cells easy to identify.

This changes the appearance of a cell range based on a condition (or criteria). In this case, we will use it to highlight blanks.

Step 1: Select the column that contains ‘Blanks’. In the below data set, we are selecting, Column ‘C’.

Select the column that contains ‘Blanks’. In the below data set, we are selecting, Column ‘C’.

Step 2: From the ‘Home’ tab, select ‘Conditional Formatting’.

From the ‘Home’ tab, select ‘Conditional Formatting’.

Step 3: A list of options will appear in the drop-down. Select the first option, ‘Highlight Cell Rule’. This will further open an array of options, as shown in the screenshot below.

Select the first option ‘Highlight Cell Rule’. This will further open into array of options as shown in the screenshot belo

Step 4: Select ‘More Rule’

Select ‘More Rule’

Step 5: With that selection, a new window will appear. Select the second option from that window ‘Format only cells that contain’

Select 'Format only cells that contain’

Step 6: Go to the ‘Edit the Rule Description’ and select ‘Blanks’ from the drop-down under the header Cell value. 

Go to the ‘Edit the Rule Description’ and select ‘Blanks’ from the drop-down under the header Cell value. 

Step 7: Click on ‘Format’ button.

Click on ‘Format’ post that.

Step 8: A new window will appear. Although the default option will be ‘Fill’. If not, manually click on the Fill tab. 

  • Select any color of your choice or as per the organization’s policy. 
  • Click on ‘OK’ to confirm the choice.
Select any colour of your choice or as per the organisation’s policy. 

Step 9: The previous window will appear. Click ‘OK’ to confirm the choice once again.

Click ‘OK’ to confirm the choice once again.

The cells with Blanks will be filled with the desired color in the column ‘C.’

The cells with Blanks will be filled with the desired colour in the column ‘C’

Things to Note while using Conditional Formatting

  1. It will automatically change the data whenever there is a new entry in Excel which means, that any data if is added in a blank cell, will no longer be highlighted.
  2. Conditional Formatting reads entire data with every change. This may work in the smaller set of data, however, it can potentially slow down the system in case of large data sets.
  3. Conditional formatting is relatively easy to set up, and you can quickly apply it to a specific range or column.
Also read: How To Change The Ribbon Color in Excel

Method 4: Highlight blank cells using ‘VBA’ method

However, if you don’t know how to use VBA or write code, Don’t Fret! 

Using the same data set as before, we will now explore the VBA option. VBA is useful in the case of a large dataset and/ or repetitive task of highlighting blank cells.

Simply follow the steps and copy the code mentioned here, and it’s done.

Step 1: From the ‘Home’ ribbon, go to ‘Developer’ and select ‘Visual Basic’. Refer to the screenshot below.

From the ‘Home’ ribbon, go to ‘Developer’ and select ‘Visual Basic’. Refer to the screenshot below

Step 2: Selection of the Visual Basic function will open another window. Click on ‘Insert’ and select ‘Module’ from the options provided.

Refer to the screenshot below

Click on ‘Insert’ and select ‘Module’

Step 3: This selection will open the VBA editor. 

open the VBA editor. 

You will now just need to paste the below code into the module.

Sub SelectBlankCells()
    ' Declares a variable for the worksheet
    Dim ws As Worksheet

    ' Sets the worksheet to "Sheet1" in the current workbook
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name as needed

    ' Selects all blank cells in the used range of the worksheet
    ws.UsedRange.SpecialCells(xlCellTypeBlanks).Select
End Sub

Take a look at the example shown below:

Please note: 1) You will need to update the ‘Sheet number’ in the code (encircled). In this example, it is updated to sheet no. 5- screenshot for reference. 

2) Delete the sentence ‘Change the sheet as needed’ upon updating the sheet number. 

This is how the final code looks like

Step 3: Run the VBA code by pressing F5 OR clicking ‘Run’ Or click ‘play’ icon

And close the window.

Step 4: All the blanks will be highlighted

Step 5: You can color the blanks so it is easy to identify them in the future. Please note this is an optional step. Choose any color of your choice or as per the organization’s policy.

The blanks will be highlighted in the chosen color.

So, now you know that VBA can automate these repetitive tasks for you? It’s really powerful when you’re dealing with empty cells with huge datasets. 

You do not need to know coding or VBA scripting; just copy the code and follow the steps mentioned above. 

In this article, we learned numerous methods to tackle the challenge of blank cells in a spreadsheet. Choosing the right method depends on your specific needs and familiarity with Excel’s functions. 

Other articles you may also like:

Leave a Comment