How to Use Conditional Formatting with Blank Cells in Excel

We have already shown various benefits of conditional formatting and its uses. However, it was not shown how to use this useful functionality when we have blank cells in our table.

We will show how to use this in the example below.

Conditional Formatting of Blank Cells with Rule

For our example, we will use the list of all Marvel movies that were released until the end of 2019 with their release year, revenue rating (compared to each other), titles, U.S. release date, and box office gross revenue.

Table

Description automatically generated

We can see that we lack certain years in the first column and certain box office revenues figures. We want to highlight all the rows in the first column where years are missing. To do so, we will select our range (A2:A24) go to the Home tab >> Styles >> Conditional Formatting >> New Rule:

Graphical user interface, application

Description automatically generated

On a pop-up window that appears, we will choose Format only cells that contain, and will choose “Blanks” on Format only cells with dropdown. We will then click Format and choose any color in the Fil tab (in our case it will be green). Our pop-up window will look like this:

Graphical user interface, text, application

Description automatically generated

When we click OK, our table will look like this:

Table

Description automatically generated

Conditional Formatting of Blank Cells with Formula

There is also a formula that we could use to get the same results. That formula is ISBLANK. This formula checks desired cell or range and returns the value TRUE if it is empty, and FALSE if it is not.

We want to highlight the names of the movies for which we lack box office revenue gross. We will select the range C2:C24 and follow the same steps as in the first example (Home tab >> Styles >> Conditional Formatting >> New Rule).

On the pop-up window that appears, this time we will choose another option- Use a formula to determine which cells to format, and we will input the following formula:

=ISBLANK(E2)

We will choose these cells that are found to be filled in yellow color:

Graphical user interface, text, application, email

Description automatically generated

When we click OK now, we will notice that rows 4, 10, and 20 are the ones populated with yellow color in column C, and those are the ones that do not have values in column E:

Table

Description automatically generated