Table of Contents
To apply conditional formatting to blank cells in Excel, select the cells you wish to format, and then click on the Home tab in the ribbon. In the Styles group, click the drop down arrow next to Conditional Formatting and select New Rule. In the New Formatting Rule dialog box, select Format only cells that contain, then select Blanks from the first drop down menu and then click Format. Finally, select a format from the Format Cells dialog box and click OK twice.
There are two ways to apply conditional formatting to blank cells in Excel:
1. Choose Format only cells that contain within the Conditional Formatting box.
2. Choose Use a formula to determine which cells to format within the Conditional Formatting box.
The following examples show how to use each method with the following dataset in Excel that shows the points scored by various basketball players during different games:
Example 1: Highlight Blank Cells Using “Format only cells that contain”
First, highlight the cells in the range A2:D11, then click the Conditional Formatting dropdown menu on the Home tab and then click New Rule:
In the new window that appears, click Format only cell that contain, then click the dropdown arrow under Format only cells with, then click Blanks.
Then click the Format button and choose a fill color.
Once we press OK, all of the blank cells in the range A2:D11 will be highlighted:
Note: We chose to use a light green fill for the conditional formatting in this example, but you can choose any color and style you’d like for the conditional formatting.
Example 2: Highlight Blank Cells Using “Use a formula to determine which cells to format”
First, highlight the cells in the range A2:D11, then click the Conditional Formatting dropdown menu on the Home tab and then click New Rule:
Then click the Format button and choose a fill color.
Once we press OK, all of the blank cells in the range A2:D11 will be highlighted:
To ensure that the formula works correctly, be sure to use the cell in the top left corner of your range of cells in the ISBLANK() function.
In this example, we used cell A2 since it represented the cell in the top left corner of our range.