When I get a data file from a client/colleague or I download it from a database, I do some basic checks on the data. I do this to make sure there are no missing data points, errors or duplicates that may lead to issues later. One such check is to find and highlight blank cells in Excel. There are many reasons that can result in blank cells in a dataset:
The data is not available. Data points accidentally got deleted. A formula returns an empty string resulting in a blank cell.
While it’s easy to spot these blank cells in a small dataset, if you have a huge one with hundreds of rows and columns, doing this manually would be highly inefficient and error prone. In this tutorial, I will show you various ways to find and highlight blank cells in Excel.
Highlight Blank Cells Using Conditional Formatting
Conditional Formatting is a great way to highlight cells based on its value when the given condition is met. I am going to showcase these examples with a small dataset. However, you can use these same techniques with large datasets as well. Suppose you have a dataset as shown below:
You can see there are blank cells in this dataset. Here are the steps to highlight blank cells in Excel (using conditional formatting):
Select the data. Go to the Home tab. In the Conditional Formatting drop down, click on New Rule. In the ‘New Formatting Rules’ dialog box, select ‘Format only cells that contain’. Select ‘Blanks’ from the drop down (as shown below): Specify the formatting (in which you want to highlight blanks). Click OK.
This would highlight all the blank cells in the dataset.
Note that conditional formatting is dynamic. This means that if conditional formatting is applied and you delete a data point, that cell would get highlighted automatically. At the same time, this dynamic behavior comes with an overhead cost. Conditional Formatting is volatile, and if used on large data sets, may slow down your workbook.
Select and Highlight Blank Cells in Excel
If you want to quickly select and highlight cells that are blank, you can use the ‘Go to Special’ technique. Here are the steps to select and highlight blank cells in Excel:
Select the data. Press the F5 key. It will open the Go To dialog box. In the Go To dialog box, click on the Special button. In the Go To Special dialog box, select Blanks. Click OK. This will select all the blank cells in the dataset. With all the blank cells selected, highlight these by giving it a cell color.
As mentioned, this method is useful when you want to quickly select all the blank cells and highlight it. You can also use the same steps to select all the blank cells and then fill 0 or NA or any other relevant text in it. Note that unlike conditional formatting, this method is not dynamic. If you do it once, and then by mistake delete a data point, it will not get highlighted.
Using VBA to Highlight Blank Cells in Excel
You can also use a short VBA code to highlight blank cells in a selected dataset. This method is more suitable when you need to often find and highlight blank cells in data sets. You can easily use the code below and create an add-in or save it in your personal macro workbook. Here is the VBA code that will highlight blank cells in the selected dataset: Here are the steps to put this VBA code in the backend and then use it to highlight blank cells in Excel:
Go to the Developer tab and click on Visual Basic (or press ALT + F11). In the Vb Editor, within the Project Explorer, right-click on any of the sheet names (if you don’t see Project Explorer, press CONTROL + R). Go to Insert and click on Module. In the Module code window, copy and paste the VBA code. Close the VB Editor.
How to Run the VBA code (Macro)?
Once you have copy pasted this macro, there are a couple of ways you can use this macro. Using the Macro Dialog Box Here are the steps to run this macro using the Macro dialog box: Using the VB Editor Here are the steps to run this macro using the VB Editor: As I mentioned, using VBA macro to highlight blank cells is the way to go if you need to do this often. Apart from the ways shown above to run the macro, you can also create an add-in or save the code in the Personal macro workbook. This will allow you to access this code from any workbook in your system.
How to Find External Links and References in Excel. How to Quickly Find and Remove Hyperlinks in Excel. Highlight EVERY Other ROW in Excel. The Ultimate Guide to Find and Remove Duplicates in Excel. Using InStr Function in Excel VBA. How to Filter Cells with Bold Font Formatting in Excel. How to Compare Two Columns in Excel.
