How to Use Conditional Formatting in Google Sheets (2026)
Conditional formatting changes a cell’s color or style automatically when it meets a condition — so overdue dates turn red, top numbers turn green, and duplicates jump out, all without manual highlighting. Here’s how to set it up.
1. Apply a Basic Rule
- Select the cells you want to format.
- Go to Format > Conditional formatting.
- In the panel on the right, under Format rules, open the dropdown and choose a condition:
- Is empty / Is not empty
- Text contains / Text is exactly
- Greater than / Less than / Is between (for numbers)
- Date is / Date is before / Date is after
- Enter the value to compare against.
- Under Formatting style, pick a fill color, text color, bold, etc.
- Click Done.
Cells that meet the condition update instantly.
2. Use a Color Scale (Heat Map)
To shade cells along a gradient based on their value:
- Select your range and open Format > Conditional formatting.
- Click the Color scale tab at the top of the panel.
- Choose a preset, or set custom colors for Min, Midpoint, and Max.
- Click Done.
Higher values get one color, lower values another — useful for spotting patterns at a glance.
3. Format Based on Another Cell (Custom Formula)
This is the most powerful option — format a cell based on a different cell’s value. Example: highlight an entire row when its status column says “Overdue.”
- Select the range to format (e.g.,
A2:E100). - Format > Conditional formatting > Custom formula is.
- Enter a formula that returns TRUE/FALSE, referencing the first row of your selection:
=$D2="Overdue"- Set the formatting style and click Done.
Key detail — the $: Locking the column ($D2) but not the row means the rule checks column D for every row in the range, so the whole row highlights when D says “Overdue.”
More custom-formula examples:
- Highlight values above the average:
=A1>AVERAGE($A$1:$A$100) - Highlight weekends in a date column:
=WEEKDAY($A1,2)>5 - Highlight duplicates:
=COUNTIF($A$1:$A$100,A1)>1
4. Manage, Reorder, and Delete Rules
- Open Format > Conditional formatting to see all rules for the current selection.
- Order matters: rules are applied top to bottom; the first matching rule wins for a given cell. Drag rules to reorder.
- Hover a rule and click the trash icon to delete it.
5. Troubleshooting
The rule isn’t applying
Check the Apply to range at the top of the rule. If it’s wrong, the formatting targets the wrong cells.
Custom formula highlights the wrong rows
Watch your $ anchors. To check a single column across all rows, lock the column only ($D2). A fully locked reference ($D$2) checks just one cell for the whole range.
Whole row won’t highlight
Make sure Apply to range covers all the columns (e.g., A2:E100, not just D2:D100), and that the formula references the first data row (row 2 here).
Related Google Sheets guides: How to remove duplicates · How to use VLOOKUP · How to use the IF function · How to create a pivot table · How to create a drop-down list
Ready to automate your busywork?
Carly schedules, researches, and briefs you—so you can focus on what matters.
Get Carly Today →Or explore our free tools
