Excel Conditional Formatting: The Complete Guide (2026)
Home›Excel›Conditional Formatting
ExcelPillar Guide⏱ 24 min read

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 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:

  1. Reactive. Change a number, the formatting updates. No refresh needed.
  2. Layered. Multiple rules can apply to the same cells with clear precedence.
  3. Formula-driven. Beyond built-in rules, you can drive CF with any Excel formula that returns TRUE or FALSE — which is nearly unlimited.
The mental model

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

Ribbon: Home → Conditional Formatting

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.

Home → Conditional Formatting → Highlight Cells Rules → Greater Than → 1000 → Green Fill with Dark Green Text

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.

Between 80 and 100 → Yellow fill

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.

Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values → Light Red Fill
Reference a cell for the threshold

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.

350 700 1000

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)
"Show Bar Only" for clean displays

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.

Low Mid High

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:

  1. Manage Rules → Edit Rule
  2. Change Minimum/Maximum Type from "Lowest Value / Highest Value" to "Number" or "Percentile"
  3. Enter your desired endpoints
Percentile endpoints for outlier resistance

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.

● Below target    ● At target    ● Above target

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.

Icons work with formulas too

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

  1. Select the range you want to format
  2. Home → Conditional Formatting → New Rule
  3. Choose Use a formula to determine which cells to format
  4. Enter the formula — think of it as the formula for the top-left cell of your selection
  5. Click Format → pick fill, font, border
  6. 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 shapeBehavior
=A1>100Highlights individual cells where the value is over 100
=$A1>100Highlights the entire row when column A of that row is over 100
=A$1>100Highlights the entire column when row 1 of that column is over 100
=$A$1>100Highlights 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.

  1. Select A2:E100 (or wherever your data lives)
  2. New Rule → formula: =$A2="Overdue"
  3. 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) > 5

WEEKDAY 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) > 1

Duplicate 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) > 0

Where 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) = 0

Even rows get formatted. Change to =MOD(ROW(), 2) = 1 for odd rows.

Highlight rows where two cells don't match

=$A2 <> $B2

Great for reconciliation — flag rows where a computed value doesn't equal an expected value.

Managing rules

Ribbon: Home → Conditional Formatting → Manage 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

  1. Data column with values
  2. Icon set (traffic light 3 icons)
  3. Configure thresholds against target cells: green ≥ target, yellow within 10%, red below
  4. Show Icon Only for a compact look

2. Overdue task highlighting

  1. Select the whole task table
  2. Formula rule: =AND($D2 < TODAY(), $E2 <> "Done")
  3. 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:

  1. Formula: =MOD(ROW(), 2) = 0 — light gray fill (zebra stripes)
  2. 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

  1. Grid of weeks (columns) × people (rows)
  2. Color scale (3-color) applied to the data range
  3. 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:

  1. Data bars on column C
  2. Show Bar Only
  3. 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:

  1. Formula: =$B2 < TODAY() → red fill (overdue)
  2. Formula: =$B2 <= TODAY() + 3 → orange fill (soon)
  3. 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

Ribbon: Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells / Entire Sheet

For a specific rule: Manage Rules → select the rule → Delete Rule.

Pitfalls to avoid

Wrong $ signs in formula rules

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.

Slow workbooks from too many CF rules

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.

Rules on merged cells

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.

Rules that reference cells outside the sheet

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.

Copy-paste multiplies rules

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.

Test rules with obvious data

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
Excel Wizard

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.