Conditional Formatting for Blank Cells in Excel (2 Methods)

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.


Download Practice Workbook

Download this practice workbook


2 Methods for Applying Conditional Formatting to Blank Cells in Excel

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:

dataset for applying conditional formatting on blank cells in excel

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.
Our goal is to apply conditional formatting on those blank cells and highlight them.

1. Use of the Conditional Formatting Command for Blank Cells

Now, if you have worked with Microsoft Excel for a while, you may have seen this Conditional Formatting command:

conditional formatting option in Excel

Basically, our whole article will revolve around this command of Excel. I hope you will find this helpful and it will enrich your Excel knowledge.

1.1 Using 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

First, select the entire dataset or the range of cells according to your choice. We are selecting the range of cells C5:E11.

select the range of cells for conditional formatting

Then, from the Home tab, go to Conditional Formatting > New Rule.

select new rule from conditional formatting option

Now, from the New Formatting Rule Box, select the Format only cells that contain option.

select option for blank cells in excel

Then, from the dropdown menu select Blanks.

select the blanks option to identify blank cells in excel

After selecting Blanks, click on Format.

select format option

Now, select the Fill menu. Then choose any color and then click on OK.

choose any fill colors to apply conditional formatting in excel

You have set the formatting and condition for blank cells.

condition and formatting is set in Excel

Finally, click on OK.

final output of conditional formatting for blank cells

As you can see, we have successfully applied conditional formatting to blank cells in Excel.

1.2  Conditional Formatting with Formula for Blank Cells in Excel

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

First, select the entire dataset or the range of cells according to your choice. We are selecting the range of cells C5:E11.

select the range of cells for conditional formatting

Then, from the Home tab, go to Conditional Formatting > New Rule.

select new rule from conditional formatting option

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

select the option to apply conditional formatting for blank cells in excel

After that, click on Format.

Now, select the Fill menu. Then choose any color and then click on OK.

choose any fill colors to apply conditional formatting in excel

Now, we have set the formula to find the blank cells and formatting.

formula and formatting is set to apply

Finally, click on OK.

final output of conditional formatting for blank cells in excel

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


Similar Readings


2. VBA codes to Apply Conditional Formatting in Excel

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

First, press Alt+F11 to open the VBA editor. Then select Insert > Module.

insert vba module in Excel

After that, type the following code:

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, select the range of cells C5:E11.

select the range of cells to apply conditional formatting

Now, press Alt+F8 to open the Macro dialog box. Select format_blank_cells.

Macro dialog box in excel

Finally, click on Run.

final ouput of conditional formatting on blank cells in excel

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: 3 Tips


Skip Conditional Formatting for Blank Cells

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:

pre applied conditional formatting in excel

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

First, select the range of cells E5:E11.

select the range of cells

Then, from the Home tab, go to Conditional Formatting > New Rule. Now, from there you can choose any of the previous methods that we discussed to format blank cells. Follow those steps.

The method is the same. But this time do not select any Format. Leave that empty for blank cells.

type the formula and leave the format option emptyselect blanks option and keep the format option empty

Then, from the Home tab, go to Conditional Formatting > Manage Rules.

manage rules option from conditional formatting option

Make sure you have marked those checkboxes for your method.

mark the checkboxes to skip the conditional formatting for blank cells in Excel

Now, click on OK.

final output of skip conditional formatting for blank cells in excel

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 Conditional Formatting of 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.

Method 1: Using the Quick Analysis option

📌 Steps

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.

select clear rules to remove conditional formatting for blank cells in excel

Method 2:  Using Conditional Formatting Command

You can remove the conditional formatting by going to Conditional Formatting  > Clear Rules. Then, choose any of these options.

remove conditional formatting from conditional formatting option in excel

Read More: How to Copy Conditional Formatting to Another Cell in Excel (2 Methods)


💬 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.


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. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo