How to Use Conditional Formatting in Google Sheets (Rules, Formulas, and Fixes)
Advertisement
Conditional formatting in Google Sheets turns a wall of numbers into something your eyes can parse in seconds — overdue dates glow red, top performers sit in green, duplicates get flagged before they cause trouble. The feature itself is two clicks away, but the real power hides in custom formulas and rule ordering, which is where most people get stuck.
This guide starts with a basic rule, moves through color scales and custom formulas, and ends with the fixes for the problems that appear once you stack multiple rules on the same range.
Your First Rule: Highlight Cells Above a Threshold
Picture a sales sheet: revenue values in column F, rows 2 through 11. You want every value above 400 to stand out.
- Select the range F2:F11 — exclude the header row.
- Go to Format → Conditional formatting. The rules panel opens on the right.
- Under Format rules, open the dropdown (it defaults to "Is not empty") and choose Greater than.
- Type 400 in the value field.
- Pick a fill color from the formatting style section below.
- Click Done.
Three cells turn green. Everything else stays white. The rule re-evaluates automatically whenever a value changes — type 500 into an empty cell and it highlights instantly.
Built-in Rule Types
The dropdown under “Format cells if” covers most everyday needs:
| Rule type | What it checks | Typical use |
|---|---|---|
| Greater than / Less than | Numeric comparison against a threshold | Flag high or low values |
| Is between / Is not between | Value falls inside or outside a range | Highlight a specific band |
| Text contains / starts with / ends with | Partial text match | Find specific names or codes |
| Text is exactly | Full cell value match | Highlight exact statuses like “Done” |
| Date is | Specific date conditions (before, after, in the past week) | Track deadlines or recency |
| Is empty / Is not empty | Whether the cell has content | Flag missing data |
| Custom formula is | Any formula returning TRUE/FALSE | Everything else (see below) |
Color Scales: Gradients Instead of Pass/Fail
A single-color rule is binary — a cell either matches or it doesn’t. A color scale paints every cell a different shade based on where its value falls between the minimum and maximum in the range.
- Select a numeric range.
- Open Format → Conditional formatting.
- In the rules panel, switch to the Color scale tab.
- Choose a preset gradient or set custom min/mid/max colors.
- Click Done.
Two-color scales go from light to dark. Three-color scales add a midpoint — red at the bottom, yellow in the middle, green at the top — useful when you want to distinguish “bad,” “neutral,” and “good” at a glance.
One limitation: color scales only work with numbers. Text cells in the range get no color at all.
Custom Formulas: The Real Power
When the built-in dropdown doesn’t cover your case, choose Custom formula is and write any expression that returns TRUE or FALSE. The formula is evaluated relative to the first cell in your “Apply to range” — if the range starts at A2, write the formula as though you’re in A2.
Highlight entire rows based on one column
Apply this to A2:G11 to highlight every column in rows where column C says “Whitehall”:
=$C2=“Whitehall”
The dollar sign locks the column to C; the row number stays relative so it shifts down for each row. The result: the whole row lights up, not just column C.
Flag duplicate values
Apply this to B2:B11 to flag any value that appears more than once:
=COUNTIF($B$2:$B$11, B2)>1
COUNTIF counts how many times the current cell’s value exists in the full range. More than one means it’s a duplicate. This is the fastest visual check before you run the actual cleanup — our guide on removing duplicates in Google Sheets covers three ways to delete them once you’ve confirmed the mess.
Highlight dates older than a threshold
Apply to a date column to flag anything more than 30 days old:
=A2<TODAY()-30
Because TODAY() recalculates every time the sheet opens, the rule stays current without any edits.
Managing Multiple Rules: Priority Matters
Stack two or more rules on the same range and Google Sheets evaluates them top to bottom in the panel. The first matching rule wins; later rules for that cell are ignored.
This creates a classic trap: a broad rule listed above a specific one swallows it. If “Greater than 300” (yellow) sits above “Greater than 500” (green), a cell worth 570 matches both but turns yellow because that rule fires first.
The fix: drag the more specific rule above the broader one. As a principle, put tight conditions at the top and catch-all conditions at the bottom.
To reorder, open Format → Conditional formatting, then drag rules by the grip handle on the left side of each rule card.
Editing and deleting rules
- Edit: click the rule in the panel, change the condition or style, click Done.
- Delete one: click the trash icon next to the rule.
- Delete all: scroll to the bottom of the panel and click “Remove all rules.”
Deleting a rule removes the formatting immediately but never touches the cell values underneath. If a cell looked green only because of a rule, it returns to its default appearance the moment you delete that rule.
Common Problems and Fixes
| Symptom | Likely cause | Fix |
|---|---|---|
| Rule exists but nothing highlights | ”Apply to range” doesn’t cover the cells you expect | Edit the range field at the top of the panel |
| Wrong color appears | A higher-priority rule matches first | Reorder rules — drag the intended rule up |
| Custom formula does nothing | Formula references the wrong starting cell | Make sure the formula’s relative reference matches the first cell in the range |
| Color scale shows gaps | Range contains text or blank cells mixed with numbers | Narrow the range to numeric cells only |
| Formatting disappears after a VLOOKUP updates values | The lookup overwrote the cell with a new value that no longer meets the rule’s condition | This is correct behavior — the rule re-evaluates against the new value |
That last row surprises people: conditional formatting is purely visual and recalculates live. If a VLOOKUP pulls in a new number that falls below your threshold, the highlight vanishes. Nothing is broken — the rule is doing its job against fresh data.
Practical Patterns Worth Stealing
- Deadline tracker. Apply =B2<TODAY()+7 to a due-date column to flag anything due within the next week in amber, and =B2<TODAY() in red for overdue items.
- Status dashboard. Text-is-exactly rules for “Done” (green), “In progress” (yellow), “Blocked” (red) turn a plain task list into a visual board.
- Error spotting. Apply =ISERROR(A2) across a formula column to highlight any cell throwing #N/A, #REF!, or #VALUE! — pair this with our #REF! error fix guide to resolve the underlying cause.
Frequently Asked Questions
Does conditional formatting work on mobile?
The Google Sheets mobile app displays conditional formatting applied on desktop, but you can’t create or edit rules from the phone or tablet app. All rule management happens in the web or desktop version.
How many rules can I apply to one range?
There’s no hard cap, but performance degrades on very large sheets with dozens of overlapping rules. If a sheet feels sluggish, consolidate rules where possible — one custom formula often replaces three or four built-in rules.
Can I copy conditional formatting to another sheet?
Not directly. You can copy the cells (which carries the formatting), but the rules themselves don’t transfer as editable rules. The fastest workaround is to recreate the rules on the target sheet, or use the Google Sheets API if you need to automate it across many tabs.
Why does my custom formula highlight the wrong cells?
The formula is evaluated relative to the top-left cell of the “Apply to range.” If your range starts at A2 but you wrote the formula referencing A1, every evaluation shifts up one row. Adjust the reference so it matches the first cell in the range.
Sources: