How to use conditional formatting in Excel and Google Sheets: built-in rules, custom formulas that highlight whole rows, deadlines, duplicates, and keeping rules manageable.
Conditional formatting changes how cells look based on their values: red for overspends, amber for deadlines approaching, a highlight on duplicates. It turns a wall of numbers into something you can scan in seconds. Both Excel and Google Sheets offer it, with slightly different menus.
Where to find it?
- Excel: select the cells, then Home > Conditional Formatting. Choose a preset rule, or New Rule > "Use a formula to determine which cells to format".
- Google Sheets: select the cells, then Format > Conditional formatting. Choose a condition, or "Custom formula is".
Built-in rules that cover most needs
- Greater than / less than: highlight values over a limit, such as spending over £100.
- Text contains: highlight rows marked "Overdue" or "Urgent".
- Dates: highlight dates in the past, this week, or next month.
- Duplicates: Excel has a "Duplicate Values" preset. In Sheets, use a custom formula (see below).
- Colour scales: shade cells from low to high, useful for spotting patterns in large tables.
- Data bars (Excel): in-cell bars showing relative size.
Custom formula rules: the powerful part
A custom formula rule applies the format when the formula returns TRUE. Write the formula as if for the top-left cell of your selection; the spreadsheet adjusts it for every other cell.
Highlight a whole row
To highlight every row in A2:F100 where column E says "Overdue", select A2:F100 and use:
=$E2="Overdue"
The dollar sign before E locks the column, so every cell in a row checks column E. The row number has no dollar sign, so it changes for each row. This is the most common source of confusion: get the dollar signs right and most rules work.
Deadlines approaching
With due dates in column C, starting at C2:
- Overdue:
=AND($C2<>"",$C2<TODAY()) - Due within seven days:
=AND($C2>=TODAY(),$C2<=TODAY()+7)
Put the overdue rule first, or above the other, so it takes priority.
Duplicates in Google Sheets
For values in A2:A500: =COUNTIF($A$2:$A$500,$A2)>1
Alternate row shading
=ISEVEN(ROW()). Sheets also has Format > Alternating colours, and Excel tables have banded rows built in.
Order and priority
When several rules apply to the same cell, order matters. In Excel, use Manage Rules to reorder them and "Stop if true" to prevent lower rules applying. In Sheets, rules higher in the list take priority; drag to reorder.
Keep it manageable
- Use a few meaningful colours. Too much colour makes nothing stand out.
- Don't rely on colour alone: about one in 12 men and one in 200 women have colour vision deficiency. Add a status column or icons too.
- Apply rules to whole tables or columns rather than many small ranges, which multiply when you copy and paste.
- Review rules occasionally: copying cells copies rules, and duplicates can slow large files.
- Put thresholds in cells, such as a budget limit in H1, and refer to them (
=$D2>$H$1) so you can change them without editing rules.
Step by step: a budget overspend rule
- Select the "Difference" column (budget minus actual), say D5:D30.
- Add a rule: "less than" 0, or the custom formula
=$D5<0. - Choose red text or a light red fill.
- Check it by entering a test value.
Frequently asked questions
Why does my rule highlight the wrong cells?
Usually the formula was written for a cell other than the top-left of the selection, or the dollar signs are wrong.
Can conditional formatting refer to another sheet?
In Google Sheets, custom formulas can only refer to other sheets using INDIRECT, for example =$A2>INDIRECT("Settings!B1"). In current Excel versions, direct references to other sheets generally work, but a named range is clearer and easier to maintain.
Does conditional formatting change the values?
No. It only changes how cells look, so sorting, filtering and formulas are unaffected. In both programs you can also sort or filter by cell colour, which is handy for pulling out all highlighted rows.
Sources and further reading
- Microsoft Support: Excel functions (by category)
- Google Docs Editors Help: Google Sheets function list
- Microsoft Support: Use conditional formatting to highlight information
- Google Docs Editors Help: Use conditional formatting rules in Google Sheets
- NHS: Colour vision deficiency
Facts checked on 20 October 2026. Spotted an error? Tell us and we will correct it.