How To Use Conditional Formatting In Excel (2026)

Conditional formatting changes how a cell looks when its value meets a rule you set.

It turns a plain grid of numbers into highlighted cells, colored scales and small icons that show patterns at a glance.

This guide follows Microsoft’s support pages for Excel on Windows and covers the quick presets, custom formula rules and how to clear them.

Quick answer: where conditional formatting lives

What you wantWhere to go
Highlight cells above, below or equal to a valueHome > Conditional Formatting > Highlight Cells Rules
Highlight the top or bottom items, or values above averageHome > Conditional Formatting > Top/Bottom Rules
Show bars, colors or icons inside cellsHome > Conditional Formatting > Data Bars, Color Scales or Icon Sets
Write your own rule with a formulaHome > Conditional Formatting > New Rule > Use a formula to determine which cells to format
See, reorder or delete rulesHome > Conditional Formatting > Manage Rules
Remove the formattingHome > Conditional Formatting > Clear Rules

Apply a quick preset rule

The presets cover most everyday needs and take only a few clicks.

  1. Select the cells you want to format.
  2. On the Home tab, select Conditional Formatting in the Styles group.
  3. Point to a category, such as Highlight Cells Rules.
  4. Choose a command, such as Greater Than, Between or Text that Contains.
  5. Enter the value and pick a format, then select OK.

You can also select your data and press Ctrl+Q to open Quick Analysis, then use the Formatting tab to preview options before you click one.

Highlight cells by number, text or date

Highlight Cells Rules compares each cell with a value, a piece of text or a date range.

Rule typeWhat it checksExample
Cell ValueA comparison operator plus a number, date or timeBetween two numbers
Specific TextContains, begins with or ends with, wildcards allowed, up to 255 charactersCells that contain a keyword
Dates OccurringA date range such as Yesterday or Next weekOverdue or upcoming dates
Blanks or No BlanksWhether a cell is empty (a cell holding only spaces counts as text)Missing entries
Errors or No ErrorsError values such as #VALUE!, #DIV/0! and #N/ABroken formulas

Select Format in the rule window to choose a number format, font, border or fill, and you can combine several of them.

Highlight top, bottom or above-average values

Top/Bottom Rules rank the selected cells instead of comparing them with a fixed number.

  • Choose Top 10 Items or Bottom 10% for a ranked group.
  • In a custom rule, a count can be any number from 1 to 1000.
  • For a percentage, enter a number from 1 to 100 and tick the option for a percent of the selected range.
  • Choose Above Average or Below Average to compare each cell with the average of the range.

In the advanced version of this rule you can also use one, two or three standard deviations instead of the plain average.

Use data bars, color scales and icon sets

These three presets draw the comparison inside the cell instead of only coloring it.

PresetWhat it showsWhere to find it
Data BarsA bar whose length matches the value, so longer means higherConditional Formatting > Data Bars
Color ScalesTwo or three colors that shade cells from low to highConditional Formatting > Color Scales
Icon SetsThree to five icons that sort values into categories by thresholdConditional Formatting > Icon Sets

Microsoft notes that widening the column makes the difference between data bars easier to see.

Create a custom rule with a formula

A formula rule lets you format a cell based on another cell, or on a test that no preset covers.

  1. Select the cells to format.
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose Use a formula to determine which cells to format.
  4. Type a formula that starts with = and returns TRUE or FALSE.
  5. Select Format, choose the look you want, then select OK twice.

Clicking a cell while you type inserts an absolute reference, so use a relative reference if the rule should adjust for each cell.

If the formula returns an error, Excel skips the formatting for that cell, so wrap it in IFERROR or an IS function when errors are possible.

Microsoft’s own example shades alternate rows with =MOD(ROW(),2)=1, which colors the odd-numbered rows.

Flag expired or upcoming dates

Microsoft’s badge example uses two rules on a column of expiry dates.

RuleFormulaFill
First rule: already expired=B2<TODAY()Red
Second rule: expires within 60 days=B2<TODAY()+60Yellow

Cells that are already red stay red because the first rule has higher precedence than the second.

Manage rules and their order

Open Home > Conditional Formatting > Manage Rules to create, edit, delete and review every rule.

SituationWhat Excel does
Two rules that do not conflict, such as bold and a red fontBoth apply
Two rules that conflict, such as two font colorsThe rule higher in the list wins
A rule and manual formatting on the same cellThe rule overrides the manual formatting
You delete the ruleThe manual formatting remains
New rule just addedIt goes to the top of the list by default

Use Move Up and Move Down to change the order, and select one rule at a time when you do.

The Stop If True box ends the check once a rule is true, and it is not available for data bars, color scales or icon sets.

Use the Show formatting rules for list to filter the list by worksheet or table.

Copy conditional formatting to other cells

  1. Click a cell that has the formatting you want.
  2. Select Format Painter on the Home tab, or double-click it to keep it active.
  3. Drag across the cells to format.
  4. Press Esc to stop.

Copying, pasting or filling cells that have conditional formats creates a new rule for the destination cells.

Clear conditional formatting

  1. To clear the whole sheet, go to Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.
  2. To clear only some cells, select them and choose Clear Rules from Selected Cells.

You can also select cells, click the Quick Analysis button at the lower-right corner of the selection and choose Clear Format.

The Quick Analysis button does not appear if the selection is empty or only its upper-left cell has content.

Limits and things that trip people up

LimitDetail
Another workbookConditional formatting cannot use references to a different workbook
PivotTable valuesText and date rules are not allowed on Values-area fields, only number rules, and unique or duplicate rules are not available there
WildcardsAn asterisk and a question mark act as wildcards in the duplicate and text rules, even inside a formula
Pasting between Excel windowsPasting into a separate Excel instance does not create the rule or copy the format

If your rule does nothing, check the Applies to range in Manage Rules and the order of the rules first.

If cells show text instead of numbers, rules that compare numbers will not match, so convert numbers stored as text first.

Official sources

Leave a Reply

Your email address will not be published. Required fields are marked *