Alok Paul
When we work with Excel, we may sometimes find that, no data in the cells or blank cells. Sometimes the cells have some hidden objects. In this article, we will discuss how to highlight the blank cells in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Methods to Highlight Blank Cells in Excel
We applied 4 different methods here to highlight blank cells. In the data set, we took the names of students studying in different standards.
1. Highlight Blank Cells Using Conditional Formatting
We can use Conditional Formatting in different ways to highlight blank cells.
1.1 Highlight All Blanks in a Range
We can highlight blank cells by Conditional Formatting with customizing the fill color.
Step 1:
- First, select the range where we will search the blanks and highlight them.
- We can select an entire range by selecting the upper-left cell and pressing Ctrl+Shift+End.
Step 2:
- Then, go to the Home tab.
- Go to the Conditional Formatting option.
We will get a drop-down menu after selecting Conditional Formatting. - Select More Rules from the Highlight Cells Rules.
We will get a new window named New Formatting Rule.
Step 3:
- Choose Format only cells that contain as Rule Type.
- Select Blanks as Format only cells with.
- Now, click on Format.
Step 4:
- Select color for the Fill field.
- Then click OK.
Step 5:
- Now, we will see the Preview.
Step 6:
- Finally, press OK.
Here, we see that the blank cells are highlighted as per our chosen color.
1.2 Highlight the Rows That Have Blank Cells (Specific Column)
In this section, we will highlight the rows that contain blank cells based on a specific column. If any cell of the specific column is blank, then that row will be highlighted. We will apply the ISBLANK function here.
Step 1:
- Select all the datasets first. (First, select the upper-left cell, and then press Ctrl+Shift+End)
Step 2:
- Go to the Home tab.
- Select New Rule from the Conditional Formatting command.
Step 3:
- Now, choose the option Use a formula to determine which cells to format from the Rule Type.
- Write the formula on the marked box of the below image.
=ISBLANK($B5)- Then, click Format.
Step 4:
- Chose the desired color for the Fill tab.
- Then press OK.
Step 5:
- We will get a Preview of the operation.
Step 6:
- After pressing OK, we will get the final return.
Here we can see the 8th row is highlighted as cell B8 is blank and we compared based on column B.
An Alternative to ISBLANK:
We can also use the LEN function to perform this operation. We have to modify the formula and change the color format for that.
The formula will look like the following:
=LEN($B5)=0
After inputting the formula, press OK.
We see that this LEN function is also performing the same operation.
1.3 Highlight the Rows That Have Blank Cells (Any Column)
In this section, we will use the CONUNTBLANK function with conditional formatting. This operation will highlight the rows that contain blank cells in any column.
Step 1:
- Write the COUNTBLANK using conditional formatting as shown before. The formula will be:
=COUNTBLANK($B5:$D5)
Step 2:
- Set the Format field and see the Preview window.
Step 3:
- Now, press OK.
Here, we see that the rows are highlighted which contains any blank cell at any column.
Turn off Conditional Formatting for Blank Cells:
If we want to turn off the Conditional Formatting, we can simply do this.
Select Clear Rules from the Conditional Formatting drop-down. We will get two options now. If we want to clear rules from the Selected Cells or the Entire Sheet.
Read More: How to Find Blank Cells in Excel (8 Easy Ways)
2. Select and Highlight Blank Cells with Go To Special
Step 1:
- First, we will select all the data cells.
Step 2:
- Then press F5 or Ctrl+G.
- A new window named Go To will appear.
- Click Special from that window.
Step 3:
- After clicking Special, we will get Go To Special
- Select Blanks from there.
Step 4:
- Finally, press OK.
Here, we see that blank cells are marked.
Note:
- This method selects pure blank cells. Those cells containing spaces, empty string, non-printing characters are not considered as blank.
- This is a one-time solution. Also, static. It means if we change the data, it will not further reflect the changes.
Read More: How to Fill Blank Cells in Excel with Go To Special (With 3 Examples)
Similar Readings
- Fill Blank Cells with Value Above in Excel (4 Methods)
- Null vs Blank in Excel
- How to Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)
- Fill Blank Cells with 0 in Excel (3 Methods)
- How to Skip Blank Rows Using Formula in Excel (8 Methods)
3. Filter & Highlight Blank Cells in Specific Column
The AutoFilter command will help to detect blank cells based on columns. But we cannot highlight cells by the AutoFilter only. We have gone a few more steps for that.
Step 1:
- First, select the heading of each column.
Step 2:
- Then, go to the Home tab.
- From Editing command get Sort & Filter tool.
- Then select Filter.
- Or we can simply press Ctrl+Shift+L.
Step 3:
- We see that the Filter option is activated.
Step 4:
- Click the drop-down and select Blanks.
Step 5:
- Then, press OK.
We can see the blank cells based on Column B are showing. You can now highlight the blanks manually by fill the color option.
We can show the blank columns of other cells also.
Read More: How to Delete Empty Cells in Excel (6 Methods)
4. Use of VBA Macros to Highlight Blank Cells in Excel
In this section, we will apply the VBA codes to highlight blank cells in Excel. We will perform with VBA Macros for perfectly blank cells, which means they do not have any content at all no space/empty string; and for apparently blank cells that have the empty string in truth.
4.1 Highlight Real Blank Cells
Step 1:
- This is our data set. We will apply the VBA code here.
Step 2:
- Go to the Home tab.
- Then choose the Developer tab.
- Click on the Macros from the commands.
Step 3:
- Get a new window.
- Set the Macro name as Hightlight_Blank.
- Then press Create.
Step 4:
- We get the command window of VBA.
- Write down the below VBA code.
Sub Highlight_Blank()
Dim Dataset As Range
Set Dataset = Range("B5:D9")
Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 181, 106)
End Sub
Step 5:
- Finally, press F5 to run the code.
We can see that the blank cells are highlighted.
4.2 Highlight Cells with Empty Strings
Step 1:
- First, we modify the data set. Add one space in a cell.
Step 2:
- Create a new Macro named
- Then press OK.
Step 3:
- Now, write the below on the command module of the VBA.
Sub Highlight_Empty_String()
Dim Dataset As Range
Set Dataset = Range("B5:D9")
For Each cell In Dataset
If cell.Text = "" Then
cell.Interior.Color = RGB(255, 181, 110)
Else
cell.Interior.ColorIndex = xlNone
End If
Next
End Sub
Step 4:
- Finally, press F5 to run the code.
We see that the cell containing space is not highlighted, but the rest blank cells are highlighted.
Read More: How to Fill Blank Cells with Value Above in Excel VBA (3 Easy Methods)
Conclusion
In this article, we showed some methods to highlight blank cells in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
