Conditional formatting is useful when you need a spreadsheet to show you what deserves attention. Instead of scanning hundreds of rows manually, you can create rules that highlight values based on their contents.
The formatting changes how a cell is displayed; it does not change the underlying value.
Highlight values above a threshold
Select the range, open Home > Conditional Formatting, choose a rule such as Greater Than, enter the threshold and select a format. This is useful for sales targets, overdue amounts, scores or inventory levels.
Highlight duplicate values
For a list of IDs, emails or product codes, use the duplicate-values rule to make repeated entries visible before deciding whether they are actually duplicates. A repeated customer name, for example, may be legitimate if the rows represent separate orders.
Highlight overdue dates
If due dates are in A2:A100, a custom rule can compare them with today. A typical formula is:
The blank check prevents empty cells from being treated as overdue.
Highlight an entire row based on one cell
Suppose column C contains status and you want to highlight the whole row when the status is Pending. Select the entire data range and create a formula rule such as:
The dollar sign locks the status column while the row number remains relative, allowing the rule to evaluate each row correctly.
Use color scales and data bars carefully
Color scales and data bars can reveal relative differences quickly. They are most useful when the reader understands what the visual scale means. Avoid decorative formatting that makes a sheet harder to interpret.
Conditional formatting for a PivotTable
Excel also supports conditional formatting in PivotTable reports, although some rule types have additional considerations. Test the rule after refreshing or changing the PivotTable layout.
Common mistakes
- Wrong starting cell: Formula-based rules depend on the first cell of the selected range.
- Incorrect absolute references: Decide which row or column should move as the rule is applied.
- Formatting blanks: Add an explicit blank check when blank cells should be ignored.
- Too many competing rules: Multiple rules can make the visual result confusing.
- Using formatting as data: A highlighted cell is still the same underlying value; do not rely on color alone for calculations.
A practical review workflow
- Select the exact data range.
- Write the business question: What should stand out?
- Choose the simplest rule that answers it.
- Test a row that should match and one that should not.
- Check blanks and boundary values.
- Review the result after sorting or refreshing the data.
Quick answer
Use conditional formatting when the goal is to make patterns, exceptions or thresholds easier to see. Start with a specific question, create the simplest rule that answers it, and test both matching and non-matching rows.
Related Excel guides
For data-quality cleanup, see How to Clean Data in Excel. For duplicate records, see How to Remove Duplicates in Excel. For interactive data entry, see How to Create a Drop-Down List in Excel.