Excel Conditional Formatting: 6 Rule Types, Formulas and Limits
Select your cells, then choose Home > Styles > Conditional Formatting and pick a preset such as Highlight Cells Rules, or build your own with a formula rule that starts with an equal sign and returns TRUE or FALSE. Excel offers 6 advanced rule types, evaluates them from the top of the list down, and lets a rule override your manual fill. It works in Microsoft 365, Excel 2024, 2021, 2019 and 2016.
Where the command lives: Home > Styles > Conditional Formatting
Microsoft puts every preset in one menu: Home > Styles > Conditional Formatting. Its submenus are Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, Icon Sets and Manage Rules. The same page applies to Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019 and Excel 2016.
You can apply a rule to a cell range, an Excel table or a PivotTable report (PivotTable support is Excel for Windows only).
There is also a faster door. Select a block of data and press Ctrl+Q to open Quick Analysis. For numbers or mixed data its Formatting tab offers Data Bars, Colors, Icon Sets, Greater, Top 10% and Clear. For text only, it offers Text, Duplicate, Unique, Equal To and Clear.
The 6 advanced rule types behind New Rule
Open Manage Rules or New Rule and the Conditional Formatting Rules Manager shows the full set. Microsoft lists these 6 types:
- Format all cells based on their values (the default): color scales, data bars and icon sets.
- Format only cells that contain: a cell value, specific text, a date, blanks, no blanks, errors or no errors.
- Format only top or bottom ranked values: 1 to 1000 items, or 1 to 100 percent.
- Format only values that are above or below average, including one, two or three standard deviations.
- Format only unique or duplicate values.
- Use a formula to determine which cells to format.
Color scales, data bars and icon sets have their own number rules
A two-color scale needs the Minimum to be lower than the Maximum. A three-color scale needs Minimum < Midpoint < Maximum, and the default midpoint is 50 percent. Percent and percentile values must lie between 0 and 100 and are typed without a percent sign.
Icon sets split your data into 3 to 5 categories using threshold values. The default is the 3 Traffic Lights (Unrimmed) set. Data bars offer Solid Fill or Gradient Fill, a Show Bar Only option and a Bar Direction setting whose default is Context.
A worked example: color a whole row when Status says Overdue
Say an invoice list sits in A2:D20, with the status in column D. The goal is a light red fill on the entire row when D says Overdue.
Step 1: select A2:D20. Step 2: choose Conditional Formatting > New Rule > Use a formula to determine which cells to format. Step 3: type =$D2="Overdue". Step 4: click Format, choose a fill, and click OK.
The dollar sign before D locks the column, so every cell in the row checks column D. The 2 has no dollar sign, so it moves down as the rule fills rows 2 to 20. Without the dollar sign, B2 would test column E, and the colors would land on the wrong cells.
Google Sheets uses the same idea. Google's own examples are =$B1="Yes" to color a row and =COUNTIF($A$1:$A$100,A1)>1 to flag duplicates in A1:A100.
Rule order, Stop If True and the three-rule limit in old files
Rules run from top to bottom in the Rules Manager, and a new rule is added at the top. Use Move Up and Move Down to change the order. Stop If True is available there, but not for data bars, color scales or icon sets.
Microsoft notes that earlier Excel versions evaluate only the first three rules. A workbook with 5 rules may look right on your PC and different when a colleague opens it in an old version.
On Google Sheets the order also matters: the first rule that is true sets the format, and you reorder by dragging.
Formula rules must return TRUE or FALSE, and errors are skipped
Every formula rule starts with an equal sign and must give TRUE (1) or FALSE (0). If the formula returns an error, Excel does not format the cell, so Microsoft suggests wrapping it in an IS function or IFERROR.
Text rules accept wildcards, and the text string can be up to 255 characters. The unique and duplicate rule also treats * and ? as wildcards, even inside a formula.
Why your manual fill disappears
When a rule and a manual format both apply, the rule wins. Manual formats are not listed in the Rules Manager, so the fill simply seems to vanish. To find out why, go to Home > Find & Select > Conditional Formatting. To find only the cells that share one rule, use Go To Special > Conditional formats > Same.
Copying and filling cells that carry a rule creates a new rule for the destination cells, so a sheet can collect many near-identical rules over time. Open Manage Rules to review them. To clear them, use Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells, or Clear Rules from Entire Sheet. If a rule behaves oddly right now, Crade can read your screen and explain what the dialog is doing.
PivotTable value fields have extra limits
In a PivotTable you can scope a rule by selection (the default), by corresponding field or by value field. Values fields cannot use the unique or duplicate rule, and they can be formatted by number only, not by text or date.
Who it suits
Presets suit quick checks such as the top 10 items or values above average, and Quick Analysis gets you there with Ctrl+Q. The formula rule suits row highlights and multi-column checks. If a file will be opened in an older Excel version, keep it to 3 rules, because older versions read only the first three.
Questions people ask
How do I highlight an entire row based on one cell?
Select the whole range, choose Conditional Formatting > New Rule > Use a formula, and type a formula with a dollar sign before the column letter, such as =$D2="Overdue". The dollar sign keeps the test on column D for every cell in the row.
How many conditional formatting rules can I use?
Microsoft's page does not state a maximum for current Excel. It says earlier Excel versions evaluate only the first three rules, and VBA documentation says a range can now hold more than three.
Why is my conditional formatting not working?
Check that a formula rule starts with = and returns TRUE or FALSE, that a formula returning an error is wrapped in IFERROR, and that a higher rule is not stopping the check with Stop If True. Also confirm the Applies to range in Manage Rules.
How do I remove conditional formatting?
Select the cells and go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Choose Clear Rules from Entire Sheet to remove every rule on the sheet.
Can I use Ctrl+Q for conditional formatting?
Yes. Ctrl+Q opens Quick Analysis, and its Formatting tab offers Data Bars, Colors, Icon Sets, Greater, Top 10% and Clear for numbers. It gives presets only, not formula rules.
Is conditional formatting the same in Google Sheets?
It is similar. In Google Sheets you choose Format > Conditional formatting, and the first true rule in the list sets the format. Custom formulas can reference only the same sheet unless you use INDIRECT.