Conditional formatting in Google Sheets helps you highlight cells automatically based on their values using formulas. By combining logic and visual rules, you can turn a plain spreadsheet into a clear, at-a-glance dashboard.
Use formulas to build advanced conditions that static rules cannot handle. This guide walks through core concepts, practical examples, and common patterns to make your sheets more responsive and readable.
Key concepts at a glance
| Feature | Description | Example Use | Best For |
|---|---|---|---|
| Simple rule | Highlight based on fixed values | Cell value greater than 100 | Quick alerts |
| Formula condition | Use a custom expression to test logic | =(A2>B2)*1 | Row comparisons |
| Color scales | preset gradients based on min/mid/maxHeatmap style gradients | Performance trends | |
| Custom formula | Any valid Boolean expression | =REGEXMATCH(D2,"urgent") | Pattern matching |
Build a custom formula rule
Start by defining the range and writing a formula that returns TRUE or FALSE. Google Sheets applies the format only when the formula evaluates to TRUE, giving you pixel level control.
Use relative and absolute references carefully. A1 refers to the active cell in the range, while $A$2 anchors to a specific row or column. This keeps your logic aligned with each row or column.
Wrap logic in functions like IF, OR, AND, and REGEX to express complex conditions. For example, =AND(B2>100,C2="complete") highlights rows that meet multiple criteria at once.
Compare rows with formula rules
Conditional formatting shines when you compare columns or rows directly. Instead of static thresholds, formulas let you test relationships between cells.
For side by side comparisons, use a formula like =A2>B2 on the range A2:B10. Cells in column A that exceed their paired B value appear in your chosen color.
You can extend this to groups by applying the rule across entire rows and anchpering key columns. The pattern =$C2="overdue" then colors an entire row based on a status cell.
Track trends with date logic
Date based rules help you visualize deadlines, expirations, and time based performance. Use functions like TODAY(), NOW(), and YEARFRAC to build dynamic windows.
A rule like =B2 Rolling windows and repeating cycles are easy with modulo and INT division. For weekly buckets, use =WEEKNUM(TODAY())=WEEKNUM(A2) to match the current week dynamically. Combine multiple conditions with AND and OR to capture nuanced scenarios. Nest checks such as =OR(AND(A2>0,A2
Use named ranges and structured references to keep formulas readable. Descriptive names like target_sales or threshold make rules easier to maintain.
p>
Limit conditional formats to essential rules to avoid slow recalculation. Use the smallest range necessary, and avoid entire column references like A:A when you can use A2:A1000.Advanced patterns and best practices
Scale and maintain your rules
FAQ
Reader questions
How do I highlight cells when my value is above the row average?
Use a custom formula such as =A2>AVERAGE($A$2:$A$50) applied to the range A2:A50. The formula compares each cell to the full range average and triggers the format only when it is higher.
Can I format an entire row based on one cell value?
Yes, apply the rule to the full row range like A2:Z100 and use a formula such as =$B2="urgent". The $ locks column B so each row checks its own status cell correctly.
Why does my conditional formatting not work on imported data?
Check that the imported values are real numbers or text, not hidden apostrophes or errors. Use functions like VALUE or TRIM when needed, and ensure the formula range matches the data range exactly.
How can I highlight duplicate entries across columns?
Use =COUNTIF($A$2:$A$50, A2)>1 on the range A2:A50. This counts occurrences and colors any value that appears more than once in the specified column.