Conditional Formatting

Topic 03

Conditional Formatting

Make your data speak visually — colour cells, rows, or values automatically based on rules you define. This topic covers the three approaches to conditional formatting and ends with two advanced techniques that use formulas as the rule engine.

3 approaches 2 advanced techniques Training file: conditional.xlsx

The Three Approaches

Conditional formatting can be applied three ways. Most trainees already know the first two — the power is in the third.

Predefined Conditions
Ready-made rules with a simple value to fill in. Fast to apply, limited in flexibility.
Highlight Cells > X
Cells containing text
Duplicate values
Top / Bottom 10 items
Above / Below average
Visual Scales
No conditions to write — Excel colours or annotates cells based on relative values. Great for at-a-glance reading.
Color Scale — green→red gradient
Data Bars — mini bar chart in cell
Icon Sets — arrows, traffic lights
Custom Formula Rule
Full control — you write a formula that returns TRUE or FALSE. Same logic as IF, COUNTIF, etc. Used in both advanced examples below.
=$A2<300000
=B2=MAX($B2:$E2)
=SEARCH("south", $B2)
💡
Where to find it
Home tab → Conditional Formatting. The menu gives you Highlight Cell Rules, Top/Bottom Rules, Data Bars, Color Scales, Icon Sets — and at the bottom: New Rule for full formula control.

Training Exercise

Open the exam results file and apply each condition below. The dataset has 23 candidates with Candidate ID, Area, Gender, Age, and Result columns.

Download conditional.xlsx
1
Candidate ID — less than 300,000
Column: Candidate ID < 300000 Yellow fill
Select Candidate ID column → Conditional Formatting → Highlight Cell Rules → Less Than… → 300000
2
Area — contains "south"
Column: Area contains "south" Green fill
Select Area column → Conditional Formatting → Highlight Cell Rules → Text that Contains… → south
📌
This matches South-East, South-West, and any value with "south" in it. The match is not case-sensitive.
3
Gender — equal to F
Column: Gender = "F" Red fill
Select Gender column → Conditional Formatting → Highlight Cell Rules → Equal To… → F
4
Age — greater than 40
Column: Age > 40 Blue fill
Select Age column → Conditional Formatting → Highlight Cell Rules → Greater Than… → 40
5
Result — Top 5%
Column: Result Top 5% Red fill
Select Result column → Conditional Formatting → Top/Bottom Rules → Top 10%… → change 10 to 5

Advanced Techniques

Both examples use the Custom Formula approach — the most powerful form of conditional formatting. The formula is evaluated for every cell in the selected range and the format is applied whenever it returns TRUE.

Advanced 01
Colour the Whole Row Based on Candidate ID
Instead of colouring just the Candidate ID cell, we want the entire row to turn yellow when the ID is below 300,000. The trick is in selecting the full data range first, then writing a formula that always checks column A — no matter which column the current cell is in.

How to apply it

1
Select the entire data range — all columns, all rows (e.g. A2:E24). Do not include the header row.
2
Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3
Enter the formula below, choose a yellow fill, and click OK.
= $A2 < 300000
🔑
Why the $ is on the column, not the row
Excel evaluates this formula for every cell in A2:E24. For a cell in column C, row 5, it checks $A5 < 300000. The $A keeps the check always in column A (the ID column), while the row number adjusts freely — so the whole row gets the same TRUE/FALSE result. Remove the $ and each column checks a different column, breaking the effect.
💡
The active cell rule
When you write the formula, reference the first row of your selection. If your selection starts at row 2, write $A2 — not $A1. Excel adjusts the row for every subsequent row automatically.
Advanced 02
Highlight the Highest & Lowest Value in Each Row
Given a grid of student scores, highlight the best score in each row (green) and the weakest (red). The formula must compare each cell against the full range of its own row — and stay in that row even when copied across columns.

Sample Dataset

Student Math Science English History
Ali 85 92 67 78
Sara 45 88 91 55
John 72 65 88 95
Mona 90 43 76 82
Khalid58 79 84 61
Lina 77 56 49 83

Green = highest in row  ·  Red = lowest in row

How to apply it

1
Select only the score cells — B2:E7. Do not include the student name column or the header row.
2
New Rule → Use a formula. Enter the highest formula, set a green fill → OK.
3
Add a second rule for the lowest — same steps, red fill.
The Two Formulas — Applied to range B2:E7
= B2 = MAX($B2:$E2)
Format: green fill — applies when this cell equals the maximum value in its row.
= B2 = MIN($B2:$E2)
Format: red fill — applies when this cell equals the minimum value in its row.
🔑
The mixed reference — locking columns, freeing the row
$B2:$E2 — the $ on B and E locks the column boundaries of the range, ensuring we always look across the four subject columns. The row number (2) has no $, so it adjusts as Excel moves down each row: row 2 checks $B2:$E2, row 3 checks $B3:$E3, and so on. This is the mixed reference from Topic 01 — now used as a practical tool.
✅
Manage Rules order matters
If a student somehow has the same score for both highest and lowest (single-value row), both rules fire. Use Conditional Formatting → Manage Rules to control which rule takes priority — the one at the top of the list wins.
Previous
Topic 02 — Advanced Formulas