Google Sheet Conditional Formatting Generator
Highlight important values, risks, dates, duplicates, or entire rows with rules that stay attached to your data. Build them manually—or ask SheetsX to create and verify several exact rules in one prompt.
Reviewed by the SheetsX team
How to add conditional formatting in Google Sheets
Select the cells, choose Format → Conditional formatting, pick a condition under Format cells if, choose the formatting style, and click Done. Use a single-color rule for thresholds or text, a color scale for a heat map, or Custom formula is when the style depends on another cell.
Watch SheetsX generate two conditional formatting rules
In a real Google Sheet, the Agent highlights Leads above 100 in green and Conversion Rate below 7% in red, then verifies the exact C2:C7 and D2:D7 ranges.
“Using the Google Sheets API spreadsheets.batchUpdate directly, add exactly two addConditionalFormatRule requests to Sheet1 (sheetId 0). Rule 1: range C2:C7, NUMBER_GREATER than 100, fill RGB 0.55/0.85/0.55. Rule 2: range D2:D7, NUMBER_LESS than 0.07, fill RGB 0.95/0.55/0.55. Keep all values, headers, number formats, and other cells unchanged. Do not create a new sheet. Verify the resulting rules and confirm the exact ranges and conditions.”
Create a single-color rule
Start with the exact applied range. A correct condition attached to the wrong range is the most common reason a rule appears broken.
Google's official conditional formatting instructions- 1
Select the applied range
Choose only the cells that should change appearance, such as D2:D100 or A2:E100.
- 2
Open the rules panel
Choose Format → Conditional formatting and confirm the Apply to range box.
- 3
Set the trigger
Choose Greater than, Less than, Text contains, Date is, Is empty, or Custom formula is.
- 4
Choose a visible style
Set the fill or text color, click Done, and test one matching and one nonmatching value.
Highlight a row based on another cell
Apply the rule to the full row range, write the formula as if it were evaluating the first row, and lock only the condition column. For A2:E100, the formula =$E2="At Risk" keeps column E fixed while the row number advances.
Highlight duplicates in A2:A100
=COUNTIF($A$2:$A$100,A2)>1Highlight overdue open rows
=AND($B2<TODAY(),$E2<>"Done")Single color, color scale, custom formula, or SheetsX?
| Method | What it does | Best use |
|---|---|---|
| Single-color rule | Applies one text, fill, or border style when a condition is true. | Thresholds, statuses, blanks, dates, and exact text matches. |
| Color scale | Maps low, midpoint, and high values to a color gradient. | Scores, revenue, conversion rates, and heat maps. |
| Custom formula | Evaluates a formula for each cell or row in the applied range. | Duplicates, overdue rows, cross-column rules, and complex logic. |
| SheetsX Agent | Builds multiple rules for named ranges from one precise prompt. | Repeatable multi-rule formatting with explicit safeguards. |
Why a rule formats the wrong cells
Range starts on the wrong row
Make the formula's first row match the first row in Apply to range.
Reference is over-locked
Use $E2 to lock a column, not $E$2, when each row needs its own test.
Percent entered as a whole number
A displayed 7% is stored as 0.07, so compare against 0.07.
Rules compete
Google Sheets evaluates rules in list order; drag them to change priority.
Describe every rule and safeguard in one request
Name the sheet and ranges, define each condition and color, state whether rules should be inserted or replaced, and protect the values and number formats. Then ask the Agent to verify what it created.
Install SheetsX- Open a new Agent chat for a clean task context.
- Name the sheet and exact A1 ranges.
- Specify every operator, threshold, and fill color.
- Protect values, headers, formulas, and number formats.
- Ask the Agent to verify the resulting ranges and conditions.
Google Sheets conditional formatting questions
How do I add conditional formatting in Google Sheets?+
Select the cells, choose Format → Conditional formatting, select a condition under Format cells if, choose the formatting style, and click Done. Use Add another rule when the same table needs more than one condition.
How do I highlight an entire row based on one cell?+
Apply the rule to the full row range and choose Custom formula is. Lock the condition column with a dollar sign, such as =$E2="At Risk", while leaving the row number relative so Google Sheets evaluates each row.
How do I highlight duplicates in Google Sheets?+
Apply a custom formula such as =COUNTIF($A$2:$A$100,A2)>1 to A2:A100. The fixed range counts every value while the relative A2 changes for each row.
Can Google Sheets conditional formatting use another cell?+
Yes. A custom formula can format one range based on another cell in the same sheet. For example, apply =$D2<0.07 to A2:E100 to highlight each row whose conversion rate in column D is below 7%.
Why is my conditional formatting applied to the wrong row?+
The formula usually starts on a different row than the applied range, or the wrong reference is locked. Write the formula for the first row in the range and use dollar signs only where the column or row must remain fixed.
Can SheetsX create multiple conditional formatting rules at once?+
Yes. State the sheet, exact ranges, conditions, thresholds, colors, and what must remain unchanged. SheetsX can add the rules and then verify their ranges and conditions, as shown in the recorded demo.