Quick, clean, and to the point

Highlight rows with blank cells

Excel formula: Highlight rows with blank cells
Generic formula 

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.


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

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.