How to Highlight Blank Cells with Conditional Formatting in Excel

Get FREE Advanced Excel Exercises with Solutions!

While working with a dataset in an Excel file, it is very normal that some cells may remain blank. It may hinder your work and result in errors if you apply any formula referring to the blank cells. If you want to avoid these problems, then highlighting the blank cells could be one of the best solutions. In this case, Conditional Formatting will pave the way for highlighting the blank cells in your Excel file.  In this article, I will show you how to highlight blank cells with conditional formatting in Excel.


Download Practice Workbook

You can download the practice book from the link below.


2 Ways to Highlight Blank Cells with Conditional Formatting in Excel

There are several ways to check whether an Excel cell is blank or not.  In this section, you will find 2 easy and effective ways to highlight blank cells with conditional formatting in Excel. I will demonstrate them one by one here. Let’s check them now!


1. Format Blank Cells with Conditional Formatting

Let’s say, we have got a dataset of some customers’ information: their Address, ordered Products, Price, Delivery Date, etc.

Dataset to Highlight Blank Cells with Conditional Formatting in Excel

You can see that all the cells don’t contain data, some cells are blank in the dataset. We want to highlight these blank cells with Conditional Formatting. In order to demonstrate this method, proceed with the following steps.

 💡 Steps:

  • First of all, select the range of the data> go to the Home tab> click Conditional Formatting> select New Rule.

Apply Conditional Formatting Commmad in Excel

  • Then, the New Formatting Rule dialogue box will show up.
  • Here, click Format only cells that contain in the Select a Rule Type field and choose Blanks from the dropdown of the Format only cells with field.

New Formatting Rule dialogue box to Highlight Blank Cells

  • Now, click Format.

  • After that, the Format cells common box will appear.
  • Here, from the Fill icon, choose a color type and click OK to close the command box.

Choose a Color to Highlight Blank Cells

  • Now, closing this box will return you to the New Formatting Rule dialogue box. Click OK to close this box also.

  • Hence, your blank cells will be highlighted with the color format you have chosen.

Highlight Blank Cells with conditional Formatting in Excel


2. Highlight Blank Cells Applying Conditional Formatting with Formula

For our same set of data, we will again apply Conditional Formatting to highlight the blank cells. For the previous method, we have applied the format for blank cells. Now, we will apply the Conditional Formatting Formula to highlight blank cells. Let’s follow the steps with proper illustrations.

 💡 Steps:

  • Firstly, select the data range> go to the Home tab> click Conditional Formatting> select New Rule.

  • Then, the New Formatting Rule dialogue box will show up.
  • Here, click Use a formula to determine which cells to format in the Select a Rule Type field and apply the following formula to the Format values where this formula is true field.

=ISBLANK(C5)=TRUE

  • Now, click Format.

Apply Conditional Formatting with Formula to Highlight Blank Cells in Excel

  • After that, the Format cells common box will appear.
  • Here, from the Fill icon, choose a color type and click OK to close the command box.

  • Again, click OK to close the New Formatting Rule dialogue box.

  • As a result, the cells that are blank will be highlighted with the selected color.

Highlight Blank Cells with Conditional formatting

Read More: How to Highlight Cell If Value Is Less Than Another Cell in Excel


How to Remove Conditional Formatting

As you can apply Conditional Formatting to a cell, Excel also allows you to remove the formatting you have applied. Here, we will learn different methods for removing Conditional Formatting.


1. Remove Conditional Formatting Keeping Blank Cells Highlighted

Previously we have shown you the application of Conditional Formatting to highlight the blank cells. Let’s say, you want to remove the formatting but still want to keep the blank cells highlighted. Don’t worry, Excel is with you!

In order to do so, just proceed with the steps below.

 💡 Steps:

  • First of all, select the whole dataset, and click a cell where you want the dataset after removing the format.
  • Then, click the Clipboard icon from the Home tab.

  • Now, on the left side of the Excel window, the copied item will appear. Click on this.

  • Now, your selected dataset will be pasted in the cell that you have clicked at first.

Paste the Highlighted Blank Cells

  • Now, delete the columns that contain the Conditionally Formatted dataset.

Delete Formatted data

  • Hence, your newly pasted data will be repositioned.

  • You can check whether Conditional Formatting is removed or not by entering some text into a blank cell. If the formatting is removed, the cell won’t change the color.

Check whether Formatting is Removed or Not


2. Undo Conditional Formatting by Clearing Rules

You can also remove the Conditional Formatting without keeping the cell highlighted. For this case, you have to clear the rules.

 💡 Steps:

  • Here, select the cells of the data range and the Quick Analysis tool will appear at the right end of the selected data. Click on it.
  • Now, click Clear from the formatting icon.

Quick Analysis tool to Remove Conditional Formatting

  • Hence, the cells will clear the formatting totally. It won’t keep the highlighted cells anymore and make the dataset just like it was before formatting.


Conclusion

In this article, I have tried to show you some methods to highlight blank cells with conditional formatting in Excel. I hope this article has shed some light on your way of this. Thanks for reading the article! If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly visit our website. Keep learning. Happy Excel! 🙂


Related Articles

Rafiul Hasan

Rafiul Hasan

Hey there! I am Md. Rafiul Hasan. Currently, I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our work time and made it easy for us to perform quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo