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 want | Where to go |
|---|---|
| Highlight cells above, below or equal to a value | Home > Conditional Formatting > Highlight Cells Rules |
| Highlight the top or bottom items, or values above average | Home > Conditional Formatting > Top/Bottom Rules |
| Show bars, colors or icons inside cells | Home > Conditional Formatting > Data Bars, Color Scales or Icon Sets |
| Write your own rule with a formula | Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format |
| See, reorder or delete rules | Home > Conditional Formatting > Manage Rules |
| Remove the formatting | Home > Conditional Formatting > Clear Rules |
Apply a quick preset rule
The presets cover most everyday needs and take only a few clicks.
- Select the cells you want to format.
- On the Home tab, select Conditional Formatting in the Styles group.
- Point to a category, such as Highlight Cells Rules.
- Choose a command, such as Greater Than, Between or Text that Contains.
- 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 type | What it checks | Example |
|---|---|---|
| Cell Value | A comparison operator plus a number, date or time | Between two numbers |
| Specific Text | Contains, begins with or ends with, wildcards allowed, up to 255 characters | Cells that contain a keyword |
| Dates Occurring | A date range such as Yesterday or Next week | Overdue or upcoming dates |
| Blanks or No Blanks | Whether a cell is empty (a cell holding only spaces counts as text) | Missing entries |
| Errors or No Errors | Error values such as #VALUE!, #DIV/0! and #N/A | Broken 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.
| Preset | What it shows | Where to find it |
|---|---|---|
| Data Bars | A bar whose length matches the value, so longer means higher | Conditional Formatting > Data Bars |
| Color Scales | Two or three colors that shade cells from low to high | Conditional Formatting > Color Scales |
| Icon Sets | Three to five icons that sort values into categories by threshold | Conditional 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.
- Select the cells to format.
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Type a formula that starts with = and returns TRUE or FALSE.
- 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.
| Rule | Formula | Fill |
|---|---|---|
| First rule: already expired | =B2<TODAY() | Red |
| Second rule: expires within 60 days | =B2<TODAY()+60 | Yellow |
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.
| Situation | What Excel does |
|---|---|
| Two rules that do not conflict, such as bold and a red font | Both apply |
| Two rules that conflict, such as two font colors | The rule higher in the list wins |
| A rule and manual formatting on the same cell | The rule overrides the manual formatting |
| You delete the rule | The manual formatting remains |
| New rule just added | It 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
- Click a cell that has the formatting you want.
- Select Format Painter on the Home tab, or double-click it to keep it active.
- Drag across the cells to format.
- Press Esc to stop.
Copying, pasting or filling cells that have conditional formats creates a new rule for the destination cells.
Clear conditional formatting
- To clear the whole sheet, go to Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.
- 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
| Limit | Detail |
|---|---|
| Another workbook | Conditional formatting cannot use references to a different workbook |
| PivotTable values | Text and date rules are not allowed on Values-area fields, only number rules, and unique or duplicate rules are not available there |
| Wildcards | An asterisk and a question mark act as wildcards in the duplicate and text rules, even inside a formula |
| Pasting between Excel windows | Pasting 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.
Related Excel guides
- Remove duplicates in Excel uses the Duplicate Values highlight as a preview step.
- Fix #N/A, #VALUE! and #REF! errors explains the errors that the Errors rule can flag.
- Create a pivot table in Excel covers the PivotTable data that these rules can format.
Official sources
- Use conditional formatting to highlight information in Excel (Microsoft Support)
- Use data bars, color scales and icon sets to highlight data (Microsoft Support)