How to Highlight Blank Cells in Excel using Conditional Formatting [Quick Tip]

 

With huge data pinpointing blank cells is near to impossible. Sometimes back we learnt how to fill blank cells with zero using Go-To special feature in Excel. But what if we only want to highlight the cell? This can easily be done in the blink of an eye using Excel’s conditional formatting.

Step 1: Select the range in which you want to highlight blank cells.

Step 2: Go to home tab > Styles group > click conditional formatting drop down button and click on New rules.

Step 3: New formatting rule dialogue box will open. From this click on the last option that says “Format only cells that contain”

Step 4: From the drop down select blanks

Step 5: Hit the format button to make changes the way you like blank cells to appear and hit OK button. DONE!

Following animation walks you through the process described above:

Bonus Tip #1: Use ISBLANK() formula

The above method used the built-in command in Excel’s conditional formatting but we can have the same thing done using a formula. Following steps help you understand the process.

Step 1: Select the range in which you have blank cells to be highlighted

Step 2: Go to Home tab > styles group > Conditional formatting > New rules

Step 3: This time select “Use a formula to determine which cells to format”

Step 4: In the input bar we will put ISBLANK() formula with the first cell in the selected range as a value. Care is needed in this case as you must know what is the first cell in the selected range which you will mention to let Excel calculate and apply formatting. In my case the first cell in the selected range is D1 so my formula is:

=ISBLANK(D1)

Bonus Tip #2: Use Common sense

Instead of ISBLANK formula you can use common sense to invoke condition. All the steps are same as Bonus tip #1 except the formula which will be as follows:

=D1=””

This formula is checking if cell D1 is empty then format it otherwise leave it as it is. In this method too you must know the first cell in the range.