Excel / 6 min read
Excel Conditional Formatting Basics
Highlight overdue dates, high values, duplicates, and status rows with conditional formatting rules that are easy to maintain.
Conditional formatting makes important cells stand out automatically. Start with one clear rule at a time, especially for overdue dates, duplicates, and status-based highlights.
Check this first
- Apply the rule to the correct range before testing formulas.
- Write the formula as if it starts in the top-left cell of the target range.
- Check absolute and relative references carefully.
- Keep overlapping rules minimal so the result stays readable.
Working examples
Highlight overdue dates in column B
=AND($B2<TODAY(), $C2<>"Done")Why this feature matters
Conditional formatting helps users scan a sheet faster. Instead of reading every number or date manually, they can notice the rows that need attention immediately.
That makes it valuable for trackers, invoice lists, project boards, and operations reports.
The reference rule people miss
When you use a formula rule, Excel behaves as if the formula starts in the top-left cell of the applied range and is copied across the rest. Most confusing results come from references that were locked incorrectly.
- Lock columns when the rule should always check one specific field.
- Leave row numbers relative when the rule should evaluate each row independently.
- Test on a small range before applying it to the whole sheet.
Keep visual rules purposeful
If every row has multiple colors, the formatting stops helping. Give each rule a clear job, such as overdue, duplicate, or complete, and keep the color system simple enough that someone can understand it at a glance.