Conditional formatting makes your data visual without building charts. It applies colors, icons, and data bars automatically based on cell values — so patterns, outliers, and problems jump out immediately.
What Is Conditional Formatting?
Conditional formatting is a rule you attach to a cell range. When a cell meets the rule's condition, Excel applies the specified format (fill color, font color, border, icon, data bar). When the condition is no longer met, the format disappears automatically.
Everything lives under Home tab → Conditional Formatting.
Applying Basic Rules: Highlight Cell Rules
The fastest way to get started. These rules apply to individual cells and compare each value to a threshold.
- Select your range (e.g., D2:D100 — a column of sales figures)
- Home → Conditional Formatting → Highlight Cells Rules
- Choose a rule type:
| Rule | Applies format when... |
| Greater Than | Cell value > your number |
| Less Than | Cell value < your number |
| Between | Cell value is within a range |
| Equal To | Cell value exactly matches |
| Text That Contains | Cell text includes a substring |
| A Date Occurring | Date matches "last week", "next month", etc. |
| Duplicate Values | Cell appears more than once in the range |
- Enter the value and pick a color from the preset dropdown (or choose Custom Format for full control)
- Click OK
- Range: D2:D100
- Rule: Less Than → 1000 → Light Red Fill with Dark Red Text
Top/Bottom Rules
These rules work on relative ranking within the selected range, not fixed thresholds.
Home → Conditional Formatting → Top/Bottom Rules:- Top 10 Items: Highlight the N highest values (change N to any number)
- Top 10%: Highlight the top N% of values
- Bottom 10 Items / Bottom 10%: Same for lowest values
- Above Average / Below Average: Compares each cell to the range mean
- Select the scores range
- Top/Bottom Rules → Top 10 Items → change 10 to 5 → choose Green Fill
Data Bars
Data bars add a colored bar inside each cell, proportional to the cell's value. The cell with the highest value gets a full bar; others get proportional bars.
Home → Conditional Formatting → Data Bars → choose Gradient Fill or Solid Fill color. When to use: Quick visual comparison of magnitudes across rows, like a mini in-cell bar chart. Works well in dashboards where space is tight. Customize data bars: Click More Rules at the bottom of the Data Bars submenu:- Set minimum/maximum value manually (otherwise the range auto-scales)
- Choose bar direction (left-to-right or right-to-left)
- Show bar only (hide the number) — useful when bar length is the point
Color Scales
Color scales apply a gradient across a range of values. The most common is the 3-color scale: red for low, yellow for midpoint, green for high (or reverse for metrics where lower is better).
Home → Conditional Formatting → Color Scales → pick a preset or click More Rules to customize. Custom color scale options:- Set the Minimum, Midpoint, and Maximum as: Lowest/Highest value, specific number, percentile, or formula
- Assign any color to each anchor point
Highlighting Duplicates and Unique Values
Home → Conditional Formatting → Highlight Cells Rules → Duplicate ValuesDialog options:
- Duplicate: Highlights every cell that appears more than once in the range
- Unique: Highlights cells that appear exactly once
Use this to spot data entry errors in ID columns, or to find which products appear in one list but not another. Limitation: "Duplicate" here means the value exists more than once — it highlights ALL occurrences including the first one, not just the extras. If you only want to flag the second and subsequent occurrences, use a custom formula rule instead (see below).
Custom Formula Rules
Custom formula rules unlock conditional formatting's full power. You write a formula that evaluates to TRUE or FALSE for each cell — when TRUE, the format applies.
How to create a custom rule:- Select your range (e.g., A2:E100)
- Home → Conditional Formatting → New Rule
- Choose "Use a formula to determine which cells to format"
- Enter your formula
- Click Format to set fill/font/border
- Click OK
Example Formulas
Highlight entire row if Status = "Overdue" (range: A2:E100, status in column D): =$D2="Overdue"The $D fixes the column to D, while 2 is relative — so each row checks its own D cell. The whole row gets formatted because the formula is anchored to column D.
Highlight cells greater than average: =A2>AVERAGE($A$2:$A$100) Flag the second+ occurrence of a duplicate: =COUNTIF($A$2:A2,A2)>1This counts how many times the current value has appeared from A2 up to the current row. On first occurrence, count = 1 (not highlighted). On second occurrence, count = 2 (highlighted).
Highlight weekends in a date column: =WEEKDAY(A2,2)>5 Alternate row shading (no Table required): =MOD(ROW(),2)=0Managing Rules
When multiple rules apply to the same cells, order matters.
Home → Conditional Formatting → Manage RulesThe Manage Rules dialog shows all rules for the current selection or the whole sheet. From here you can:
- Reorder rules: Use the up/down arrows. Rules higher in the list take priority.
- Stop If True: Check this box on a rule to prevent lower-priority rules from applying if this rule matches. Useful for "if red, don't also apply yellow" logic.
- Edit Rule: Double-click any rule to modify it.
- Delete Rule: Select and click Delete Rule.
- Apply to different range: Edit the "Applies To" field directly.
Change the scope dropdown at the top to see rules for the current selection, current sheet, or specific sheets.
Performance Considerations
Conditional formatting with volatile functions (NOW(), TODAY(), RAND(), OFFSET(), INDIRECT()) or rules applied to entire columns can slow down large workbooks significantly. Best practices:
- Apply rules to finite ranges (A2:A10000) rather than entire columns (A:A)
- Avoid INDIRECT inside conditional formatting formulas
- Keep the total number of distinct rules under 50 per sheet where possible
- Delete unused or duplicate rules regularly via Manage Rules
Frequently Asked Questions
Why does my custom formula rule apply to the wrong cells?
Almost always a reference anchoring mistake. Check whether your formula uses $ correctly. For row-by-row rules (e.g., format a row based on one column), lock the column with $ but leave the row number relative: =$D2="Yes". For cell-specific comparisons, lock both: =$D$2="Yes" (same cell checked for all).
Can conditional formatting reference another sheet?
Not directly in the formula. Excel does not allow cross-sheet references inside conditional formatting formulas. Workaround: create a helper column on the current sheet that pulls the value from the other sheet with a normal formula (e.g., =Sheet2!A1), then reference that helper column in the conditional formatting rule.
Does conditional formatting slow down Excel?
Yes, if overused. Rules applied to whole columns (1M+ cells) or rules using volatile/lookup functions are the most common culprits. Use Manage Rules to audit and trim unnecessary rules. Converting source data to Excel Tables can sometimes help by scoping rules more tightly.
How do I copy conditional formatting to another range?
Select a cell with the formatting → Format Painter (Home tab, paintbrush icon) → paint over the target range. Alternatively, copy the cell (Ctrl+C), select the target, Paste Special (Ctrl+Alt+V) → Formats. The rule's "Applies To" range will update automatically.
Can I use AND/OR logic in custom formula rules?
Yes. Wrap your conditions:
- Both conditions true: =AND($D2="Overdue",$E2>1000)
- Either condition true: =OR($D2="Overdue",$D2="At Risk")