Conditional formatting changes how a cell looks based on its value, so problems and patterns stand out without manual shading. This guide follows Microsoft’s Excel instructions.
Quick formatting from the Home tab
- Select the cells, table or PivotTable range.
- Go to Home > Styles > Conditional Formatting.
- Pick one of the options below.
| Option | What it does |
|---|---|
| Highlight Cells Rules | Between, Equal To, Text that Contains, A Date Occurring, Duplicate Values. Enter values and choose a format. |
| Top/Bottom Rules | Top 10 Items, Bottom 10%, Above Average, Below Average. |
| Data Bars | In-cell bars sized by value. Choose a bar style. |
| Color Scales | Two- or three-colour gradients. The top colour marks higher values, the bottom colour lower ones. |
| Icon Sets | Icons per value band. Choose a set. |
Faster: Quick Analysis
Select your data, then click the Quick Analysis button at the lower-right corner (or press Ctrl+Q). On the Formatting tab, hover to preview each option, then click one.
Build a custom rule
- Select the cells and go to Home > Styles > Conditional Formatting > Manage Rules.
- In the Rules Manager, select New Rule. (Or select a rule, choose Duplicate Rule, then Edit Rule.)
- Choose a rule type under Select a Rule Type, such as formatting cells based on their values, cells that contain specific text, top or bottom ranked values, above or below average, or unique or duplicate values.
- Set the options under Edit the Rule Description.
- Select Format, choose the look and click OK.
Format with a formula
- In Manage Rules, select New Rule and choose Use a formula to determine which cells to format.
- Enter a formula that starts with
=and returns TRUE or FALSE. - Select Format, choose the look and click OK.
Microsoft’s example shades every other row with =MOD(ROW(),2)=1. To stop error values from hiding formatting, wrap the formula in IS or IFERROR functions.
Manage rule order
- Rules are evaluated top to bottom. Use Move Up and Move Down to change precedence.
- Stop If True halts evaluation at a rule. It isn’t available for data bars, color scales or icon sets.
Find and clear rules
- Find all formatted cells: Home > Editing > Find & Select > Conditional Formatting.
- Find cells sharing one rule: select a cell, then Find & Select > Go To Special > Conditional formats > Same.
- Clear: Home > Conditional Formatting > Clear Rules, then Clear Rules from Selected Cells or from Entire Sheet.
Quick checklist
- Range selected before applying a rule
- Formula rules return TRUE or FALSE
- Rule order reviewed in Manage Rules
- Old rules cleared before reusing a sheet
