How to use conditional formatting in Excel — apply rules, formulas, and troubleshoot

Quick answer: Use the Conditional Formatting menu on the Home tab to apply built-in rules (highlight cells, top/bottom, data bars, color scales, icon sets) or choose New Rule to enter a formula. Assign the rule to the correct range, set formatting, and manage rule order in Manage Rules to control priority.

What conditional formatting in Excel does and when to use it

Conditional formatting in Excel converts values and patterns into visual cues so you and others can scan a sheet and spot outliers, trends, or missing items. Use it when you want immediate visual signals for rules such as low inventory, overdue dates, top performers, or relative values within a column.

This article focuses on practical, step-by-step actions: applying built-in rules, writing formula-based rules, using color scales and icon sets, managing rule priority, and troubleshooting common mistakes.

Quick steps to apply a built-in conditional format

Select the cells you want to format.
On the Home tab, open Conditional Formatting and pick a rule type (Highlight Cell Rules, Top/Bottom Rules, Data Bars, Color Scales, Icon Sets).
Set the rule parameters (value, percent, formula where applicable) and choose formatting (fill color, font color, icon).
Confirm and, if necessary, return to Manage Rules to adjust the rule range or priority.

Built-in rules are the fastest way to highlight numbers or dates. For tailored logic you will use formula-based rules, described next.

Using formula-based rules for precise control

Formula-based rules let you express logic that built-in templates cannot, for example: highlight rows where sales are below a target or flag cells that depend on values in another column.

When to choose a formula-based rule

Comparisons across columns (for example, flag row when column B < column C).
Row-level formatting (color the entire row when a condition in one cell is met).
Complex conditions involving AND, OR, COUNTIF, or functions like WEEKDAY and TODAY.

Worked example: highlight sales below a target

Assume your sales values are in B2:B100 and the company target is in D1.
Select B2:B100 (or select the entire rows B2:E100 if you want to color the whole rows).
Open Home > Conditional Formatting > New Rule.
Choose 'Use a formula to determine which cells to format'.
Enter the formula: =B2<$D
. Note the mixed references: B2 is the first cell in the apply-to range and $D
is an absolute reference to the target.
Click Format, choose a fill color, and apply. Verify that the rule's Range in Manage Rules is correct.

This approach ensures Excel applies the formula to each row relative to the starting cell. For other formulas, keep the same principle: write the formula as if you were evaluating the top-left cell of the selected range.

Color scales, data bars, and icon sets — visual summaries

Color scales, data bars, and icon sets are best when you want to show relative magnitude or categories across a range.

When to use each

Color scales: good for heat-map view of values across a range; pick 2-color or 3-color schemes to show low-to-high gradients.
Data bars: show relative size directly inside the cell; useful for quick comparisons of numeric columns.
Icon sets: assign icons (arrows, flags, ticks) to ranges. Use for categorical thresholds like good/ok/bad.

Practical tip

When combining icon sets or color scales with other rules, expect overlap. Use Manage Rules to control which visual takes precedence and verify the formatting looks correct on sample rows before rolling out to a full report.

Rule Manager and priority — controlling overlapping rules

Open Home > Conditional Formatting > Manage Rules to see all applied rules for the active sheet or selection. The list shows the rule, the applied range, and the formatting.

How priority works

Excel evaluates conditional formatting rules in the order shown in Manage Rules.
If two rules apply to the same cells and specify different formats, the rule higher in the list typically determines the visible formatting.
To change which rule applies first, select a rule and use the Move Up / Move Down buttons.

Practical checks in Manage Rules

Confirm the 'Applies to' range — mis-specified ranges are the most common cause of rules not appearing.
Turn rules on and off temporarily to isolate which rule is producing the observed format.
When rules conflict, consider consolidating logic into a single formula-based rule to avoid priority confusion.

Troubleshooting common mistakes

Most issues stem from reference errors, range selection, or rule order. Here are frequent problems and fixes.

Wrong relative/absolute references - If your formula uses B2 but Excel applies it starting at B5, the formula will be evaluated incorrectly. Solution: write the formula relative to the first cell of the apply-to range and lock any references that must remain constant (use $).
Incorrect Apply To range - A rule that appears to do nothing often targets a different range. Open Manage Rules to check and correct it.
Formatting seems overridden - Check rule order in Manage Rules and temporarily disable other rules to identify conflicts.
Performance slowdown - Many complex conditional rules on large ranges can slow a workbook. Limit rules to needed ranges and use simpler formulas; see Excel performance tips for large spreadsheets for additional guidance.

Checklist before sharing a workbook

Verify all 'Applies to' ranges are precise and do not include entire columns unless necessary.
Confirm formula-based rules reference the correct "first cell" and use absolute references where appropriate.
Consolidate overlapping rules or reorder them in Manage Rules so the intended formatting is visible.
Remove unnecessary rules and keep an eye on file size and calculation time.
If you need to replicate formatting on another sheet, use the formatting copy tools; guidance at How to copy formatting between Excel sheets and workbooks.

When to use formulas versus built-in rules

Use built-in rules for standard tasks such as highlighting numbers above a threshold or top 10% values. Choose formula-based rules when logic depends on multiple cells, different sheets, or a custom combination of conditions. For help writing robust formulas, see Using Excel formulas: structure and best practices.

Closing: practical next steps

Start with one clear goal for conditional formatting — highlight underperforming items, spot missing dates, or visualize distribution — and implement the simplest rule that achieves it. Test on a small sample, verify ranges and references in Manage Rules, and keep formatting limited to the cells that need it. If performance becomes an issue, review workbook design and consult performance guidance to streamline rules and calculations.