How to Use Conditional Formatting to Highlight Data
Beginner
How-To · Excel
Automatically color cells based on their value — over budget, below target, duplicate entries, and more.
- Select the range of cells you want to apply formatting to.
- Go to Home > Conditional Formatting > Highlight Cells Rules for simple cases like "Greater Than" a specific number.
- For visual gradients (like a heat map of values), use Conditional Formatting > Color Scales instead.
- For something more specific, use Conditional Formatting > New Rule > "Use a formula to determine which cells to format" and write a logical formula, e.g. =A1<0 to highlight negative numbers.
- To highlight duplicate entries, use Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Use Conditional Formatting > Manage Rules to edit, reorder, or delete rules once you have several applied to the same range.
Running into an error instead of just needing the how-to? Check our Excel troubleshooting guides or the error lookup.
Frequently Asked Questions
Does conditional formatting change my actual data?
No — it only changes how cells are displayed (color, font, icons) based on rules you set. The underlying values in the cells remain completely unchanged.
Will formatting rules follow the data if I sort or filter?
Yes — conditional formatting is tied to the cell reference or range, so it continues to apply correctly as data moves through sorting or filtering.
More Excel how-tos
How to Build a Simple Budget Spreadsheet
Set up a working monthly budget with income, expenses, and an automatic running total.
How to Pull Data From Another Sheet With XLOOKUP
Look up a value in one table and pull back matching information from another — the modern replacement for VLOOKUP.
How to Build a Pivot Table From Scratch
Summarize a large dataset into totals, averages, and counts without writing a single formula.