Excel Conditional Formatting: Complete Guide
Conditional formatting highlights patterns, flags problems, and builds heat maps — automatically, without charts or manual work. Here is every technique from quick highlights to formula-driven rules.
Quick Start
Select your cells → Home → Conditional Formatting → pick a rule type. For the most powerful option, choose New Rule → Use a formula to determine which cells to format — any formula that returns TRUE/FALSE works as a rule.
The 6 Types of Conditional Formatting Rules
Highlight Cell Rules
Home → Conditional Formatting → Highlight Cells RulesGreater than, less than, equal to, between, text containing, dates, duplicates. The quickest way to flag outliers and specific values.
Top/Bottom Rules
Home → Conditional Formatting → Top/Bottom RulesTop 10 items, bottom 10%, above/below average. Useful for ranking and identifying performance extremes.
Data Bars
Home → Conditional Formatting → Data BarsHorizontal bars inside cells proportional to the value. Creates a mini bar chart directly in the cells — no separate chart needed.
Colour Scales
Home → Conditional Formatting → Colour ScalesGradient from one colour to another across a range — a heat map effect. Useful for spotting high/low patterns across a table.
Icon Sets
Home → Conditional Formatting → Icon SetsArrows, traffic lights, stars, flags inside cells based on value thresholds. Good for status indicators in dashboards.
Formula-based rules
Home → Conditional Formatting → New Rule → Use a formulaThe most powerful option — any logical formula that returns TRUE/FALSE. Enables highlighting entire rows, cross-column conditions, and patterns no built-in rule can express.
Formula-Based Rules: 6 Practical Examples
Formula rules are the most powerful conditional formatting option. The formula must return TRUE or FALSE — if TRUE, the format applies. The formula is evaluated relative to the top-left cell of the selected range.
Highlight entire row if status = "Overdue"
Apply to range
A2:F100Formula
=$D2="Overdue"Key: $D locks the column to D; 2 is relative so each row checks its own D cell.
Highlight row if date is in the past
Apply to range
A2:F100Formula
=$C2<TODAY()Key: TODAY() recalculates daily — the highlight updates automatically.
Highlight duplicates in a column
Apply to range
A2:A500Formula
=COUNTIF($A$2:$A$500,A2)>1Key: The COUNTIF range is absolute; the lookup value A2 is relative.
Highlight every other row (zebra striping)
Apply to range
A2:F100Formula
=MOD(ROW(),2)=0Key: Use =MOD(ROW(),2)=1 to shade odd rows instead.
Highlight cells where the value is above the column average
Apply to range
B2:B100Formula
=B2>AVERAGE($B$2:$B$100)Key: The AVERAGE range is absolute; B2 is relative to compare each cell.
Highlight a row if ANY cell in it is blank
Apply to range
A2:F100Formula
=COUNTBLANK($A2:$F2)>0Key: Column references are locked ($A, $F); row is relative.
The Most Important Rule: Absolute vs Relative References
The single biggest source of conditional formatting errors is wrong reference locking. The formula is written for the top-left cell of your selected range — Excel then shifts it for every other cell.
- $A$1 — fully locked. Same cell for every row and column. Use for a single fixed threshold.
- $A1 — column locked, row relative. The column stays on A; the row shifts per row. Use for row-based rules that check one column across multiple rows.
- A$1 — row locked, column relative. Rare — use for column-based rules checking one row.
- A1 — fully relative. Both shift. Use inside COUNTIF/AVERAGE ranges that should stay fixed — wrap those separately with $ locks.
Test before applying: Write your formula in a regular empty cell first. If it returns TRUE or FALSE correctly, paste it into the conditional formatting rule.
Managing Multiple Rules: Priority and Conflicts
Open Home → Conditional Formatting → Manage Rules to see all rules and their priority order. Rules are evaluated top to bottom — the first rule that matches wins (by default). Drag rules up or down to change priority. Check "Stop If True" on a rule to prevent lower-priority rules from applying even if they also match.
Common Problems and Fixes
Rule applies to wrong cells
Home → Conditional Formatting → Manage Rules → check the "Applies to" range. Edit it to cover the correct range.
Rule is being overridden
Rules are evaluated top to bottom and the first match wins (unless "Stop If True" is unchecked). Open Manage Rules and drag your rule above the conflicting one.
Formula rule highlights everything or nothing
Check absolute vs relative references. For row-by-row rules: lock the column ($A) but keep the row relative (1). Test your formula in a regular cell first — it must return TRUE or FALSE.
Colour scale ignoring some cells
Cells containing text or errors are excluded from colour scale calculations. Clean the data so all cells are numeric.
Formatting applies but shows nothing in print
Page Layout → check "Print" under Sheet Options. Some conditional formats also need "Print object" enabled on the format dialog.
Frequently Asked Questions
Why is my conditional formatting not working?
Most common causes: wrong range in the rule, a higher-priority rule overriding yours, incorrect absolute/relative references in the formula, or cells formatted as Text when the rule expects numbers. Open Manage Rules and check each.
How do I highlight an entire row based on a cell value?
Select the full row range (e.g. A2:F100), create a formula rule, and reference the decision column with a locked column: =$C2="Complete". The $ locks column C; the row stays relative so each row checks its own C cell.
How do I remove conditional formatting without deleting data?
Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells. Data, values, and manual formatting are unaffected.
Related Guides
IF Statement Examples
Formula logic that complements conditional formatting — different output, same logical approach.
Read →
Pivot Table Guide
Combine conditional formatting with pivot tables for dashboard-quality reports.
Read →
Fix Numbers Stored as Text
Text-formatted numbers are invisible to colour scales and numeric rules — fix them first.
Read →
Data Cleaning Hub
Clean data before formatting — dirty data produces misleading highlights.
Read →