Highlighting Empty Cells

Denise has a worksheet that contains thousands of rows of data. Scattered within that data are numerous empty cells. Denise wonders if there is a quick way to highlight, in yellow, all of those empty cells.

Before getting into the techniques you can apply, it is important to understand what "empty" means. In Excel parlance, a cell can "look empty," or it can actually be empty. A cell looks empty if it contains a formula that returns an empty string or if it returns a numeric zero but you have the display of zero values suppressed. A cell that is actually empty contains nothing. Excel refers to these empty cells as being "blank."

A very easy way to highlight blank cells is to follow these steps:

1. Press F5. Excel displays the Go To dialog box. (See Figure 1.)



Figure 1. The Go To dialog box.

2. Click the Special button. Excel displays the Go To Special dialog box. (See Figure 2.)



Figure 2. The Go To Special dialog box.

3. Select the Blanks radio button.
4. Click OK. Excel selects all the empty sells in the worksheet.
5. Use the tools on the Home tab of the ribbon to fill the selected cells any way you desire.

Once you apply highlighting in this manner, the effect is static. That means that if you clear cells or add cell content within the worksheet, the highlighting becomes increasingly inaccurate. If you want something more dynamic, it is best to create a conditional formatting rule to handle the highlighting. Follow these steps:

1. Select cell A1.
2. Press Ctrl+A to select all the cells in the worksheet. (If you want the conditional formatting rule to apply to just a range of cells, then select just those cells.)
3. Make sure the Home tab of the ribbon is displayed.
4. Click the Conditional Formatting tool in the Styles group. Excel displays a series of choices.
5. Click New Rule. Excel displays the New Formatting Rule dialog box.
6. In the Select a Rule Type area at the top of the dialog box, choose Format Only Cells that Contain. Excel changes the options at the bottom of the dialog box. (See Figure 3.)



Figure 3. The New Formatting Rule dialog box.

7. Using the first drop-down list in the Edit the Rule Description area, choose Blanks.
8. Click Format to display the Format Cells dialog box.
9. Set the formatting options to highlight the Fridays as desired.
10. Click OK to dismiss the Format Cells dialog box.
11. Click OK.

The result is that all blank cells are highlighted (formatted) as you specify in steps 8-10. As your data changes in the worksheet, then the highlighted cells change as well.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13646) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.