Conditional formatting changes a cell’s colour automatically based on its value. It turns a wall of numbers into something you can read at a glance: overdue invoices in red, top sellers in green, duplicates highlighted. Here are 10 practical examples you can use at work today.
Quick answer
- Select the cells you want to format.
- Go to Home → Conditional Formatting.
- Pick a rule (for example Highlight Cells Rules → Greater Than), enter a value, choose a colour and click OK.
Example 1: Highlight values above a target
Select your sales column, then Home → Conditional Formatting → Highlight Cells Rules → Greater Than. Type 1000 and pick Green Fill with Dark Green Text. Every sale above 1,000 turns green.
Tip: instead of typing 1000, click a cell that holds the target (for example $H$1). Change the target later and the colours update.
Example 2: Find duplicates
Select a column of invoice numbers or emails, then Highlight Cells Rules → Duplicate Values. Any value that appears more than once is highlighted, which is perfect for spotting double entries before you send a report.
Example 3: Highlight text that contains a word
Select a status column, then Highlight Cells Rules → Text that Contains. Type Pending and choose a yellow fill. Add a second rule for Cancelled in red.
Example 4: Show dates in the next 7 days
Select a due-date column, then Highlight Cells Rules → A Date Occurring and choose Next week or This week. Useful for deadlines and contract renewals.
Example 5: Top 10 and bottom 10
Select your numbers, then Top/Bottom Rules → Top 10 Items. You can change 10 to any number, or use Top 10% or Below Average instead.
Example 6: Data bars
Select the numbers, then Conditional Formatting → Data Bars and choose a colour. Each cell gets a small bar showing its size compared to the others, like a mini chart inside the table.
Tip: to show only the bars, go to Manage Rules → Edit Rule and tick Show Bar Only.
Example 7: Colour scales (heat map)
Select a grid of numbers (for example sales by month and region), then Color Scales and pick Green–Yellow–Red. High values turn green and low values red, so patterns jump out.
Example 8: Highlight a whole row
This is the most useful trick, and it needs a formula.
- Select the whole table without headers, for example
A2:F100. - Go to Conditional Formatting → New Rule → Use a formula to determine which cells to format.
- Enter
=$E2="Overdue"and choose a light red fill.
The $ before E locks the column, so every cell in the row checks column E. The row number has no $, so each row checks its own status.
Example 9: Overdue and unpaid invoices
Combine two conditions with AND. With due dates in column D and status in column E, select A2:F100 and use:
=AND($D2<TODAY(), $E2<>"Paid")
Only rows that are past due and not paid turn red. The rule updates by itself every day because it uses TODAY().
Example 10: Shade weekends or alternate rows
- Weekends (Friday and Saturday): with dates in column A, use
=OR(WEEKDAY($A2)=6, WEEKDAY($A2)=7). For a Saturday–Sunday weekend use=WEEKDAY($A2,2)>5. - Alternate rows: use
=MOD(ROW(),2)=0and a light grey fill for easy-to-read banding.
Manage, edit and remove rules
- See all rules: Home → Conditional Formatting → Manage Rules. Change “Show formatting rules for” to This Worksheet to see everything.
- Order matters: rules at the top run first. Use the arrows to reorder them.
- Remove rules: Conditional Formatting → Clear Rules → from selected cells or the entire sheet.
Common mistakes
- Wrong $ signs in formulas: use
$E2(column locked, row free) for whole-row rules.$E$2makes every row check the same cell. - Formula written for the wrong row: always write the formula for the first row of your selection.
- Too many colours: two or three colours with a clear meaning are easier to read than a rainbow.
- Rules multiplying: copying and pasting cells can duplicate rules. Clean up in Manage Rules now and then.
FAQ
Does conditional formatting work in Google Sheets?
Yes: Format → Conditional formatting. Custom formulas work the same way.
Can I filter by colour?
Yes. Turn on filters (Ctrl + Shift + L), open the filter arrow and choose Filter by Color.
Next steps
- 25 Excel formulas every office worker should know
- Free monthly budget template (uses conditional formatting to flag overspending)