What is conditional formatting in Excel and Google Sheets?
Quick answer
Conditional formatting is a spreadsheet feature that changes how a cell looks, such as its fill colour, when its value meets a rule you set, for example "greater than 0" or "Status is Done". The colour updates by itself whenever the data changes.
Last updated
Take the free module →Module 1 of the free Excel and Word for the Web Jump Start course, about 15 minutes. No account needed.
Updated · By Robert Breen
Why it matters for a small business
A list of numbers hides its problems until someone reads every line. A rule that turns the bad lines red, or the unbilled jobs yellow, makes the answer visible at a glance, and it keeps working next month without anyone checking by hand.
Excel has ready-made Highlight Cell Rules (Greater Than, Less Than, Text that Contains) and colour scales. Google Sheets has the same basics, plus "Custom formula is", which can colour a whole row based on one column, such as =$D2="Done".
In a real lesson: Excel for the Web: Your First Budget Spreadsheet
In the free Excel for the web budget lesson, Tom (made up) owns a small bakery and his bookkeeper wants to know where he went over budget. The table has a Difference column, Actual minus Budget.
You select the Difference cells and choose Home, Conditional Formatting, Highlight Cell Rules, Greater Than. In Excel for the web that opens a panel on the right: type 0, keep the light red style and click Done. Ingredients (+$360) and Utilities (+$62) turn red; Wages and Marketing, which came in under, stay plain.
The Google Sheets job tracker lesson uses a custom formula instead, =$D2="Done", to turn every finished-but-unbilled job yellow across the whole row.
Try this lesson free or read the step-by-step guide.
Common confusions
Conditional formatting vs a filter
A filter hides rows that do not match. Conditional formatting keeps every row and colours the ones that do.
Highlight Cell Rules vs a custom formula
A Highlight Cell Rule looks at each cell's own value. A custom formula can look at another column, which is how you colour a whole row from one Status cell.
Tips
- Use one colour per meaning, such as red for over budget, so nobody has to guess.
- In a custom formula, put a dollar sign before the column letter ($D2) so every cell in the row checks the same column.
Related terms
More Data, Sheets and Gmail terms
Where you use it: free lessons
- Excel for the Web: Your First Budget Spreadsheet (Excel, 15 min)
- Google Sheets: Build a Job Tracker That Totals Itself (Google Sheets, 15 min)
- Claude's Excel Skill: Turn a Messy Export into a Clean Workbook (Claude, 8 min)
Frequently asked questions
- How do I highlight cells greater than 0 in Excel?
- Select the cells, choose Home, Conditional Formatting, Highlight Cell Rules, Greater Than, type 0, pick a style and click OK (Done in Excel for the web).
- Can conditional formatting colour a whole row?
- Yes, with a custom formula rule applied to the whole range, such as =$D2="Done" in Google Sheets or "Use a formula to determine which cells to format" in Excel.
- Does conditional formatting change the data?
- No. It only changes how cells look. The values, totals and formulas stay the same.