While working with a large number of data, numerous blank cells in your dataset is a common scenario. As a result, it can misinterpret your dataset or can give you wrong information. So you may need to identify if there are any blank cells. Conditional formatting is one of the essential tools in Microsoft Excel. We can use it for highlighting cells according to our choice. In this tutorial, we will show you how to apply conditional formatting to blank cells in Excel with suitable examples and proper illustrations.
Applying Conditional Formatting for Blank Cells in Excel: 2 Easy Methods
Here, we are providing you with two easy but effective methods for applying conditional formatting to blank cells in Excel. We recommend you learn and apply all these methods to your dataset. To demonstrate these methods, we will be using the following dataset:
Here, you can see a dataset consisting of some persons’ names, ages, joining dates, and salaries. You can also notice some blank cells in the dataset. In this article, our goal is to apply conditional formatting on those blank cells and highlight them.
1. Apply Conditional Formatting Command for Blank Cells in Excel
Now, if you have worked with Microsoft Excel for a while, you may have seen this Conditional Formatting command:
Basically, our whole article will revolve around this command of Excel. I hope you will find this helpful and that it will enrich your Excel knowledge.
1.1 Use Built-in Formatting Rule from Conditional Formatting Option
Now, this method is the go-to method of almost every user. It is simple and easy to use. You can save a lot of time by using this method. This method works faster than other methods. If you are looking for a method that can highlight blank cells quickly, you can apply this method to your dataset.
📌 Steps
- Firstly, select the entire dataset or the range of cells according to your choice.
- So, we are selecting the range of cells C5:E11.
- Then, from the Home tab, go to Conditional Formatting >> New Rule.
- After that, from the New Formatting Rule Box, select the Format only cells that contain option.
- Then, from the dropdown menu select Blanks.
- After selecting Blanks, click on Format.
- Now, select the Fill menu.
- Then, choose any color and click on OK.
- So, you have successfully set the formatting and condition for blank cells.
- Finally, click on OK.
As you can see, we have successfully applied conditional formatting to blank cells in Excel.
1.2 Apply Conditional Formatting with Formula
If you love to work with formulas in Excel, you can definitely find this method helpful. Sometimes formulas can solve complex problems at ease. Though the previous method works fine, this method will give an extra edge to your Excel knowledge. This method is also simple and easy to use. We are using the ISBLANK function to find blank cells from the dataset.
We are using the same dataset here to demonstrate this method.
📌 Steps
- Firstly, select the entire dataset or the range of cells according to your choice.
- Here, we are selecting the range of cells C5:E11.
- Then, from the Home tab, go to Conditional Formatting >> New Rule.
- Now, from the New Formatting Rule dialog box, select Use a formula to determine which cells to format option.
- Then, type the following formula:
=ISBLANK(C5)=TRUE
- After that, click on Format.
- Now, select the Fill menu.
- Then, choose any color and click on OK.
- Now, we have to set the formula to find the blank cells and formatting.
- Finally, click on OK.
In the end, it will format the blank cells with your fill color. So, we are successful in applying conditional formatting to blank cells in Excel.
Read More: Conditional Formatting If Cell is Not Blank
2. Assign VBA Code to Apply Conditional Formatting for Blank Cells
Now, if you have an idea about Microsoft Excel’s VBA codes, you know how effective this is for solving complex problems. This method will work at ease to highlight blank cells. If you love to work with VBA, this method will help you solve your problems.
📌 Steps
- Firstly, press Alt+F11 to open the VBA editor.
- After that, select Insert >> Module to insert the module.
- After that, type the following code in the module:
Sub format_blank_cells()
Dim range_of_cells As Range
Set range_of_cells = Selection
range_of_cells.SpecialCells(xlCellTypeBlanks).Interior.Color = vbBlue
End Sub- Then, save the file.
- Now, you have to select the range of cells C5:E11.
- Now, press Alt+F8 to open the Macro dialog box.
- Then, select format_blank_cells.
- Finally, click on Run.
As you can see, we have successfully applied conditional formatting to blank cells using VBA in Excel.
Read More: Apply Conditional Formatting to Each Row Individually
Skip Conditional Formatting for Blank Cells in Excel
Now, you may find yourself in a situation where you need to avoid the conditional formatting of blank cells. Take a look at this dataset:
We applied conditional formatting to highlight cells of the Salary column. We wanted the cells to have less than 3000. But, notice here. It included the blank cells that we didn’t require.
📌 Steps
- Firstly, select the range of cells E5:E11.
- Then, from the Home tab, go to Conditional Formatting >> New Rule.
- Now, you can choose any of the previous methods that we discussed to format blank cells. Follow those steps.
- Here, the method is the same. But this time we do not need to select any Format. Leave that empty for blank cells.
- Then, from the Home tab, go to Conditional Formatting >> Manage Rules.
- In the Conditional Formatting Rules Manager window, make sure you have marked those checkboxes for your method.
- Now, click on OK.
As you can see, we have skipped the conditional formatting for blank cells in Excel.
Read More: How to Remove Conditional Formatting but Keep the Format in Excel
How to Undo Applied Conditional Formatting in Blank Cells in Excel
Sometimes, you may need to remove the conditional formatting for your need. Or, maybe you have received the workbook from someone else and it has some conditional formatting. In those cases, you can follow any of these two methods to clear the formats.
1. Use Quick Analysis option
📌 Steps
- Firstly, you have to select the range of cells where the conditional format is applied.
- After that, you will show a Quick Analysis option in the right bottom corner. Click on that.
- Then, select the Clear Rules option. It will remove your conditional formatting for blank cells in Excel.
2. Using Conditional Formatting Command
You can remove the conditional formatting by going to Conditional Formatting >> Clear Rules. Then, choose any of these options.
💬 Things to Remember
- Make sure to mark the checkboxes to skip the conditional formatting for blank cells.
- Clear formatting rules won’t work on VBA codes.
Download Practice Workbook
Download this practice workbook
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge about conditional formatting on blank cells in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Related Articles
- How to Apply Different Types of Conditional Formatting in Excel
- How to Find External Links in Conditional Formatting in Excel
- How to Apply Conditional Formatting on Multiple Columns in Excel
- How to Compare Two Columns Using Conditional Formatting in Excel
- How to Remove Conditional Formatting in Excel
- How to Do Conditional Formatting in Excel
- Conditional Formatting with Formula in Excel
<< Go Back to Conditional Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
