Apply Conditional Formatting on Blank Cells in Excel

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

1. First, select the range where you have data.
   
2. Afterward, go to the Home Tab > Conditional Formatting > Highlight Cells Rules > More Rules.
   
3. Select blank from the “Format only cells with” in the New Rule dialog box.
   
4. Next, click the “Format…” button and specify the format you want to apply to the blank cell.
   
5. 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

1. Go to the Home Tab > Conditional Formatting > New Rule to use a custom formula.
   
2. In the New Rule dialog box, click on the “Formula to determine which cell to format…” and enter the formula =OR(A1=””,A1=” “).
   
3. Now click on the “Format…” button and specify the format to apply to the blank cell.
   
4. 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.