Conditional Formatting in Excel and Google Sheets: Practical Recipes

Q: Where is conditional formatting in Excel?

Home → Conditional Formatting. Use New Rule → formula for row-wide colours.

Q: Where is it in Google Sheets?

Format → Conditional formatting, then set the range and the rule on the right.

Q: Can I colour a whole row?

Yes. Select the row range and use a formula like =$E2=TRUE with a locked column letter.

Q: Does conditional formatting slow down a sheet?

A few rules on a few thousand rows are fine. Hundreds of volatile rules on entire columns can feel slow; limit the range to your data.

Updated 2026-11-06

TL;DR: Conditional formatting changes a cell's colour when a rule is true. Use it to turn overdue dates red, mark overspend when Spent is greater than Budget, and colour completed rows green when a checkbox is ticked. In Excel open Home → Conditional Formatting; in Google Sheets open Format → Conditional formatting.

Conditional formatting makes a spreadsheet show problems before you have to hunt for them. Here are recipes you can copy for holiday lists, budgets and task trackers.

Excel: add a rule

Select the cells (or the whole data row range).
Home > Conditional Formatting > New Rule.
Choose "Use a formula to determine which cells to format" for row-wide rules, or a built-in highlight rule for simple cases.
Set the format (fill colour) and click OK.

Google Sheets: add a rule

Select the range.
Format > Conditional formatting.
Under Format cells if… pick a condition, or Custom formula is.
Choose a fill colour and click Done.

Recipe 1: overdue dates turn red

Dates in column D, highlight when the date is before today and the status is not Done:

Excel/Sheets custom formula (applied to A2:H100):
=$D2


To ignore blank date cells:
=AND($D2<>"",$D2

Recipe 2: budget overspend in red
Budget in C, Spent in D. Colour the Spent cell (or the whole row) when spending is over budget:
=$D2>$C2
Pair this with the Christmas gift list spreadsheet or the Christmas budget tracker.
Recipe 3: checkbox done turns the row green
Checkbox in column E (Sheets Insert → Checkbox, or Excel 365 checkbox). Format A2:H100 with:
=$E2=TRUE
If you use Yes/No text instead:
=$E2="Yes"
Recipe 4: whole row based on status text
Status in column F. Colour the row when status is Late:
=$F2="Late"
For a deeper walkthrough of row highlighting, see highlight an entire row based on a cell value.
Recipe 5: top of a number range
Highlight the top 3 values in a spend column: Excel → Conditional Formatting → Top/Bottom Rules → Top 10 Items (set to 3). Sheets → Format cells if → Top → 3.
Fixes when it looks wrong

Use a $ on the column letter in the formula ($E2) so every cell in the row looks at the same status cell
Apply the rule to the full data range starting at row 2, not only one column, if you want the whole row coloured
In Excel, clear old rules: Conditional Formatting → Manage Rules
In Sheets, check the range listed at the top of the sidebar; rules do not expand by themselves unless the range includes extra rows

Quick FAQ
Where is conditional formatting in Excel? Home → Conditional Formatting. Use New Rule → formula for row-wide colours.
Where is it in Google Sheets? Format → Conditional formatting, then set the range and the rule on the right.
Can I colour a whole row? Yes. Select the row range and use a formula like =$E2=TRUE with a locked column letter.
Does conditional formatting slow down a sheet? A few rules on a few thousand rows are fine. Hundreds of volatile rules on entire columns can feel slow; limit the range to your data.
Sources: Microsoft Support: Use conditional formatting to highlight information; Google Docs Editors Help: Conditional formatting.
← More guides