Microsoft Excel · Data preparation · Beginner
Highlight Values with Conditional Formatting in Excel
Highlight exceptions with conditional formatting in Excel with a complete practice example, expected results and recovery checks.
Official documentation reviewed; example logic independently checked; native Excel execution not performed · Editorial standards
Quick answer: Select the numeric range, then use Home → Conditional Formatting → Highlight Cells Rules → Greater Than. Check equality separately: 100 does not meet a greater-than-100 rule.
Version and platform: Microsoft 365 Excel for Windows desktop; Mac, Excel for the web and Excel 2013 interfaces not covered. Use a disposable workbook for the exercise.
Conditional formatting helps surface exceptions without changing the underlying values. Use this small benchmark so the result is easy to verify: enter 92, 105, 130, and 87 in B2:B5. The goal is to highlight only values greater than 100.
Create a simple threshold rule
- Select
B2:B5. - On Home, open Conditional Formatting.
- Choose Highlight Cells Rules → Greater Than and enter
100. - Pick a readable fill/font combination and confirm the rule.
- Verify that 105 and 130 are formatted while 92 and 87 are not.
Microsoft also documents color scales, data bars, icon sets, duplicate/unique checks, text/date rules and formula-driven conditional formatting. For formula rules, the formula must return a logical result; relative and absolute references control how the rule moves through the applied range.
Manage rather than recreate rules
Use Home → Conditional Formatting → Manage Rules to inspect the range a rule applies to, change precedence, edit it, duplicate it or remove it. This matters when two rules compete for the same cells. Excel’s Rules Manager also exposes Stop If True where applicable.
Troubleshooting
- A number is not highlighted: check whether it is actually stored as text.
- The wrong rows are highlighted: inspect relative references if you used a formula rule.
- A rule works in ordinary cells but behaves differently in a PivotTable: Microsoft documents extra PivotTable scoping options; do not assume an ordinary range and a PivotTable field are identical.
- The formula returns an error: conditional formatting is not applied to cells whose relevant formula result is an error; handle the error explicitly if needed.
Check the boundary and maintain the range
Label A1 as Item and B1 as Amount; put Alpha, Beta, Gamma, Delta in A2:A5 alongside the four numbers. Work in a new workbook so an old rule cannot disguise your result. The rule should apply to =$B$2:$B$5, not to the heading or the entire column. In Manage Rules, choose the worksheet in Show formatting rules for if the rule disappears when another cell is selected.
Change B2 from 92 to 100. It must remain unformatted because Greater Than excludes equality. Change it again to 101: now B2, B3 and B4 should be highlighted. Restore 92 and confirm the original two highlighted cells return. This is a better test than simply seeing some colored cells after creation.
Enter 150 in B6. It is outside the original range; do not assume the rule includes it. Extend Applies to to =$B$2:$B$6 deliberately, then check B6. Save as XLSX and reopen to inspect both the rule and its range. If you need to start over, delete this specific rule in Rules Manager rather than clearing unrelated formatting across the sheet.
Official references
Documentation snapshots retrieved 20 October 2026. These instructions and example checks are documentation-based; no native Excel run or application screenshot is claimed.