Pointing blank cells in larger data might be a tricky thing to do. You may find it odious, boring, and the most unwanted thing. A blank cell is just a tiny cell in huge crowdedly filled rows and columns of cells. Just a white cell that represents missing data or formula results in an empty string and appears as a black cell.
Importing an Excel file from an external database needs you to check the data so that you can find out missing data points. Though a small datasheet easily allows you to highlight black cells in Excel. On the other side, having a huge data set leads you to find some effective solutions. Manually highlighting hundreds and thousands of rows and columns with blank cells is clearly not possible.
That’s where you need some tricks and tips to highlight blank cells in Excel. Highlighting these empty cells with any color simply makes the task uncomplicated.
How to Highlight Blank Cells in Excel
You must be wondering which method answers perfectly to how to highlight blank cells in Excel. Well, it purely depends upon you as we have come up with more than one method that can help you recognize blank cells. Each method has its significance, so you can try it all randomly.
Here we have tried and proved methods to highlight and color empty cells.
Method # 1: Highlight Blank Cells with Go To Special
With this simple method, you can highlight all blank cells in a suspected range that later on can be filled with any color.
Let’s begin with the process:
- Choose the area or range from where you want to highlight blank cells. Click the upper-left cell and press Ctrl +Shift + End to pick each cell with data. It will make the selection longer till the last used cell.
- Go to the Home tab and choose the “Editing” group. Now, click on Find & Select then Go to Special. Or else you can simply press F5 and click on Special.
- Choose Blanks in the Go To Special dialogue box and press OK.
- It will result in selecting all blank cells in the range.
- As you see all blank cells being selected, fill them with a color to make them prominent.
All done!
Note: Keep in mind that Go To Special method works where your data has truly empty cells. For instance, absolutely blank cells with nothing in them. Also, note that if your data has cells filled with spaces, empty strings, non-printing characters, carriage returns, etc. will not be highlighted because they are not considered as blank.
In either case, you can use another method called Conditional Formatting or VBA macro that we will discuss in this article later.
This is one of the best methods, as you don’t need to repeat the process in any case. Another thing to bear in mind is that any change you make later will not be highlighted or colored. So, you have to keep repeating the method until the last change you make.
Method # 2: Conditional Formatting to Highlight Blank Cells
Here is another greatly effective method to practice:
- At first, you need to choose the data table.
- Now, go to the Home tab and click on Conditional Formatting.
- Next, select the “highlight cell rules” option and click on the “more rules” option.
- From this menu, select the “Blank” option.
- Now, click on the “format” button to start formatting.
- Choose the color you want to use for highlighting from the formatting options.
- Press OK.
Do you know the best part of this method? If your data set has blank cells resulting from a formula, these cells will highlight as well. Moreover, if you are working with a pivot table rather than a normal table, you don’t need to change any step.
Method # 3: Filter Approach
The above two methods work finely with all kinds of data sets. However, if you want to find and highlight blank cells of just rows in a specific column, then you can try this approach.
For this:
- You need to choose all data using the first method’s first step.
- Now, press Ctrl + Shift + L to turn filters on.
- Continue the selection and start filtering the columns you need to represent blank values.
- Now, fill the cells with yellow color.
All Done!
Isn’t it that simple?
Method # 4: Using VBA to Highlight Blank Cells in Excel
Are you familiar with the VBA coding system?
Using a short VBA code can help you highlight empty cells in a selected range. Try this method frequently when you want to find and highlight empty cells in data sets.
Below you will find the VBA code to highlight blank cells in the selected range of the dataset:
Sub Highlight Blank Cells()
Dim Dataset As Range
Set Dataset = Selection
Dataset.Special Cells(xlCellTypeBlanks).Interior.Color = vbRed
End Sub
Now, let’s run this method practically by following some easy steps:
- Go to the Developer tab and click on the Visual Basic option. Or else you can simply press ALT + F11.
- Open the VB editor and right-click on the given sheet names in the Project Explorer. In case, if you don’t find Project Explorer, press Ctrl + R.
- Go to Insert and choose the Module option.
- In the Module code window, you need to copy and paste the VBA code.
Now, close the VB Editor.
To Sum Up
So, these were some of the most efficient methods to practice. You will find each of the above-given methods helpful. It is entirely up to you, which method you choose because each method has its worth. Conditional Formatting is my personal favorite method that I like to use always for highlighting empty cells in all data sets.
If you have come across a method of finding and highlighting blank cells in Excel other than those mentioned above, you are welcome to share with us.
Keep practicing the tricks and enjoy using different methods for the same task.
