by Svetlana Cheusheva, updated on
The article shows how to find and highlight blanks in Excel with the help of conditional formatting and VBA. Depending on your needs, you can color only truly blank cells or those that contain zero-length strings as well.
When you receive an Excel file from someone or import it from an external database, it's always a good idea to check the data to make sure there are no gaps or missing data points. In a small dataset, you can easily spot all the blanks with your own eyes. But if you have a huge file containing hundreds or even thousands of rows, pinpointing empty cells manually is next to impossible.
This tutorial will teach you 4 quick and easy ways to highlight blank cells in Excel so that you can visually identify them. Which method is the best? Well, that depends on the data structure, your goals and your definition of "blanks".
This simple method selects all blank cells in a given range, which you can then fill with any color of your choosing.
To select blank cells in Excel, this is what you need to do:
Tips and notes:
If you do not care about empty cells anywhere in the table but rather want to find and highlight cells or the entire rows that have blanks in a certain column, Excel Filter can be the right solution.
To have it done, carry out these steps:
In our sample table, this is how we can filter, and then highlight the rows where the SKU cells are empty:
Notes:
Both techniques discussed earlier are straightforward and concise, but they do have a significant drawback - neither method reacts to changes made to the dataset. Unlike them, Conditional Formatting is a dynamic solution, meaning you need to set up the rule just once. As soon as an empty cell is populated with any value, the color will immediately go away. And conversely, once a new blank appears, it will get highlighted automatically.
To highlight all empty cells in a given range, configure the Excel conditional formatting rule in this way:
To highlight absolutely blank cells that contain nothing:
=ISBLANK(A2)
To also highlight seemingly blank cells that contain zero-length strings ("") returned by your formulas:
=LEN(A2)=0
or
=A2=""
For the detailed steps, please see Create a formula-based conditional formatting rule in Excel.
In situation when you want to highlight the entire rows that have empty cells in a particular column, just make a small change in the formulas discussed above so that they refer to the cell in that specific column, and be sure to lock the column coordinate with the $ sign.
For example, to highlight rows with blanks in column B, select the whole table without column headers (A2:E6 in this example) and create a rule with one of these formulas:
To highlight absolutely blank cells:
=ISBLANK($B2)
To highlight blanks and cells containing empty strings:
=LEN($B2)=0
or
=$B2=""
As the result, only the rows where an SKU cell is empty are highlighted:
For more information, please see Excel conditional formatting for blank cells.
If you are fond of automating things, you may find useful the following VBA codes to color empty cells in Excel.
This macro can help you highlight truly blank cells that contain absolutely nothing.
To color all empty cells in a selected range, you need just a single line of code:
To highlight blanks in a predefined worksheet and range (range A2:E6 on Sheet 1 in the below example), this is the code to use:
Instead of an RGB color, you can apply one of the 8 main base colors by typing "vb" before the color name, for example:
Selection.SpecialCells(xlCellTypeBlanks).Interior.Color = vbBlue
Or you can specify the color index such as:
Selection.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6
To recognize visually blank cells containing formulas that return empty strings as blanks, check if the Text property of each cell in the selected range = "", and if TRUE, then apply the color.
Here's the code to highlight all blanks and empty strings in a selected range:
To add a macro to your workbook, carry out these steps:
To run the macro, this is what you need to do:
For the detailed step-by-step instructions, please see:
That's how to find, select and highlight blank cells in Excel. I thank you for reading and hope to see you on our blog next week!
Highlight blanks with Conditional Formatting (.xlsx file)
VBA macros to color empty cells (.xlsm file)
Table of contents