What is conditional formatting in Excel?
Quick answer
Conditional formatting in Excel changes how a cell looks (fill color, font color, icons or data bars) automatically when its value meets a rule you set, such as greater than 0, a date before today or the top 10 values. Change the value and the formatting updates.
Last updated
Updated · By Robert Breen
Why it matters
People do not read every number in a sheet; they scan for the ones that need action. Conditional formatting does the scanning: overdue invoices turn red, low stock turns orange, and the eye goes straight to them.
Because it is a rule, not a color you painted on, it never goes stale. Fill a cell red by hand and it stays red after the invoice is paid; a rule clears itself.
A worked example
An accounts receivable clerk has 120 open invoices with a Due date column. She selects the column and adds a rule: format cells with a date before today in red. Then she adds Data Bars to the Amount column so large balances stand out.
On Monday 9 invoices are red, and three of them have long bars, totaling $18,400. Those three get a phone call first. As payments come in and she deletes those rows or marks them paid, the colors update without anyone repainting cells.
Often confused with
Conditional formatting vs manual formatting
Manual formatting is fixed. Conditional formatting follows a rule and changes with the data.
Conditional formatting vs a filter
A filter hides rows that do not match. Conditional formatting keeps every row visible and highlights the ones that match.
Built-in rules vs formula rules
Highlight Cells Rules and Top/Bottom Rules cover common cases. For anything else, such as coloring a whole row when its Status column says "Late", use a rule with a formula that returns TRUE or FALSE.
Tips
- Use one or two colors with a clear meaning; a rainbow sheet highlights nothing.
- Apply rules to whole table columns so new rows get them too.
- Use Home > Conditional Formatting > Manage Rules to see, edit and order every rule on the sheet.
Related terms
Learn it in a free lesson
- Excel for the Web Tutorial: Your First Budget Spreadsheet (about 15 minutes)
Sources
Written from these official help pages. Menus change, so if a button has moved, the help page has the current wording.
Frequently asked questions
- How do I highlight a whole row based on one cell?
- Select the rows, add a New Rule that uses a formula such as =$E2="Late", and pick a format. The $ keeps the rule looking at column E on every row.
- Does conditional formatting work in Excel for the web?
- Yes. It is on the Home tab, with the same Highlight Cells, Top/Bottom, data bar, color scale and icon set options.
- Can I copy conditional formatting to other cells?
- Yes. Use the Format Painter, or extend the "Applies to" range in Manage Rules.
All microsoft 365 terms, A to Z · Free microsoft 365 lessons