It’s hard to recognize blank cells in a large data, because a blank cell is just a white cell without any value.
In most of the cases, blank cells represent some sort of gap missing data, or a formula returns an empty string resulting in a blank cell.
And that’s why it’s recommended to highlight these cells with a color so that we can recognize them.
You might be wondering: Which is the best method to find and highlight cells that are blank? But here’s the kicker:
It depends on you as there are more than one ways for this and you can use any of these ways to get your work done. All of these methods are unique.
So today, in this post, I’d like to share with you 3 different methods which you can use to highlight blank cell in Excel. So let’s get started.
Here we have below table where we have some empty cells. You can download it from here to follow along.
1. Select and Highlight Blank Cells with GoTo Special
If you want to apply color to all the blank cells from a range, go to special option can be an easy to use way. This method is simple ➤ select all the blank cells and apply cell color. Yes, that’s it.
Below are the steps you need to follow to highlight all the blank cells.
- First of all, select your entire data.
- Go to Home Tab -> Find & Select -> Click on “Go To Special”.
- From Go To Special window, select “Blank” and click OK. (It will select all the blank cells from the table.)
- After that, go to Home Tab and apply the color to highlight the cells.
Important Note: If you want to use a dynamic method, you apply conditional method, see the next method.
2. Apply Conditional Formatting to Highlight Blank Cells
- First of all, select the data table.
- Go to -> Home tab -> Conditional Formatting.
- In conditional formatting options, select “highlight cell rules” and click ” more rules”.
- Now, from rule description select the “Blank” from the drop menu.
- After that, click on Format button to open formatting option.
- From formatting options, select the color you want to use for highlighting.
- Click OK.
And, the best part is, if you have blank cells with a formula it will also highlight it and if you have pivot table instead of a normal table, the steps will be same.
Quick Tip: It’s a one-time setup, you don’t have to apply it again and again. Use a table in your data and conditional formatting will extend when you add new data to the table.
3. Using a VBA Code to Highlight Blank Cell
I know you have an unconditional love for VBA. And, that’s why we have a macro code to apply color to all the blank cells from a selected range.
Sub ColorBlankCells()
Dim rng As Range
Set rng = Selection
rng.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed
End Sub
When you run this code it will check each cell from the range and then apply red color if a cell is blank.
Conclusion
So these are the 3 methods which you can use to highlight blank cells. You can use any of these methods which you think is a perfect for you.
And, if you ask me I always prefer to use conditional formatting because it’s dynamic, so you don’t need to apply it again and again.
I hope these methods with help you in your daily work.
Now tell me one thing.
Do you have any other method to apply color to blank cells?
Please share with me in the comment section, I would love to hear from you. And, please don’t forget to share this tip with your friends.
Must Read Excel Tutorials
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.
I used conditional formatting to point out data entry cells and cells with formulas.
On all spreadsheets I use:
Formula:=ISBLANK($A$1:$F$5) as rule 1 highlighted light yellow
and
Formula:=ISFORMULA($A$!:$F$5) as rule 2 highlighted light green
The yellow cells reminds me to enter data and the green cells not to change.
I liked very much
One more way that sometimes helps me: I take a sharpie and mark them on the screen. I usually use my right most screen because I don’t use it as much as the other two and the sharpie will look silly if the Excel spreadsheet is not up. Note: this is not a dynamic solution. It is very much a static fix.
I don’t have another way, but would like to say that conditional formatting would be the only way I’d do it. Only downside is Excel does corrupt it’s conditional formatting from time to time so you may need to repair them, which means you need to document what they are, why, etc so you can reset them when that happens.
Manually highlighting the cells would be okay in a one-off spreadsheet (because values won’t really change), but would look odd when later on a value appeared!
I avoid VBA as much as possible, hence I don’t have a personal library available to any & every XLSX I create – others would be unlikely to have the same so the spreadsheet wouldn’t work for them. Also just like using “Goto Special”, the VBA is also a one-time event. The user would need to then rerun the macro to update the highlighting.
Last point – the VBA should be enhanced to revert the colour back to default if it not blank so it would not remain highlighted if the cell now has a value.
I had not seen the Conditional Formatting “Format only cells that contain” option BLANK … Thanks, my lesson for today!.
I use Conditional Formatting “Use a formula to determine which cells to format” with =isBlank () and =isFormula () to shade blank cells yellow and cells with formulas light green. Keeps me from missing something and messing up equations.
Thanks, as always; a fantastic support.