Conditional Formatting - Highlight by a rule
Make overdue items stand out with a rule that reevaluates as values change, while keeping appearance separate from the data.
By the end, you can
- Create a numeric highlighting rule.
- Verify the threshold boundary and Applies to range.
- Distinguish automatic highlighting from manually applied fill.
Before you start: Understand a numeric comparison and selected ranges. Steps use Windows desktop Excel.
In this lesson
A format determined by a condition
Conditional Formatting applies a visual format when a rule is true. A collections report can highlight invoices more than 30 days overdue without manually coloring each one. The rule reevaluates when the underlying values change; it does not change the overdue count, delete an invoice, or decide its accounting status.
Overdue-age fields to highlight
45| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Days overdue | Amount INR |
| 2 | I101 | 45 | 1200 |
| 3 | I102 | 0 | 800 |
| 4 | I103 | 60 | 2500 |
| 5 | I104 | 30 | 300 |
Create and inspect the Excel rule
- Select B2:B5, excluding the Days overdue header.
- Choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
- Enter 30 and select a readable highlight format, then confirm.
- Choose Home > Conditional Formatting > Manage Rules. Inspect the rule and its Applies to range, $B$2:$B$5.
- Check that the 45- and 60-day values are highlighted and 0 and 30 are not.
- Change the 45-day value to 20 temporarily. Its rule-based highlight should disappear; undo the edit to restore the example.
Highlighted| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Days overdue | Rule >30 outcome |
| 2 | I101 | 45 | Highlighted |
| 3 | I102 | 0 | Not highlighted |
| 4 | I103 | 60 | Highlighted |
| 5 | I104 | 30 | Not highlighted |
Use the right rule and scope
- Data bars can show relative magnitude; color scales can show a range. Keep a clear legend and avoid interpreting color as a precise numeric substitute.
- For a whole-record highlight, use New Rule > Use a formula to determine which cells to format, with =$B2>30 applied to A2:C5. Column B is fixed while the row follows each record.
- Use Manage Rules to inspect overlapping rules, their priority, and Stop If True when applicable.
- Clear an unwanted rule through Conditional Formatting > Clear Rules for the intended scope rather than changing the source data to make its color disappear.
Common mistakes and troubleshooting
Native Excel rule management is not implemented in the custom workspace. Its basic selection/reference skills are separate from this feature; no conditional-formatting challenge is invented.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.