Conditional formatting makes patterns in a worksheet easier to see without changing the underlying values. You can use it to highlight duplicate entries, overdue dates, low inventory, high values, errors or rows that meet a particular business rule.
The feature is powerful enough that the hardest part is often not finding the menu. It is deciding what should be highlighted and making sure the rule matches the data.
Start with the question you want the formatting to answer
Good conditional formatting has a purpose. Maybe you need to find overdue invoices, spot duplicate customer IDs, highlight sales above a target, or identify blank fields that still need attention.
Start from that question, then choose the simplest rule that answers it.
Quick method for common rules
Select the relevant cells, choose Home → Conditional Formatting, and pick a rule such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales or Icon Sets.
Microsoft currently documents these built-in categories and formula-based rules.
Highlight duplicates
To find duplicate values, select the range and use Conditional Formatting → Highlight Cells Rules → Duplicate Values. This is useful as a review step before deleting data. Microsoft specifically recommends highlighting duplicates so you can decide what should actually be removed.
Highlight values above or below a target
For sales, budgets or inventory, a simple greater-than or less-than rule can bring important values to the surface. Set a threshold based on a real business requirement rather than an arbitrary number.
Highlight overdue dates
Date-based rules can flag records that fall before today or within a selected period. This works well for invoices, follow-ups and renewal dates.
Use color scales carefully
Color scales show relative high and low values across a range. They are useful for scanning a report, but they are less precise than a rule with a specific threshold.
If a user must act on a clear condition, a threshold-based rule is often easier to interpret.
Use data bars for quick comparisons
Data bars place a visual bar inside each cell so relative values can be compared at a glance. They can work well in sales or inventory tables where ranking matters more than exact threshold detection.
Use icon sets when categories matter
Icon sets can classify values into a few ranges. They are useful for simple status indicators, but make sure the thresholds have a clear meaning.
Build a formula-based rule
For more specific conditions, choose a formula rule. The formula should return TRUE when the formatting should appear. Microsoft notes that formula rules can use logical functions such as AND and OR.
For example, a rule such as =AND(B2="Open",C2<TODAY()) can flag rows where a task is still open and its date has passed.
Understand relative references
Formula-based formatting follows Excel's normal reference behavior. The cell references must be written relative to the top-left cell of the selected range.
This is one of the most common reasons a rule appears to highlight the wrong rows.
Rule order and precedence
Multiple rules can apply to the same cell. Excel lets you move rules up or down and, for some configurations, stop evaluating after a rule becomes true. Microsoft documents rule ordering and precedence in its Conditional Formatting guidance.
Formatting blanks and errors
Conditional formatting can distinguish blanks, non-blanks, errors and other values. This can be useful for quality checks, but remember that a cell containing spaces is not the same as an actually blank cell.
Highlight an entire row
You can apply a formula rule to an entire row based on one cell. This is useful for project trackers and status reports.
For example, you might shade the row when the Status column equals “Blocked.” The important part is setting the references so the rule checks the correct status cell for each row.
Avoid over-formatting
If every value has a different color, the sheet becomes harder to read. Use a small visual vocabulary and reserve strong highlighting for conditions that deserve attention.
Test after sorting and filtering
Conditional formatting should be checked after common table operations. Sort the data, filter it and add a new row if the workbook is expected to grow.
Common mistakes
- Applying the rule to the wrong range.
- Using the wrong relative reference.
- Creating too many competing rules.
- Using colors without explaining their meaning.
- Assuming a visual highlight proves the underlying data is correct.
How to troubleshoot a rule
- Select the affected cell.
- Open Manage Rules.
- Confirm the range.
- Check the rule formula or condition.
- Review the rule order.
- Test the logic on a simple sample value.
When conditional formatting is not enough
Formatting can show a problem, but it does not replace a data-quality process. If the workbook needs cleanup, reconciliation or repeated transformation, combine formatting with formulas, Power Query or a documented review workflow.
Keep the formatting understandable
Add a small legend when the meaning is not obvious. For example, explain what red, amber and green mean rather than assuming every user interprets them the same way.
Use Tervilo around the spreadsheet workflow
Conditional formatting is most useful when it sits inside a broader workflow that includes clean input, formulas and verification. Tervilo can support related spreadsheet and document tasks where needed.
Use a small visual language
A shared workbook is easier to understand when a few colors or icons have consistent meanings. Reserve strong highlighting for conditions that need attention and leave ordinary values visually quiet.
Recheck rules when the range changes
After adding rows, changing formulas or copying a sheet, confirm that the formatting still applies to the intended range. Conditional formatting is useful only when the rule continues to reflect the data it is supposed to flag.
Final recommendation
Start with a specific question, choose the simplest rule that answers it, and keep the formatting restrained. Formula-based rules are powerful, but test the range and references carefully. Use formatting to direct attention—not to hide uncertainty in the data.
FAQ
What is conditional formatting used for?
It automatically changes the appearance of cells when they meet defined conditions, helping users spot patterns or exceptions quickly.
Can conditional formatting highlight duplicates?
Yes. Excel provides a built-in Duplicate Values rule for this purpose.
Can I highlight an entire row?
Yes. A formula-based rule can check a status or other field and apply the formatting across the selected row.
Why is my formula-based rule highlighting the wrong cells?
Check the selected range and make sure the formula references are written relative to the top-left cell of that range.
Sources checked
Conditional Formatting options vary by Excel version. Verify current Microsoft documentation for your environment.