What Is Conditional Formatting?
Conditional formatting changes the appearance of cells — fill colour, font, border — based on the cell's value or a formula. It's a visual analysis tool: patterns, outliers, and rankings become visible at a glance without any sorting or filtering.
Salary column formatted with a colour scale:
₹62,000 → red (below average)
₹81,000 → yellow (around average)
₹110,000 → green (top earner)
Applying Conditional Formatting
- Select the range to format
- Home → Conditional Formatting → choose a rule type
- Set the condition and formatting
- Click OK
Highlight Cell Rules
Pre-built rules for common comparisons:
Home → Conditional Formatting → Highlight Cells Rules:
| Rule | Highlights cells that are... |
|---|---|
| Greater Than | Above a specified value |
| Less Than | Below a specified value |
| Between | Within a range of values |
| Equal To | Matching an exact value |
| Text That Contains | Containing specific text |
| A Date Occurring | In a relative date range (yesterday, last week, etc.) |
| Duplicate Values | Appearing more than once |
| Unique Values | Appearing exactly once |
Example: Highlight salaries above ₹90,000 in green
Select C2:C11 → Conditional Formatting → Highlight Cell Rules → Greater Than → 90000 → Green Fill
Top/Bottom Rules
Highlight the top or bottom performers automatically:
Home → Conditional Formatting → Top/Bottom Rules:
| Rule | What it formats |
|---|---|
| Top 10 Items | The 10 highest values |
| Top 10% | Values in the top 10 percentile |
| Bottom 10 Items | The 10 lowest values |
| Bottom 10% | Values in the bottom 10 percentile |
| Above Average | Values above the column's average |
| Below Average | Values below the column's average |
Select salary column → Top/Bottom Rules → Top 10 Items → change to 3
→ Highlights only the 3 highest salaries
Data Bars
Show a mini bar chart inside each cell — longer bar = higher value:
Home → Conditional Formatting → Data Bars → choose gradient or solid fill
Salary column with data bars:
EMP-009: ₹62,000 → [▇ ] (short bar)
EMP-001: ₹78,000 → [▇▇▇▇ ]
EMP-005: ₹110,000 → [▇▇▇▇▇▇] (full bar)
Customise: Manage Rules → Edit Rule → set minimum/maximum values; choose fill and border colours.
Tip: For negative values, data bars show on both sides of a midpoint — useful for profit/loss columns.
Colour Scales
Apply a gradient colour across the range — from one colour for low values to another for high values:
Home → Conditional Formatting → Color Scales:
| Scale | Colours |
|---|---|
| 3-colour: Green-Yellow-Red | Green = high, Yellow = middle, Red = low |
| 3-colour: Red-Yellow-Green | Red = low, Yellow = middle, Green = high (percentages, KPIs) |
| 2-colour: White-Blue | White = low, Blue = high (heatmaps) |
| Custom | Set your own colours for min, midpoint, max |
Revenue heatmap by region/month:
Low revenue → pale yellow
Mid revenue → orange
High revenue → dark green
→ Instantly see which months and regions performed best
Customise: Manage Rules → Edit → set the colour for each stop (Minimum, Midpoint, Maximum); set type (Lowest Value, Number, Percent, Percentile, Formula).
Icon Sets
Display icons (arrows, traffic lights, stars, checkmarks) in cells based on value thresholds:
Home → Conditional Formatting → Icon Sets:
| Icon Set | Common Use |
|---|---|
| 3 traffic lights | Red/Yellow/Green status |
| 3 arrows | Directional trends (up/flat/down) |
| 3 symbols | ✓ / ! / ✗ for pass/warning/fail |
| 5 stars | Rating display |
| 4 arrows | Up/slight-up/slight-down/down |
Rating column (1–5):
Rating 5 → ▲ (up arrow / green star)
Rating 3 → → (flat arrow / yellow star)
Rating 1 → ▼ (down arrow / red star)
Customise: Edit rule → set exact thresholds and which icon maps to each threshold.
Show icon only (hide number): Edit Rule → check "Show Icon Only" → the number disappears, only the icon shows.
Formula-Based Conditional Formatting
The most powerful option — use any formula that returns TRUE/FALSE:
- Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format
- Enter formula (TRUE = apply format, FALSE = don't)
Highlight Entire Row
Select the entire data range first (A2:E11), then:
-- Highlight row if Department = Finance:
=$B2="Finance"
→ $B = column B is locked (check B regardless of which column is being evaluated)
→ 2 = row adjusts for each row
-- Highlight row if salary is above average:
=$C2>AVERAGE($C$2:$C$11)
-- Alternate rows for readability (manually, without Tables):
=MOD(ROW(), 2) = 0 → format even rows
=MOD(ROW(), 2) = 1 → format odd rows
Compare Two Columns
-- Highlight if this month's sales are below last month:
=$C2 < $B2 (C = This Month, B = Last Month)
-- Flag missing data:
=ISBLANK($A2) → highlight entire row if name is missing
Highlight Duplicates Across Columns
-- Highlight if employee ID appears more than once:
=COUNTIF($A$2:$A$11, $A2) > 1
Date-Based Rules
-- Highlight if deadline has passed:
=$C2 < TODAY() → red fill
-- Highlight if deadline is within 7 days:
=AND($C2 >= TODAY(), $C2 <= TODAY()+7) → orange fill
-- Highlight weekends in a date column:
=WEEKDAY($A2, 2) > 5 → background colour for Saturday/Sunday
Managing Multiple Rules
Rule Priority
Rules are evaluated top to bottom. The first rule that matches wins (unless "Stop If True" is unchecked).
Home → Conditional Formatting → Manage Rules:
- Shows all rules for the current selection
- Change priority using up/down arrows
- "Stop If True" — if this rule matches, don't apply lower-priority rules
- Edit, delete, or duplicate rules
Example priority order:
1. Formula: =$C2 > 100000 → Dark green (stops here for top earners)
2. Formula: =$C2 > 80000 → Light green
3. Formula: =$C2 < 65000 → Red (low earners)
4. Top/Bottom: Above Average → Yellow
Copying Conditional Formatting
- Format Painter: select formatted cell → Format Painter → paint to target range
- Paste Special → Formats only (
Ctrl+Alt+V→ Formats)
Clearing Rules
Home → Conditional Formatting → Clear Rules:
- Clear from Selected Cells
- Clear from Entire Sheet
Practical Examples
Example 1: Sales Dashboard Heatmap
Monthly revenue table: Rows = Products, Columns = Months
Select the entire data range (B2:M10):
Color Scale: 3-colour → Low (white) → Mid (yellow) → High (dark green)
→ Instantly see seasonal patterns and top products
Example 2: Project Status Tracker
Column A: Task name
Column B: Due Date
Column C: Status (Not Started / In Progress / Done)
Column D: Owner
Conditional formatting rules (applied to entire row A:D):
1. =$C2="Done" → grey fill + strikethrough font
2. =$B2 < TODAY() → red fill (overdue)
3. =AND($B2 >= TODAY(), $B2 <= TODAY()+3) → orange fill (due soon)
4. =$C2="In Progress" → yellow fill
Result: colour-coded task list that updates every day automatically
Example 3: Payroll Comparison
B = Last Year Salary, C = This Year Salary
D column (% Change) = (C2-B2)/B2
Conditional formatting on D:
=D2 > 0.10 → green (more than 10% raise)
=D2 < 0 → red (salary decreased)
Icon Set on D: 3 arrows — up > 10%, flat 0-10%, down < 0
Example 4: Duplicate Detection
Select the entire column A (Employee ID):
Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values
→ All repeated IDs highlighted in red
→ Click any highlighted cell to investigate
Performance Tips
- Conditional formatting with volatile functions (
TODAY(),NOW()) recalculates on every change — can slow large sheets - Limit formula-based rules to the actual data range, not entire columns (
A:A) - Avoid more than 3–4 rules on the same range — more rules = more recalculation
- For very large datasets, consider using a helper column with formulas, then format based on that column's values
Common Mistakes
1. Entire row not highlighted — forgot to anchor the column
Wrong: =B2="Finance" → each column evaluates its own value
Right: =$B2="Finance" → all columns in the row check column B
Key: Lock the column ($B) but leave the row relative (2)
2. Rule applied to the wrong range
Check the "Applies to" range in Manage Rules. A common mistake is selecting a single cell before creating the rule — the rule then only applies to that one cell.
3. Colour scale on a column that includes text or blanks
Text cells are excluded from the colour scale, which can shift the min/max inappropriately. Clean the column first.
4. Stop If True misused
If "Stop If True" is checked on a low-priority rule, higher values may not get higher-priority formatting applied.
Practice Exercises
- Apply a 3-colour scale (Red-Yellow-Green) to the salary column of your employee data.
- Use a formula-based rule to highlight the entire row of any employee whose rating is below 3.
- Create an icon set (traffic lights) on the rating column: green = 5, yellow = 3–4, red = 1–2.
- Add data bars to a revenue column. Customise the bar to be solid blue with a solid border.
- Build a project tracker with 4 rules: Done = grey, Overdue = red, Due in 3 days = orange, In Progress = yellow.
Summary
In this chapter you learned:
- Highlight Cell Rules: Greater Than, Less Than, Between, Contains, Duplicates, Unique Values
- Top/Bottom Rules: Top 10, Bottom 10%, Above Average, Below Average — all dynamic
- Data Bars: mini bar chart in each cell; customise min/max, colour, direction for negatives
- Colour Scales: 2-colour or 3-colour gradient; set min/midpoint/max by value, percent, or percentile
- Icon Sets: traffic lights, arrows, stars, symbols — set thresholds for each icon; show icon only option
- Formula-based: New Rule → Use a formula → TRUE applies format; use
=$B2="Finance"to highlight entire rows - Manage Rules: priority order matters — top rule wins; Stop If True halts lower rules
- Clear rules: Home → Conditional Formatting → Clear Rules → Selected Cells or Entire Sheet
- Lock column reference (
$B) but leave row relative for row-highlighting formulas - Avoid volatile functions and entire-column ranges for better performance on large datasets
Next up: Macros & VBA — record and write automation scripts to eliminate repetitive tasks.