To highlight rows that contain one or more blank cells. you can conditional formatting with a simple formula based on the COUNTBLANK function.

In the example shown, the range B5:F15 is formatted with a conditional formatting rule based on this formula:


Note: it's important that CF formulas be entered relative to the "first cell" in the selection, B5 in this case.

Once you save the rule, you'll see the rows that contain one or more blank cells highlighted.

Generic formula



Conditional formatting is applied to all cells in the active selection at the time a rule is created.

In this case, the column references are locked to prevent columns from changing as the formula is evaluated, but the row references are relative so that row numbers are free to change. The result is a formula that applies exactly the same logic to every cell in the same row.

If COUNTBLANK finds any blank cells in a given row, it returns a positive number, which Excel evaluates to TRUE, triggering the rule.

If COUNTBLANK finds no blank cells, it returns zero, which is evaluated as FALSE, and the formatting is not triggered.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.