Highlight Rows with Missing Data

Excel Formulas › Conditional Formatting

To spot incomplete records at a glance — any row missing a value — use a conditional-formatting rule with COUNTBLANK. The whole row lights up the moment a cell in it is empty.

Functions used (tap for the full reference guide):

How it works

Rows with any empty cell are flagged. The helper column shows the rule’s TRUE/FALSE.

The formula

The rule (applied to A2:C4, the data area):

How it works

The mixed reference is what shades the whole row:

1. COUNTBLANK($A2:$D2) counts the empty cells in the current row. The columns are locked ($A, $D); the row is relative (2) so it moves down per row.
2. If that count is greater than 0, at least one field is missing.
3. The rule is TRUE for the whole row, so every cell in an incomplete row is shaded — an instant “needs attention” flag.
4. Apply it to the full data range so the shading spans all columns.

Try it: interactive demo

Variations

Highlight fully empty rows only

All cells blank, not just one:

Require specific fields

Only Name and Email matter:

Highlight a single empty cell

Cell-level rather than whole-row:

Pitfalls & errors

Practice workbook

Frequently asked questions

Related formulas: Highlight rows with a formula · Count blank cells · Check if a cell is blank

Function references: COUNTBLANK · COUNTA