Conditional Formatting¶

Conditional formatting makes data speak visually — red flags, colour scales, data bars. Used well, it lets a reader spot the important cells in a sea of numbers in one glance. Used badly, it turns a spreadsheet into a rainbow nobody can read.

Learning Objectives¶

Apply built-in conditional formatting rules
Create formula-based custom rules
Use colour scales, data bars, and icon sets appropriately
Avoid over-formatting

Built-In Rules (Home → Conditional Formatting)¶

Highlight Cells Rules¶

Greater Than       → flag values above a threshold
Less Than          → flag values below
Between            → flag a range
Equal To           → flag a specific value
Text That Contains → flag text matches
Duplicate Values   → flag duplicates (great for data cleaning)

Example: Highlight all orders over £500 in green: Home → Conditional Formatting → Highlight Cells Rules → Greater Than → 500 → choose green fill.

Top/Bottom Rules¶

Top 10 Items / Top 10%        → highlight best performers
Bottom 10 Items / Bottom 10%  → highlight worst
Above/Below Average           → flag relative to the mean

Colour Scales, Data Bars, Icon Sets¶

Type	What it shows	Best for
Colour Scales	Gradient (e.g., red→yellow→green)	Heatmaps — performance across a grid
Data Bars	In-cell bar proportional to value	Quick magnitude comparison in a list
Icon Sets	Arrows/traffic lights	Status indicators (up/down, good/bad)

Colour scales make instant heatmaps

Apply a red-yellow-green colour scale to a pivot table of revenue by region × month, and you instantly see hot and cold spots without reading a single number.

Formula-Based Custom Rules (The Powerful Part)¶

When built-in rules aren't enough, write a formula. The rule applies the format wherever the formula returns TRUE.

Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format".

' Highlight the ENTIRE ROW where status is "cancelled"
=$D2 = "cancelled"
' (apply to the whole data range; note $D2 locks the column, not the row)

' Highlight orders that are both high-value AND pending
=AND($G2 > 500, $D2 = "pending")

' Highlight rows where actual is below budget
=$C2 < $B2

' Flag duplicate IDs
=COUNTIF($A:$A, $A2) > 1

' Highlight weekends in a date column
=WEEKDAY($A2, 2) > 5

The $ in formula rules controls what gets highlighted

To highlight an entire row based on one column's value, lock the column with $ but leave the row relative: =$D2 = "cancelled". If you write =$D$2 it only checks one cell; if you write =D2 the column reference shifts as Excel applies the rule across columns. This is the most common conditional-formatting mistake.

A Worked Example — Financial Variance Report¶

Highlight unfavourable variances (actual below budget) in red and favourable in green:

Select the variance column
New Rule → formula =$C2 < 0 → red fill (unfavourable)
New Rule → formula =$C2 >= 0 → green fill (favourable)

Now anyone scanning the report sees problems instantly — exactly what a CFO wants.

Managing and Clearing Rules¶

Manage Rules: Home → Conditional Formatting → Manage Rules — see, edit, reorder, and delete all rules. Rules apply top-down; order matters.
Clear Rules: Home → Conditional Formatting → Clear Rules (from selection or whole sheet).
Stop If True: in Manage Rules, tick this to prevent lower rules from also applying.

Don't Over-Format¶

Rainbow spreadsheets communicate nothing

If everything is highlighted, nothing stands out. Conditional formatting works by exception — highlight the few cells that need attention, leave the rest plain. A report where 10% of cells are coloured is readable; one where 80% are coloured is noise.

Good practice: - Reserve red for problems/alerts - Use one colour scale per table, not three - Highlight outliers and exceptions, not normal values

Practice Exercises¶

Warm-up 1. Highlight all order values over £300 in green. 2. Use Duplicate Values to flag duplicate customer IDs. 3. Apply a data bar to a column of sales figures.

Main 4. Apply a red-yellow-green colour scale to a revenue-by-region pivot to create a heatmap. 5. Write a formula rule to highlight the entire row where an order is "cancelled". 6. Highlight rows where actual revenue is below budget (a variance report).

Stretch 7. Build a status dashboard: use a formula rule + icon set so each KPI shows a green/amber/red indicator based on whether it's above target, near target, or below. 8. Highlight weekend dates in a date column using WEEKDAY.

Interview Questions¶

[Beginner] What is conditional formatting used for?

[Beginner] How would you highlight all values above a certain threshold?

[Mid-level] How do you highlight an entire row based on the value in one column?

[Mid-level] What's the difference between a colour scale and a formula-based rule, and when would you use each?

[Senior] A stakeholder's report has so much conditional formatting it's unreadable. How do you advise them to fix it?

Previous: 03-lookup-functions | Next: 05-excel-shortcuts