How to add conditional formatting in Google Sheets
Conditional formatting automatically colors, bolds, or styles cells based on their values or formulas. It turns a wall of numbers into a visual dashboard where patterns, outliers, and issues jump out immediately. This guide covers 5 methods — basic color rules, color scales, custom formulas, formatting based on another cell, and highlighting duplicates.
⚡ Quick Answer
Select the cells you want to format, go to Format → Conditional formatting. In the sidebar, choose a rule type (Single color or Color scale), set the condition (like Greater than, Text contains, or Custom formula), pick your formatting style, and click Done. Matching cells get formatted immediately.
📋 5 methods in this guide
- Basic single color rules Most common
- Color scales (gradient) Heat maps
- Custom formula rules Advanced
- Format based on another cell Row highlighting
- Highlight duplicates Data cleanup
Basic single color rules
Apply a background color when a cell meets a simple condition — greater than, less than, equal to, text contains, and more. This is the most common conditional formatting pattern.
Select your range
Highlight the cells you want to potentially format. Can be a single cell, a column, or a rectangular range.
Open Conditional Formatting
The conditional format rules panel opens on the right.
Choose a condition
Under Format cells if, pick from options like:
• Is empty / Is not empty — for tracking incomplete data
• Text contains / Text is exactly — for status columns
• Greater than / Less than — for thresholds
• Date is / Date is before — for deadlines
• Between — for ranges
Enter the value in the field that appears.
Pick a formatting style
Choose text color, background color, bold, italic, underline, or strikethrough. Common patterns: red background for "overdue", green background for "complete", yellow for "warning".
Save with Done
Cells matching the condition get formatted instantly. Add more rules by clicking + Add another rule.
Color scales (gradient heat maps)
Color scales apply a gradient across your range — typically red for low values, yellow for middle, green for high (or any color combination). Perfect for heat maps that show relative performance at a glance.
Select your numeric range
Highlight a column or range of numbers. Color scales work best on numeric data with a clear low-to-high spread.
Open Conditional Formatting and switch tabs
Format → Conditional formatting. At the top of the panel, click the Color scale tab (next to Single color).
Pick a preset or customize
Choose from preset color scales (red-yellow-green, white-to-green, etc.) or manually set colors for Min, Midpoint, and Max values. You can also change the values that define each point (default is Min value / 50th percentile / Max value).
Click Done
Cells get a gradient color based on their value's position between min and max. Highest values are most intensely colored on one end; lowest on the other.
Custom formula rules (advanced)
When basic conditions aren't enough, custom formulas unlock the full power of conditional formatting. Any formula that returns TRUE triggers the format.
Select target range and open Conditional Formatting
Format → Conditional formatting.
Choose "Custom formula is"
Under Format cells if, scroll to the bottom and pick Custom formula is. A formula input appears.
Write your formula
Reference the first cell of your range. Common examples:
Highlights cells where the value exceeds 100.
Highlights only when both conditions are true.
Highlights cells where the date is a Sunday.
Choose format and click Done
Any cell where your formula returns TRUE gets the formatting applied.
Format based on another cell (whole row highlighting)
Highlight entire rows based on a value in one column. Perfect for "highlight the whole row when Status = Overdue" scenarios.
Select the entire data range (not just one column)
To highlight full rows, select all columns you want colored. Example: A2:F100 for six columns of data.
Custom formula with absolute column reference
Format → Conditional formatting → Custom formula is. Enter:
The $ locks column D (so every cell in the row checks column D). The 2 without a dollar sign means the row shifts as Sheets evaluates each row.
Pick a background color and click Done
Every row where column D contains "Overdue" gets highlighted end-to-end.
Highlight duplicates
Data cleanup essential — instantly see which values appear more than once.
Select the column to check
Click the column header or highlight a specific range.
Custom COUNTIF formula
Format → Conditional formatting → Custom formula is:
Adjust the range to match your data. Pick a highlight color. Done.
See our full remove duplicates guide for the complete workflow after highlighting.
Common conditional formatting patterns
| Pattern | Formula or condition |
|---|---|
| Highlight blank cells | Is empty |
| Highlight negative numbers | =A2<0 |
| Highlight overdue dates | =A2<TODAY() |
| Highlight top 10% values | =A2>=PERCENTILE($A$2:$A$100, 0.9) |
| Alternating row colors (zebra) | =ISEVEN(ROW()) |
| Weekend dates in a schedule | =WEEKDAY(A2, 2)>5 |
If a cell matches multiple rules, only the first matching rule's format applies. Reorder rules by dragging in the sidebar. Put your most specific rules at the top and general rules below.
Frequently asked questions
How do I add conditional formatting in Google Sheets?
Select the cells you want to format, go to Format menu, click Conditional formatting. In the sidebar, choose a rule type (Single color or Color scale), set the condition (like Greater than, Text contains, or Custom formula), pick your formatting style, and click Done. Matching cells get formatted immediately.
Can I use a formula for conditional formatting in Google Sheets?
Yes. Under Format cells if, choose Custom formula is. Enter a formula that returns TRUE for cells you want formatted. For example, =A2>100 highlights cells where A2 exceeds 100. =AND(A2>0, B2="Active") highlights only when both conditions are true. Custom formulas unlock advanced formatting logic that basic rules can't handle.
How do I highlight cells based on another cell's value?
Use a custom formula referencing the other cell. Select your target range (say A2:A100), then Format → Conditional formatting → Custom formula: =B2="Overdue". This highlights each row in column A where the corresponding column B value equals "Overdue". Use absolute references ($) to lock specific cells in the formula.
What is a color scale in Google Sheets conditional formatting?
A color scale applies gradient colors based on cell values — typically red for low values, yellow for middle, green for high (or any color combination you choose). Perfect for heat maps and quickly spotting outliers. Format → Conditional formatting → Color scale tab. Sheets auto-calculates min, mid, and max points, or you can set them manually.
How do I remove conditional formatting in Google Sheets?
Select the cells with conditional formatting, open Format → Conditional formatting to see all rules applied to the range. Hover over any rule and click the trash icon to delete it. Or click a rule to edit it. To clear ALL conditional formatting from a range, use Format → Clear formatting (though this also clears manual formatting like bold and colors).