Conditional Formatting in Excel
Make Excel color cells automatically. Highlight rules, data bars, color scales and custom formulas, with examples.
Apply a basic rule
- Select the cells to format.
- Go to Home > Conditional Formatting.
- Choose Highlight Cells Rules and pick a rule such as Greater Than, Text that Contains or Duplicate Values.
- Enter the value and choose a format, then click OK.
Top and bottom rules
Use Top/Bottom Rules to color the top 10 items, the bottom 10%, or values above average.
Data bars, color scales and icon sets
Data Bars show a bar inside each cell sized by its value. Color Scales shade from low to high, for example red to green. Icon Sets add arrows or traffic lights.
Highlight a whole row with a formula
- Select the table rows, for example A2:F100.
- Choose Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- Enter a formula with an absolute column, for example:
Choose a fill color and click OK. Every row where column E says Overdue is shaded.
More formula examples
The first marks past dates, the second combines two conditions and the third flags duplicates.
Manage and remove rules
Open Conditional Formatting > Manage Rules to edit, reorder or delete rules. To clear them, choose Clear Rules from the same menu.
Frequently asked questions
How do I highlight a row based on a cell value?
Use a formula rule with an absolute column reference, such as =$E2=”Overdue”, applied to the whole table range.
How do I highlight duplicates in Excel?
Select the range and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Why is my conditional formatting not working?
Check that the rule range is correct, that numbers are not stored as text and that the formula uses the right absolute and relative references.
Can I use conditional formatting with dates?
Yes. Use the A Date Occurring rule or a formula such as =$C2<TODAY() for overdue dates.
How do I copy conditional formatting?
Use Format Painter on the Home tab, or Paste Special > Formats.
Need Excel work done for you?
More free tools: All free calculators · Excel automation services · Excel templates · Budget template