Overdue highlight
Custom formula =AND($E2<TODAY(),$F2<>"Done") on row 2 applied down column A flags late open tasks.
What is conditional formatting in Google Sheets?
Conditional formatting changes how cells look when they meet rules you define, such as values above a target or dates in the past. It helps scanners spot problems fast without writing values into extra helper columns. Rules can be simple comparisons or custom formulas that reference other sheets.
When to use it
Use it on dashboards, trackers, and QA lists where color draws the eye: overdue dates in red, top performers in green, or duplicate ids highlighted before export.
When to skip it
Skip heavy conditional formatting on huge ranges you scroll constantly; it can slow very large sheets. If you need computed labels in mail merges, put values in cells with formulas instead of color alone.
How it works
-
1
Select the range to format, then Format > Conditional formatting.
-
2
{:"Choose a rule type"=>"greater than, text contains, date is, or custom formula is."}
-
3
{:"Set format style"=>"fill color, bold text, or color scale across the range."}
-
4
For custom formulas, write as if the top-left cell of the range is active, using $ locks when needed.
-
5
Add multiple rules; Google applies them in order listed, first match wins for overlapping rules.
-
6
{:"Test edge cases"=>"blanks, zeros, text that looks like numbers, and rows inserted at the bottom."}
Examples in Google Sheets
Heat map on revenue
Color scale on B2:M2 months shows seasonality without building a separate chart for a standup slide.
Duplicate id check
Custom formula =COUNTIF($A:$A,$A2)>1 highlights repeated keys before you sync to another system.
Build this without starting from a blank cell
Use a Better Sheets tool for conditional formatting, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Forgetting $ on column letters so the rule shifts wrong when applied across many columns.
- Stacking ten rules with no note, then wondering why a cell turned purple.
- Using conditional formatting instead of data validation, so users can still type invalid statuses.
- Applying a rule to entire columns A:A on a 100k row sheet without need, hurting performance.
- Writing formulas that reference the wrong sheet name after a tab rename.
Frequently asked questions
- Can conditional formatting use another tab?
- Yes in custom formulas: ='Settings'!$B$1 or similar. Quote sheet names with spaces.
- Why is my custom formula rule not working?
- Check relative vs absolute references, whether the range starts on the row your formula assumes, and that the formula returns TRUE for cells you expect.
- Can I format entire rows?
- Use a custom formula on row 2 like =$C2="Urgent" and apply across A:Z so the whole row highlights.
- Do rules copy when I paste rows?
- New rows inside the formatted range usually inherit rules. Pasting values from outside may not bring formats.
- Conditional formatting vs filter views?
- Formatting always shows color. Filter views hide rows. They complement each other on busy dashboards.
- Can I use icons?
- Google Sheets supports icon sets in some rule types depending on UI options; color scales and text color are most common.
- How do I clear all rules?
- Select the range, open Conditional formatting, and remove rules in the sidebar one by one or reset the range.
- Does formatting work on IMPORTRANGE data?
- Yes on the receiving sheet, but refresh timing depends on the import, not your rule.
Related Tutorials
Watch how conditional formatting works
Ultimate Checklist in Google Sheets
DarkSide of Google Sheets - Creating Dark Mode and Making it Better!
What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users
The First Thing I Do When Starting a Google Sheet
Study Better With Google Sheets
Automate Google Sheets With Zero Experience
Related blog posts
Guides that explain conditional formatting in more depth.
Upsides and Downsides of Google Sheets Conditional Formatting
Format your data based on certain conditions. Learn how to use Conditional formatting to quickly identify patterns and trends in your data. But there are downsides to Conditional formatting too.
Read post →Getting Started in Google Sheets Spreadsheet Management
Users can also customize the appearance of their spreadsheets with different fonts, colors, and themes.
Read post →The Simplest Bestest Checklist in Google Sheets
Build the ultimately, best, and simplest checklist. Designed well, and FUN to fill out!
Read post →Related glossary terms
dropdowns / data validation
Data validation restricts what people can type in a cell. A dropdown is the friendly face of that feature: pick from a list instead of free text. It keeps statuses, categories, and owners consistent so filters, pivot tables, and scripts do not break on typos.
Read guide →dashboards
A dashboard is a single view that answers "how are we doing?" without hunting across tabs. In Google Sheets, it is usually a summary tab with big numbers, charts, and conditional formatting fed by QUERY, pivot tables, or IMPORTRANGE from raw data you keep elsewhere.
Read guide →KPI tracking
KPI tracking means choosing a small set of numbers that show whether you are winning, then updating them on a rhythm everyone trusts. In Sheets, each KPI usually has a definition, data source, target, and actual cell on a dashboard fed by formulas, not hand-typed guesses.
Read guide →Google Sheets URL Hacks
URL hacks are extra parameters you add to a Google Sheets link to control what opens: a specific tab, a highlighted range, a prefilled form, or an export format. They save clicks when you embed links in docs, SOPs, or buttons. They do not bypass sharing: viewers still need access to the file.
Read guide →Done reading about conditional formatting?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.