Formatting & Highlighting Blank Cells (Conditional)

If you have a small dataset, it can be easy to spot the blank cells.

However, if you have a big one with hundreds of columns and rows, you may encounter many errors or become highly inefficient if you do this manually.

Whenever I get information from a colleague or client or download it from a database, the data needs to undergo some basic checks.

Doing this can make sure that there are no duplicates, errors, or missing data points that can eventually lead to problems.

One check involves highlighting and looking for blank cells in Excel.

There are so many reasons why this can lead to a dataset with blank cells:

  • The data points got deleted accidentally.
  • Returned by the formula is an empty string that results in a blank cell.
  • The data is unavailable.

Our tutorial will show you the different ways you can highlight and look for the blank cells in Excel.

Use VBA to Highlight the Blank Cells in Excel

If you want to highlight the blank cells in a selected dataset, you can use the short VBA code. 

It is highly suitable to use this method whenever you have to look for and highlight the data sets’ blank cells.

Below is a code that you can easily use to create an add-in or save your macro workbook.

Have the blank cells highlighted in the chosen dataset with this VBA code:

‘Code by ExcelMaster

Sub HighlightBlankCells()

Dim Dataset As Range

Set Dataset = Selection

Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed

End Sub

With these steps, have this VBA code placed in the back-end so you can use it in highlighting the blank cells in Excel:

  • Press ALT + F11 or check the Developer tab and then click on Visual Basic.

  • Check the Vb Editor. Look for Project Explorer. If you don’t see it, press Control + R. In the Project Explorer, right-click on any sheet name.
  • Check out Insert and then click on Module.

  • Once you get to the Module code window, have the VBA code copied and pasted.

  • Close the VB Editor.

How Can You Run the Macro or VBA code?

After you copy and paste the macro, there are several ways you can use this.

Use the VB Editor

These are the steps on how you can use the VB Editor to run this macro:

  1. Choose the data.
  2. Check the Developer tab and then tap on Visual Basic.                                                                                                                        
  3. Get to VB Editor. Within the code, click anywhere.
  4. Tap on the F5 key or click on the toolbar’s Green triangle button.                                                                                                                                                 

Use the Macro Dialog Box

Use the Macro dialog box to run this macro with these steps:

  1. Pick the data.
  2. Tap on the Developer tab and then choose Macros.                                                                                                                                                                   
  3. Once you get to the Macro dialog box, choose the macro ‘HighlightBlankCells’ and then tap on Run.                                                                               

As I mentioned earlier, if you need to do this frequently, it would be best to highlight the blank cells using VBA macro.

Aside from the ways that we mentioned in running the macro, you can also save the code or make an add-in in your macro workbook, as this can let you have access to this code from any of your system’s workbooks.

Choose and Highlight the Blank Cells in Excel

Whenever you want to immediately choose and highlight the blank cells, use the technique ‘Go to Special.’

Do these steps to choose and highlight the Excel spreadsheet’s blank cells:

  • Choose the data.
  • Tap on the F5 key to get to the Go To dialog box.
  • Once you get to the Go To dialog box, click on the button Special.

  • Select Blanks in the dialog box Go To Special.

  • Click on OK so you can choose all of the dataset’s blank cells.

  • Once you select all of the blank cells, have them highlighted by giving them a cell color.

Like what we discussed, this method can be helpful whenever you want to have all the blank cells selected and highlighted immediately.

You can choose every empty cell with the same steps and then fill them with NA, 0, or any relevant text.

Take note that this is not similar to conditional formatting as this is not a dynamic method.

Whenever you do this once and mistakenly have a data point deleted, it wouldn’t get highlighted.

Use Conditional Formatting to Highlight Blank Cells

If you want to have the cells highlighted according to its value whenever it meets a specific condition, the best way to do this is through Conditional Formatting.

Here are some examples that have a small dataset.

Yet, you can have the same techniques used with large datasets. Assume that you have this dataset:

This dataset has blank cells. If you want to highlight the empty cells, here are the steps on how you can do this through conditional formatting:

  • Make sure to select the data                                                                                                                                                                                                                 
  • Check out the Home tab.                                                                                                                                                                                                                          
  • Go to the drop-down of Conditional Formatting and then click on New Rule.                                                                                                                                                                             
  • Once you get to the dialog box ‘New Formatting Rules,’ tap on ‘Format only cells that contain.’                                                                         
  • From the drop-down, choose ‘Blanks’ as seen here:                                                                                                                                                   
  • Highlight the blanks and then specify the formatting.                                                                                                                                                               
  • Click on OK.                                                                                                                                                                                                                                                                                                                         

Once you do this, it will highlight all of the empty cells in the dataset.

Take note that conditional formatting can be dynamic.

Because of that, whenever you apply conditional formatting and delete a data point, the cell could automatically get highlighted.

However, there is an overhead cost that comes with this dynamic behavior.

Because of the volatility of Conditional Formatting, using it on large data sets can make your workbook slow down.

Become Knowledgeable in Conditionally Formatting and Highlighting Blank Cells with ExcelMaster

Make yourself an Excel expert with us!

Get to know how you can conditionally format and highlight blank cells with ExcelMaster NOW!