Conditional Formatting

What conditional formatting actually is

Conditional formatting is a set of rules attached to a cell range that Excel evaluates every time the sheet recalculates. Each rule says, in effect, 'if this condition is true for a cell, apply this formatting' — and Excel checks every rule, top to bottom, for every cell in the range.

Built-in rule categories

Excel groups its presets into families that cover the most common at-a-glance reporting needs:

• Highlight Cell Rules — greater than, less than, between, text contains, duplicate values
• Top/Bottom Rules — top 10 items, top 10%, above/below average
• Data Bars — an in-cell horizontal bar proportional to the value, great for quick magnitude comparison
• Color Scales — a gradient (e.g. red-yellow-green) applied across a range based on relative value, ideal for heat maps
• Icon Sets — arrows, traffic lights, or flags applied based on thresholds

Formula-based rules unlock everything else

The built-in presets only look at the value of the cell being formatted. A formula-based rule can reference any other cell in the row, meaning you can highlight an entire row based on a status column, a due date, or a comparison between two other columns — this is where conditional formatting goes from decoration to real business logic.

Rule precedence and evaluation order

Rules are evaluated top-to-bottom as listed in the Conditional Formatting Rules Manager, and by default a later rule doesn't cancel an earlier one — both can apply unless you check 'Stop If True' on the earlier rule to prevent lower rules from also firing on the same cell.

1. Step 1 — Apply a built-in Highlight Cell Rule

Select a numeric range, go to Home → Conditional Formatting → Highlight Cell Rules → Greater Than. Enter a threshold (e.g. 10000) and pick a preset fill/font color, then click OK.

2. Step 2 — Flag duplicates in a list

Select a column of IDs or names, then Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values. Every repeated entry is instantly colored, which is essential before importing a list into a system that requires unique keys.

3. Step 3 — Add a Color Scale heat map

Select a numeric grid (e.g. monthly sales by region), then Home → Conditional Formatting → Color Scales → pick Red-Yellow-Green. High values shade green, low values shade red, with an automatic gradient in between — no manual thresholds needed.

4. Step 4 — Add Data Bars for magnitude comparison

Select a numeric column, Home → Conditional Formatting → Data Bars → pick a gradient or solid fill. Each cell gets an in-cell bar proportional to its value relative to the range, letting you compare magnitudes visually without reading numbers.

5. Step 5 — Write your first formula-based rule

Select the full data range starting from row 2 (skip headers). Go to Home → Conditional Formatting → New Rule → 'Use a formula to determine which cells to format.' Enter a formula referencing the leftmost column of the selection with a relative row reference and absolute column reference, then set the fill color.

Selection: A2:E200 (whole table body)
Formula: =$C2="Overdue"
Format: Fill = light red

Result: Every column in any row where column C
says "Overdue" is highlighted, not just column C.

6. Step 6 — Build a due-date warning rule

For a project tracker with a Due Date column, write a formula rule like =AND($D2<TODAY(),$E2<>"Complete") applied to the whole row range — this highlights any row that is both past its due date and not yet marked complete, combining two conditions with AND.

7. Step 7 — Compare two columns with a formula rule

To flag when Actual exceeds Budget, select the Actual column and use the formula =B2>C2 (where B is Actual and C is Budget in the first data row), applied with a relative reference so it adjusts per row automatically.

8. Step 8 — Manage, reorder, and edit rules

Home → Conditional Formatting → Manage Rules opens the Rules Manager, listing every rule on the sheet in evaluation order. Use the up/down arrows to reorder, Edit Rule to adjust formulas or ranges, and the checkbox for 'Stop If True' to prevent lower-priority rules from also firing.

9. Step 9 — Scope rules correctly with 'Applies to'

In the Rules Manager, double-check the 'Applies to' range for each rule. A common error is a rule accidentally scoped to only part of a table (e.g. it was created after selecting just one column) rather than the full intended range.

10. Step 10 — Clear rules cleanly

To remove formatting, use Home → Conditional Formatting → Clear Rules → 'Clear Rules from Selected Cells' or 'Clear Rules from Entire Sheet' rather than manually removing fill colors, which won't actually delete the underlying rule.

What's the difference between a built-in rule like Data Bars and a formula-based rule?

Built-in rules only evaluate the value of the cell being formatted; formula-based rules can reference any other cell, enabling logic like highlighting a whole row based on a different column's status.

Why should a formula rule use $C2 instead of $C$2 when applied across multiple rows?

$C2 locks the column but lets the row reference shift for each row in the selection, so the condition is evaluated correctly per row; $C$2 would lock every row to check row 2 only.

What does 'Stop If True' do in the Rules Manager?

It prevents lower-priority rules in the evaluation order from also applying to a cell once a higher-priority rule's condition has already matched, avoiding conflicting overlapping formats.

Why might removing a cell's fill color manually not actually fix unwanted conditional formatting?

Manual fill removal doesn't delete the underlying rule, so the color will reappear whenever the rule's condition evaluates true again; you must use Clear Rules instead.

How would you highlight an entire row when a Status column says 'Overdue'?

Select the full row range across all relevant columns, then create a formula rule like =$C2="Overdue" (with C being the Status column) so the mixed reference applies the condition consistently to every column in that row.

Why is it useful to base conditional formatting ranges on an Excel Table?

Table ranges auto-expand as new rows are added, and formatting/rules tied to the table are more likely to extend automatically, avoiding the common problem of new rows being left unformatted.

Can I apply more than one conditional formatting rule to the same range?

Yes, and it's common — just manage evaluation order and use Stop If True where rules would otherwise visually conflict.

Does conditional formatting slow down large workbooks?

Complex formula-based rules across very large ranges (hundreds of thousands of cells) can add minor recalculation time, but typical business-size ranges perform fine.

Can conditional formatting reference another worksheet?

Not directly for the value comparison in most cases — Excel restricts formula rules to referencing cells on the same sheet as the formatted range; use a helper column pulling the value in first if you need to compare across sheets.

How do I copy conditional formatting to another range?

Use Format Painter after selecting a formatted cell, or Paste Special → Formats, which carries the conditional formatting rule along with regular formatting.

What happens to conditional formatting when I sort or filter the data?

Formula-based rules using relative row references still evaluate correctly after sorting because Excel recalculates the formula per row; built-in rules like Top 10 also recompute automatically against the current visible/full range.

Can I use conditional formatting to compare a cell to today's date automatically?

Yes — reference TODAY() directly inside a formula rule, such as =$D2<TODAY(), and it recalculates live every time the file is opened or recalculated.

Is there a limit to how many conditional formatting rules a sheet can have?

There's no strict practical limit for typical business use, but sheets with dozens of overlapping rules become hard to maintain and audit — keep rules purposeful and documented.