Excel Conditional Formatting: The Complete Guide
Conditional formatting is Excel's most underused feature. Applied well, it turns a wall of numbers into an at-a-glance story — outliers pop, trends emerge, exceptions demand attention. This guide covers every built-in rule type, formula-based rules, managing multiple rules, and the patterns that actually earn their place in a dashboard.
What's in this guide
What conditional formatting is
Conditional formatting (CF) applies a visual style — background color, font color, borders, icons, or bars — to cells that meet a rule you define. The formatting is dynamic: when the underlying value changes, the format updates. It doesn't touch the data itself.
Three things make CF powerful:
- Reactive. Change a number, the formatting updates. No refresh needed.
- Layered. Multiple rules can apply to the same cells with clear precedence.
- Formula-driven. Beyond built-in rules, you can drive CF with any Excel formula that returns TRUE or FALSE — which is nearly unlimited.
Conditional formatting is style-as-a-function-of-value. You're not painting cells; you're writing a rule that Excel paints for you. When you copy cells, the rule copies too. When you change values, the paint updates.
Where to find it
The dropdown reveals six main categories:
- Highlight Cells Rules — greater than, less than, between, equal to, text contains, dates occurring, duplicate values
- Top/Bottom Rules — top 10, bottom 10, above average, below average (and variants)
- Data Bars — in-cell bars that scale with value
- Color Scales — gradient fills based on rank in the range
- Icon Sets — arrows, traffic lights, ratings, symbols
- New Rule / Clear Rules / Manage Rules — the advanced controls
Highlight Cells Rules
The bread and butter. Rules that highlight cells matching simple comparisons.
Greater Than
Highlight cells above a threshold. Great for flagging overages, top performers, or big numbers.
Less Than
Same principle, opposite direction. Common uses: negative balances, low stock, missed targets.
Between
Highlight a mid-range. Useful for "acceptable" zones between soft and hard limits.
Equal To
Flag specific values. Case-insensitive for text. Handy for exact-match business rules ("status = Pending").
Text that Contains
Substring match for text cells. "Contains 'urgent'" or "Contains customer code prefix".
A Date Occurring
Options: Yesterday, Today, Tomorrow, In the last 7 days, Last week, This week, Next week, Last month, This month, Next month. Perfect for aging reports and to-do lists.
Duplicate Values
Highlights any value appearing more than once. Also has a "Unique" variant that flags only values appearing once.
Instead of typing "1000", click the cell reference box and then click a cell that holds the threshold (say, D1). Now changing D1 updates the formatting live. Perfect for adjustable dashboards.
Top/Bottom Rules
Rank-based highlighting. Excel figures out what "top" or "bottom" means for your selection.
- Top 10 Items — the highest N values (adjustable, doesn't have to be 10)
- Top 10 % — the highest percentile
- Bottom 10 Items — the lowest N
- Bottom 10 % — the lowest percentile
- Above Average — cells greater than the arithmetic mean of the range
- Below Average — cells less than the mean
All these rules recalculate as data changes. Add a new "top" value, and previous top rows may lose their highlight while the new one gains it.
Data Bars
In-cell bars that scale with the value. Left-aligned, they act as tiny embedded bar charts — you get magnitude at a glance without leaving the cell.
Solid vs. Gradient fill
Solid bars are cleaner for large ranges. Gradient bars fade out toward the right — sometimes prettier but harder to read at high densities.
Customizing data bars
Home → Conditional Formatting → Manage Rules → select the rule → Edit Rule. You can:
- Change color
- Show bar only (hide the number)
- Set min/max as automatic, lowest/highest value, number, percentile, or formula
- Choose bar direction (left-to-right or right-to-left)
- Change axis behavior (for ranges that include negatives)
Hiding the number leaves just the bars — a compact way to visualize a column of data without the numeric clutter. Add the raw number in an adjacent column if you need both.
Color Scales
Fills each cell with a gradient color based on rank within the range. Two-color and three-color scales are built in; custom colors are available under Manage Rules.
Classic red-yellow-green (heatmap)
Low values = red, middle = yellow, high = green. The universal "hot to cold" pattern.
Reverse for financial data
For metrics where lower is better (errors, response time, defects), reverse the scale: red at the top, green at the bottom. Manage Rules → Edit Rule → swap the color assignments.
Setting custom endpoints
By default, Excel uses the min and max of the range as the endpoints. This means an outlier can compress the rest of the scale. To fix:
- Manage Rules → Edit Rule
- Change Minimum/Maximum Type from "Lowest Value / Highest Value" to "Number" or "Percentile"
- Enter your desired endpoints
Set min to the 5th percentile and max to the 95th. Extreme outliers get pinned to the endpoint colors, but the middle range spreads cleanly across the gradient.
Icon Sets
Attach icons (arrows, traffic lights, checkmarks, stars, flags) to cells based on value ranges.
Traffic light (3 icons)
Green for high, yellow for middle, red for low. Great for KPI dashboards.
Directional arrows (3 or 5 icons)
Up, sideways, down — for trend indicators. 5-icon variants add "trending up" and "trending down" between the extremes.
Ratings (4 or 5 icons)
Bar-filled indicators or stars. Good for quality scores and survey results.
Configuring thresholds
Manage Rules → Edit Rule → set the numeric or percentile cutoffs. For example, "green if value ≥ 80, yellow if 50-79, red if <50".
Uncheck "Show Icon Only" to display the value alongside; check it for clean icon-only cells.
Set thresholds as "Number" and reference cells that hold your targets. Now the icons adjust when targets change — perfect for tuning without opening the rule editor.
Formula-based rules
The most powerful CF option. Instead of picking from Excel's built-ins, write any formula that returns TRUE or FALSE. Excel applies the format wherever the formula is TRUE.
Setting up a formula rule
- Select the range you want to format
- Home → Conditional Formatting → New Rule
- Choose Use a formula to determine which cells to format
- Enter the formula — think of it as the formula for the top-left cell of your selection
- Click Format → pick fill, font, border
- OK
Understanding $ signs (the critical part)
Excel evaluates your formula relative to each cell in the selection. To make it evaluate correctly, use $ signs strategically.
| Formula shape | Behavior |
|---|---|
=A1>100 | Highlights individual cells where the value is over 100 |
=$A1>100 | Highlights the entire row when column A of that row is over 100 |
=A$1>100 | Highlights the entire column when row 1 of that column is over 100 |
=$A$1>100 | Highlights everything (or nothing) based on a single cell |
The "highlight the row" pattern
The most common formula-based use case. Highlight the whole row when column A meets a condition.
- Select A2:E100 (or wherever your data lives)
- New Rule → formula:
=$A2="Overdue" - Format → red fill
The $ locks column A but not row 2. As Excel evaluates row by row, it checks A2, A3, A4, etc. When any is "Overdue", the entire row (A through E) gets colored.
Common formula patterns
Highlight overdue tasks
=$B2 < TODAY()Where B holds the due date. Highlights past-due rows.
Highlight weekends in a date column
=WEEKDAY($A2, 2) > 5WEEKDAY with return_type=2 returns 6 for Saturday and 7 for Sunday.
Highlight duplicates across columns
=COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2) > 1Duplicate combinations across two columns. Built-in "Duplicate Values" only checks one column at a time.
Highlight the row of the maximum value
=$C2 = MAX($C$2:$C$100)Whichever row has the biggest number in column C gets highlighted.
Highlight cells matching a search box
=SEARCH($F$1, A2) > 0Where F1 holds the search term. Cells containing that text (case-insensitive) get formatted. Wrap in IFERROR to suppress errors for non-matches.
Highlight every other row (zebra striping)
=MOD(ROW(), 2) = 0Even rows get formatted. Change to =MOD(ROW(), 2) = 1 for odd rows.
Highlight rows where two cells don't match
=$A2 <> $B2Great for reconciliation — flag rows where a computed value doesn't equal an expected value.
Managing rules
The Rule Manager is the control center for every CF rule in the workbook.
The scope dropdown
At the top: "Show formatting rules for: [scope]". Options:
- Current Selection — rules touching the selected cells
- This Worksheet — every rule on the current sheet
- [Sheet Name] — rules on a specific sheet
Use "This Worksheet" to audit — you'll often find forgotten rules that no longer apply or duplicate rules created by accident.
Rule precedence
Rules run top to bottom. The first matching rule wins for each cell. Reorder with the up/down arrows.
Stop If True
Check this box to prevent lower-priority rules from applying to cells where this rule matches. Useful for exception rules that should override everything below.
Applies to
Every rule has a range it applies to. Edit this to expand or contract coverage without recreating the rule. Common pattern: copy data to new rows and update the "Applies to" range to include them.
Common patterns
1. Traffic-light KPI dashboard
- Data column with values
- Icon set (traffic light 3 icons)
- Configure thresholds against target cells: green ≥ target, yellow within 10%, red below
- Show Icon Only for a compact look
2. Overdue task highlighting
- Select the whole task table
- Formula rule:
=AND($D2 < TODAY(), $E2 <> "Done") - Red fill
Rows where Due Date is past AND Status isn't Done get highlighted.
3. Zebra stripes with search highlight
Two rules, both applied to the same range:
- Formula:
=MOD(ROW(), 2) = 0— light gray fill (zebra stripes) - Formula:
=SEARCH($F$1, $A2) > 0— yellow fill (search highlight)
Put search highlight above zebra in Rule Manager so it wins when both match.
4. Heatmap for weekly performance
- Grid of weeks (columns) × people (rows)
- Color scale (3-color) applied to the data range
- Set min/max as percentile 5 and 95 to resist outliers
5. Gantt-style visualization with data bars
Given start and end dates in columns A and B, and a numeric "days elapsed" column C:
- Data bars on column C
- Show Bar Only
- Set min = 0, max = project total duration
Bars visually show progress. Combined with color coding by status, a working Gantt.
6. Approaching deadlines
Three rules on a due-date column, ordered from most urgent:
- Formula:
=$B2 < TODAY()→ red fill (overdue) - Formula:
=$B2 <= TODAY() + 3→ orange fill (soon) - Formula:
=$B2 <= TODAY() + 7→ yellow fill (this week)
Order matters: overdue must be on top or the "soon" rule wins for past-due dates.
7. Highlight the max in each row
To find the top value across a grid, one rule per row-target column:
=B2 = MAX($B2:$E2)The $ signs lock the columns but not the row. Each row's max gets highlighted independently.
Copying and clearing formatting
Copy formats with Paste Special
Copy a cell that has CF → Paste Special → Formats. The rules extend to the pasted range.
Extend to new rows
If your CF applies to A2:E100 and you add data down to A200, the rule doesn't automatically extend. Two fixes:
- Manage Rules → edit "Applies to" from A2:E100 to A2:E200
- Or: convert the range to an Excel Table (Ctrl+T). Tables auto-extend CF as rows are added.
Clearing rules
For a specific rule: Manage Rules → select the rule → Delete Rule.
Pitfalls to avoid
By far the most common issue. If your rule seems to highlight nothing, or highlights the wrong cells, check the $ signs. Rule of thumb: for "highlight the whole row based on column X", the formula should be =$X2 = condition — $ before the column letter, not the row number.
Large numbers of formula-based rules on wide ranges recalculate constantly. Symptoms: slow scrolling, slow response after edits. Fix: consolidate rules (one rule covering a range beats N rules covering rows individually), or convert dynamic ranges to static and refresh manually.
Conditional formatting on merged cells behaves unpredictably. If you must use CF with merged cells, format the top-left cell only and expect the visible behavior to be inconsistent. Better: avoid merged cells in data areas.
A rule that references another sheet ("=Sheet2!$A$1<100") works but breaks if Sheet2 is deleted or renamed. Prefer references on the same sheet, or use named ranges that update if references change.
Copying formatted cells and pasting creates duplicate CF rules with narrower ranges. Over time, a workbook accumulates hundreds of near-duplicate rules. Open Manage Rules occasionally and consolidate.
Before deploying a formula rule, put clearly matching and non-matching values in the first few cells. Verify the format appears where expected and only there. Adjust $ signs until behavior matches intent.
Conditional formatting in Google Sheets
Google Sheets has conditional formatting under Format → Conditional Formatting. The core is similar — highlight, top/bottom, color scales, and custom formulas — but with differences:
- No icon sets (workaround: use custom formulas with emoji or images)
- No data bars natively (use SPARKLINE or REPT-based bars)
- Single-condition or gradient are the two main modes
- Custom formulas work the same way as Excel — same $ sign logic
Formatting that writes itself
Conditional formatting rules are powerful but the $ signs, ranges, and rule ordering trip up almost everyone. Excel Wizard takes intent ("highlight overdue tasks in red, upcoming in yellow") and generates the rules with correct anchoring, correct precedence, and correct applies-to ranges.
Install Excel Wizard →Frequently asked questions
What is conditional formatting in Excel?
Conditional formatting applies visual styles — colors, fills, icons, data bars — to cells based on rules you define. When values change, the formatting updates in real time. The data itself is never modified.
How do I highlight cells greater than a value?
Select the range → Home → Conditional Formatting → Highlight Cells Rules → Greater Than. Enter the threshold and pick a style. Reference a cell for the threshold to make it adjustable.
How do I use a formula in conditional formatting?
Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format". Enter a formula that returns TRUE/FALSE for the top-left cell of your selection. Use $ signs carefully: $A1 locks column A, A$1 locks row 1.
How do I remove conditional formatting?
Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells (or Entire Sheet). For specific rules, use Manage Rules → Delete Rule.
Why isn't my conditional formatting working?
Usually: wrong $ signs in the formula, wrong Applies-to range, or another rule overriding it. Open Manage Rules → Show rules for This Worksheet to audit everything active.