How to use conditional formatting in Google Sheets
Learn to highlight cells, rows, or columns automatically in Google Sheets using conditional formatting rules, color scales, and custom formulas.
Quick answer
Select the cells you want to format, click Format, then Conditional formatting to open the rules panel. Choose 'Single color' or 'Color scale,' set the condition or value range that should trigger formatting, pick a text or background style, and click Done. Rules apply instantly and update automatically as your data changes.
Steps at a glance
- Select the cell range you want to format
- Open Format then Conditional formatting from the menu
- Choose Single color or Color scale as the rule type
- Set the condition that should trigger formatting
- Pick a text color or background style
- Click Done to apply the rule
- Add another rule or edit existing ones as needed
Summary
Conditional formatting in Google Sheets automatically changes a cell's text or background color when it meets a condition you set, like containing a specific word or exceeding a number. It helps important data stand out instantly without manual color-coding, and updates automatically as values change.
Step-by-step guide
Step 1
Open your spreadsheet and select a range
Open the spreadsheet in Google Sheets and select the cells, column, or row you want to format. You can select a single column, a full table, or a specific range like B2:B50 depending on what data you're highlighting.
Step 2
Open the Conditional Formatting menu
Click Format in the top menu, then choose Conditional formatting. A panel opens on the right side of the screen showing the rule editor for the range you selected.
Step 3
Choose a single-color rule
Under 'Format cells if,' pick a condition such as 'Text contains,' 'Greater than,' or 'Is not empty,' then enter the value that should trigger the rule. Under 'Formatting style,' choose the background color or text style to apply when the condition is met.
Step 4
Or apply a color scale instead
Switch to the Color scale tab if you want a gradient effect based on value size, such as shading low numbers red and high numbers green. Choose a minimum, maximum, and optional midpoint value, using the dropdown arrows to set whether each is a number, percentage, or percentile.
Step 5
Use a custom formula for advanced rules
Select 'Custom formula is' under the condition dropdown to format cells based on another cell's value, such as highlighting a row when a status column says 'Overdue.' Reference the first row of your range with a relative cell reference so the rule applies correctly down the column.
Step 6
Click Done to save the rule
Click Done at the bottom of the panel to apply the rule to your selected range. The formatting appears immediately, and you can return to Format > Conditional formatting anytime to edit, duplicate, or delete rules.
Step 7
Add more rules or reorder priority
Click Add another rule in the panel to layer multiple conditions on the same range, such as combining a color scale with a text-based rule. Rules are applied in the order listed, so drag them to reorder priority if two conditions could both match the same cell.
Why this matters
You're tracking a budget, project status, or inventory list and need overdue tasks, negative balances, or low stock to jump out visually. Setting up conditional formatting once means you never have to manually recolor cells again as new data comes in or existing values change.
Frequently asked questions
Can I apply more than one conditional formatting rule to the same range?
Yes. Click Add another rule in the Conditional format rules panel to stack multiple conditions on the same cells. Rules are evaluated in list order, so reorder them if you need one condition to take priority over another.
Can conditional formatting reference a cell on a different sheet?
Yes, using a custom formula you can reference another tab, such as =A2=Sheet2!A2. This is useful when comparing values across sheets before applying formatting to your main table.
Does conditional formatting still work after sorting or filtering data?
Yes, formatting rules are tied to the condition, not the row position, so they recalculate automatically when you sort or filter. This makes it easy to combine conditional formatting with a filter view to focus on flagged results.
Will conditional formatting carry over if I download the sheet as Excel?
Most basic rules like single-color and color-scale formatting transfer when you download as Excel, though some advanced custom formula rules may need to be rechecked after export.
Create interactive demos like this one — free, no coding required.
Start for free →