Google Sheets Conditional Formatting Formula Builder
Quick answer: To highlight cells with conditional formatting, pick a rule below, set your range's first cell, and paste the generated formula (e.g. =$A2>100) into Format → Conditional formatting → Custom formula is.
The hard part of conditional formatting is the custom formula and its dollar signs: which part locks and which part moves as the rule is applied down the range. This builder writes it correctly for the most-asked cases — highlight a whole row based on one column, flag duplicates, empty cells, overdue dates, or numbers in a band — so you just copy and paste.
Build your conditional formatting formula
Pick the rule, set your range's first cell, and copy the custom formula into Google Sheets → Format → Conditional formatting → Custom formula is.
=$A2>100
Apply to range: A2:A1000
How to apply it (3 steps)
- Select the range you want to format (e.g.
A2:A1000), then open Format → Conditional formatting. - Under Format rules, change the dropdown to “Custom formula is” and paste the formula above.
- Choose a fill colour and click Done. Every cell whose row makes the formula return TRUE gets highlighted.
How the conditional formatting builder works
Google Sheets applies a custom formula rule cell by cell across the range you select. The formula must be written relative to the top-left cell of that range. That is why this builder uses a mixed reference like $A2: the $ locks the column (so every cell in the row is tested against column A), while the row number 2 moves down as the rule walks through the range.
To highlight an entire row by a value in one column, you apply the rule to a wide range such as A2:Z1000 but keep the column locked with the dollar sign — =$C2="Done" colours the whole row whenever column C says Done. To highlight just the matching cell, apply the rule to that single column instead. For duplicates the formula counts the whole column with COUNTIF(A:A, A2)>1; for dates it compares against TODAY() so the colours update automatically each day.
Common ready-to-paste examples
Highlight the whole row when column C says "Done" (apply to A2:Z1000):
=$C2="Done"
Highlight numbers greater than 100:
=$A2>100
Flag duplicate values in column A:
=COUNTIF(A:A, A2)>1
Highlight empty cells:
=ISBLANK($A2)
Flag overdue dates (before today):
=$A2<TODAY()
Highlight amounts between 10 and 20:
=AND($A2>=10, $A2<=20)
FAQ
Why does my conditional formatting only colour one cell instead of the whole row?
Two things must line up: apply the rule to a wide range (e.g. A2:Z1000, not just A2:A1000), and lock the column in the formula with a dollar sign, like =$C2="Done". Without the $ the test drifts to a different column on each row; with it, every cell in the row is tested against column C.
What cell should the custom formula reference?
Always the top-left cell of the range you selected. If you apply the rule to A2:A1000, the formula is written for A2; Google Sheets then shifts it down automatically for the other rows. This builder asks for that first cell so it writes the reference correctly.
How do I highlight duplicates in Google Sheets?
Use =COUNTIF(A:A, A2)>1 as the custom formula and apply it to your data range. It counts how many times each value appears in the column and colours any that appear more than once. Pick the 'duplicate' rule above to generate it for your column.
Can conditional formatting react to dates automatically?
Yes. Formulas that use TODAY() re-evaluate every day, so =$A2<TODAY() keeps overdue rows highlighted without you editing anything. Use the 'overdue', 'today' or 'next 7 days' rules in the builder.