In Excel, you can use conditional formatting to highlight blank cells with a cell color or a color to the font of the value in the text. This tutorial will teach us to do it with two different methods.
For example, below are a few dates; some of the cells are blank between dates, and you need to highlight blank cells.
Steps to Use Blank Cell Option in Conditional Formatting
- First, select the range where you have data.
- Afterward, go to the Home Tab > Conditional Formatting > Highlight Cells Rules > More Rules.
- Select blank from the “Format only cells with” in the New Rule dialog box.
- Next, click the “Format…” button and specify the format you want to apply to the blank cell.
- In the end, click OK to apply.
Once you click OK, all the blank cells will highlight with the specified color.
You can use some ways to highlight the blank cells, but this conditional formatting works better. Let me tell you a few examples to understand why I’m saying this.
In the above example, the select cell has a few spaces but conditional formatting still considers it blank.
Using a Custom Formula in the Conditional Formatting for Blank Cells
- Go to the Home Tab > Conditional Formatting > New Rule to use a custom formula.
- In the New Rule dialog box, click on the “Formula to determine which cell to format…” and enter the formula =OR(A1=””,A1=” “).
- Now click on the “Format…” button and specify the format to apply to the blank cell.
- And then click OK to apply.
And once you click OK, you get all the cells highlighted.
The formula we used in the test two conditions is whether a cell is blank, and the second is if the cell has a space.
But I’d suggest you use the first method.
Get the Excel FileDownload
More on Conditional Formatting
- Apply Conditional Formatting to a Pivot Table
- Remove Conditional Formatting
- Compare Two Columns using Conditional Formatting
- Apply Conditional Formatting Based on Another Cell
- Apply Conditional Formatting Based on Another Column
- Copy Conditional Formatting
- Apply Conditional Formatting to an Entire Column in Excel
- Highlight Rows using Conditional Formatting in Excel
- Apply Multiple Conditions in Conditional Formatting in Excel
- Why Conditional Formatting Not Working in Excel
- Highlight IF a Cell Contains a Specific Text
- Apply Conditional Formatting Based on a Date
- Applying Color Scales using Conditional Formatting
- Data Bars in Excel using Conditional Formatting
- Stop IF True in Conditional Formatting
- Find and Highlight Duplicates with Conditional Formatting
- Icon Sets in Excel