Conditional Formatting: Let Colors Do the Talking

A sheet where you have to hunt down duplicate values, overdue deadlines, or deviations from average by eye hasn't done its job — the data is there, but you still have to extract the information from it. Conditional formatting flips that step around: you define a rule once (greater than, duplicate, below average), and from then on Excel highlights matching cells in color on its own, however the data changes.

A typical scenario

Ondřej tracks invoice due dates in a sheet with two hundred rows. Without conditional formatting, he'd have to scan the entire due-date column at every check and compare it against today's date in his head — with two hundred rows, something easily slips by, especially right around a deadline.

With conditional formatting, he sets up a rule once that the date is earlier than today, and from then on Excel colors every overdue invoice red automatically — instantly, the moment the workbook opens, with nothing to check by hand. When an invoice gets paid and the date updates, the color disappears on its own.

How to do it

1. Select the data range you want the rule applied to and go to Home → Conditional Formatting.
2. Choose a rule type from the menu — the most useful ones are Highlight Cells Rules (Duplicate Values, Greater Than, Less Than, Between) and Data Bars (mini bar charts right inside the cells, good for quickly comparing numbers visually).
3. For number and date rules, you enter a threshold value (say, today's date or a target number) and choose the highlight format — fill color or font color.
4. The rule stays on the cells permanently and recalculates automatically every time the data changes — you don't have to re-run it after editing numbers.
5. You can manage multiple rules on the same range through Conditional Formatting → Manage Rules, where you can see their order and edit or delete anything that no longer applies.

The best tools

• Excel's built-in Conditional Formatting — covers most needs from duplicates to deadlines, with no extra formula required.
• A custom formula in conditional formatting, a more advanced option in the dialog, for rules that can't be built from the ready-made templates, like comparing two columns against each other.
• Google Sheets — you'll find conditional formatting under Format → Conditional formatting, with a similar set of rules and color scales.

What you get out of it

• Time: roughly 10 minutes a week that would otherwise go to manually scanning a sheet for exceptions.
• Fewer missed problems: an overdue date, a duplicate, or an outlier shows up in color on its own, even when a check isn't top of mind.
• Faster decisions: color in a sheet works as a first filter for attention — you know right away where to look before reading the numbers one by one.

Pro tip

Data bars also work well as a quick visual check without building a chart — with a column of dozens of numbers, you can immediately see which values stand out, without calculating anything.