- Where conditional formatting lives in Google Sheets
- Single colour rules: highlight cells that meet a condition
- Colour scale rules: shading a range from low to high
- Custom formula rules in Google Sheets conditional formatting
- Rule order and overlapping conditional formatting
- Conditional formatting in the Google Sheets app on a phone
- Copying and clearing conditional formatting
- Conditional formatting ideas for a small business sheet
- If conditional formatting isn't working in Google Sheets
- Frequently asked questions
To use conditional formatting in Google Sheets, select your cells, click Format, then Conditional formatting, and build a rule in the right-hand toolbar. Choose single colour, colour scale or a custom formula, then click Done. Google says the first true rule sets the format.
A spreadsheet full of plain black numbers makes you do all the noticing. Conditional formatting flips that around: the sheet colours the cells that need your attention, so overdue jobs go red, healthy stock goes green and duplicates stand out without you hunting for them.
It's a small feature that makes team spreadsheets far easier to scan. I'll cover the three rule styles Google's help describes, the custom formula route and the mistakes that make rules behave oddly.
Where conditional formatting lives in Google Sheets
On a computer, Google's steps are short. Open your spreadsheet, select the cells you want to format, click Format, then Conditional formatting. A toolbar opens on the right-hand side. Create your rule there, then click Done.
The selection matters. Rules only apply to the cells you chose, so select the whole column or block first, such as a status column or a set of dates, rather than a single cell.
Single colour rules: highlight cells that meet a condition
This is the one most people want. In the toolbar, under Format cells if, choose the condition that triggers the rule. Under Formatting style, choose how matching cells should look, and menus vary a little between versions.
- Select the status column in your tracker.
- Click Format, then Conditional formatting.
- Under Format cells if, pick the condition, for example text is exactly, and type the word you're watching for.
- Under Formatting style, choose how matching cells should look.
- Click Done.
Every cell that now matches changes colour, and it updates by itself when you edit the cell.
Colour scale rules: shading a range from low to high
When you want to see a pattern across a column of numbers, use a colour scale. Under Preview, select a colour scale. You then set a minimum value and a maximum value, plus an optional midpoint, and you can use the down arrow to change the value category.
Google's Android page adds a handy note: if you don't set a value, it adjusts automatically based on your data. That's a good starting point if you're not sure what limits to choose. It suits things like hours logged per team member or enquiries per week, where you care about the shape rather than any exact threshold.
Custom formula rules in Google Sheets conditional formatting
This is the powerful one. When the built-in conditions don't cover what you need, select your cells, and under Format cells if click Custom formula is. Enter your formula under Value or formula, choose the formatting, and click Done.
Google's example for highlighting duplicates uses the COUNTIF function over a fixed column range, counting how many times each value appears and flagging anything above one. The formula is written for the first cell of your selection, and Sheets adjusts it for the rest.
A second pattern Google describes is highlighting a whole row based on one column. You select the entire block, then write a formula that points at the status column, with the column letter locked using the absolute-reference symbol and the row number left free. Locking the column but not the row is what lets every cell in the row follow the same status cell.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
- Formulas can only look at the same sheet. To check another sheet, Google says to use the INDIRECT function.
- Text typed into a formula needs quotation marks around it.
- Test the rule on a small range first so you can see what it does before you apply it to the lot.
Rule order and overlapping conditional formatting
You can stack several rules on the same cells, for example red for overdue, amber for due soon and green for done. Google says rules are checked in order, and the first true rule sets the format. If a colour isn't showing, a rule above it may be catching the cell first. Open the toolbar and drag the rules into the order you want.
| Rule type | Best used when you want to |
|---|---|
| Single colour | Flag cells that match a word, date or number condition |
| Colour scale | See low-to-high patterns across a column |
| Custom formula | Highlight duplicates, whole rows or anything the standard conditions miss |
Conditional formatting in the Google Sheets app on a phone
On Android, Google's help gives nearly the same route: highlight the cells, open the Format menu, choose Conditional formatting and a panel appears. Set the rule and tap Done. The iPhone and iPad version has its own help page, and menus change between app updates, so if a step looks different on your screen, trust the screen.
Copying and clearing conditional formatting
Here's a detail that can catch you out. Google says copying and pasting cells also pastes their conditional formatting rules. That's brilliant when you want the same colours on a second tab, and annoying when you paste in values from elsewhere and a stray rule comes along. To remove one, open the toolbar for those cells and delete the rule from the list.
Conditional formatting ideas for a small business sheet
- A job tracker where overdue dates turn red.
- A content calendar where each status gets its own colour, so a client can see progress at a glance. If you plan posts there, my thoughts on collecting content requests with form tools pair well with it.
- A stock list where low quantities stand out.
- A contact list where repeated names are flagged before you email anyone twice.
And if the sheet feeds paperwork, my guide to setting up a Google Docs template you can reuse is a natural next read.
If conditional formatting isn't working in Google Sheets
- Check the rule's range. It only formats the cells it was applied to.
- Check the order of the rules. The first true rule wins.
- Check the formula points at the right cell, and that a text match has quotation marks.
- If the formula refers to another sheet, wrap that reference in INDIRECT.
- Check for stray rules brought in by copy and paste.
Frequently asked questions
How do I use conditional formatting in Google Sheets?
Select your cells, click Format, then Conditional formatting. Create a rule in the toolbar on the right, choosing a condition and a formatting style, and click Done.
How do I highlight duplicates in Google Sheets?
Use a custom formula rule with COUNTIF. Google's example counts how often each value appears in the column and formats anything above one.
Can conditional formatting colour a whole row?
Yes, with a custom formula rule applied to the whole block, where the formula points at one column and locks the column letter but not the row number.
Why isn't my conditional formatting working?
Common causes are the wrong range, a rule higher up catching the cell first (Google says the first true rule sets the format), or a formula that points at another sheet without INDIRECT.
Can I use conditional formatting on my phone?
Google's help covers the Sheets app on Android and iPhone or iPad. On Android you highlight cells, open the Format menu, choose Conditional formatting, set the rule and tap Done.
