Conditional Formatting

Instead of you deciding which cells look a certain way, you write a rule and Excel applies the formatting automatically to every cell that satisfies the condition — and removes it the moment it no longer does.

You find Conditional Formatting on the Home tab → Styles group. It is always applied to a selected range — every cell in that range is evaluated against the rule independently, and the formatting is applied only to cells where the condition is true.

Highlight Rules

You define a condition — greater than, less than, equal to, or containing specific text — and Excel applies the chosen format to every matching cell. Cells that don't match the condition are untouched.

Home → Conditional Formatting → Highlight Cells Rules → Greater Than / Text That Contains / etc.

Colour Scales

Instead of a fixed threshold, colour scales assign a colour gradient across the entire range based on relative value. The lowest values get one colour, the highest get another, and everything in between is shaded proportionally. This makes outliers and trends visible at a glance without knowing the exact numbers.

Home → Conditional Formatting → Colour Scales → choose a 2-colour or 3-colour scale.

Blank Cell Rules

A blank cell in a column that should always have a value — a date, an amount, a status — is a data quality problem. It may cause formulas to return incorrect results, COUNT and COUNTA to diverge unexpectedly, or reports to be incomplete. Highlighting blanks makes these gaps visible immediately rather than letting them hide in a large table.

Home → Conditional Formatting → New Rule → Format only cells that contain → Blanks.

Managing and Editing Rules

Once rules are applied, you will sometimes need to edit, reorder, or remove them. Excel evaluates multiple rules on the same range in priority order — the first rule that matches wins, unless you check "Stop If True" on a rule to prevent lower-priority rules from also applying. You manage all of this through the Rules Manager.

Home → Conditional Formatting → Manage Rules.