Use conditional formatting for a specific signal, such as overdue, above target, or unusual. Write the business rule first, apply it to the correct range, and use a formula rule when the highlight depends on another column.
- Color should reinforce a label or threshold, not replace its meaning.
- Formula rules are evaluated relative to the top-left cell of the applied range.
- Review rule order and Stop If True when several rules can affect one cell.
Choose a rule that matches the signal
Data bars help compare magnitudes, color scales show a range, and icon sets divide values into bands. A custom formula is better for a rule such as highlight the whole row when Due date is before today and Status is not Complete.
Use restrained colors with enough contrast. A report should remain understandable when printed in grayscale or viewed by someone who cannot distinguish the chosen colors.
Control the reference in a formula rule
To highlight rows based on column D, lock the column but leave the row relative. A rule such as =AND($D2<TODAY(),$E2<>"Complete") can be applied across A2:E100. Each row checks its own D and E values.
- Check the Applies to range after inserting or deleting rows.
- Put the most specific rule above a broad fallback rule.
- Test boundary values exactly at the threshold.
Highlight overdue open tasks
Due date is in D, Status is in E, and the report spans columns A through E.
- Select A2:E100 and create a formula-based rule.
- Use =AND($D2<TODAY(),$E2<>"Complete").
- Test an overdue open task, an overdue completed task, and a task due today.
Common mistakes
- Using a color scale when the business rule has a firm threshold.
- Locking both row and column in a formula that should evaluate each row.
- Applying many competing colors with no legend or priority.
- Failing to test blank cells, which may behave like zero or an old date.
Try one
In a row-highlighting rule, why might you write $D2 instead of $D$2?
The column stays fixed on D, but the row changes so each record checks its own value.
Sources
- Microsoft Excel help and learningOfficial reference for Excel features, formulas, tables, charts, and data tools.