Conditional formatting rules
Highlight the rows that matter
Say what should stand out, like late tasks, duplicates or upcoming dates. Get the custom formula with correct $ signs and the exact steps to add the rule.
- Correct $ anchoring
- Click-by-click steps
- Excel and Google Sheets
Your recent formulas
Saved in this browser only.
Ready-made rules
Popular highlight rules
Highlight the whole row by status
Apply to
A2:F1000=$D2="Late"The $ before D locks the column, so every cell in the row checks column D.
Highlight duplicates
Apply to
A2:A1000=COUNTIF($A$2:$A$1000, $A2)>1Counts each value in the fixed range.
Highlight dates in the next 7 days
Apply to
A2:F1000=AND($B2>=TODAY(), $B2<=TODAY()+7)Updates every day by itself.
Zebra stripes
Apply to
A2:F1000=ISEVEN(ROW())Shades every other row for easier reading.
More free spreadsheet tools
Everything you need when a formula gets in the way.
- Formula generatorDescribe it in plain English, get a working formula.Open tool
- Formula explainerPaste any formula and see what each part does.Open tool
- Formula fixerFix #N/A, #VALUE!, #REF! and wrong results.Open tool
- Excel ↔ Sheets converterTranslate formulas between Excel and Google Sheets.Open tool
- Clean my dataTrim, dedupe and tidy a table, privately in your browser.Open tool
- VBA & Apps ScriptMacros for Excel and scripts for Google Sheets.Open tool
- Regex formulasExtract, match and replace text with REGEX functions.Open tool
Frequently asked questions
Why does my conditional formatting formula only work on the first row?
The formula is written for the top-left cell of the range and adjusted for every other cell. Lock the column with $ (like $D2) but not the row, so each row checks its own value.
Where do I paste the formula in Excel?
Select the range, then Home > Conditional Formatting > New Rule > "Use a formula to determine which cells to format". Paste the formula, click Format to pick a fill colour, and click OK.
Where do I paste the formula in Google Sheets?
Select the range, then Format > Conditional formatting. Under Format rules choose "Custom formula is", paste the formula, pick a style and click Done.
Can a rule look at another sheet?
In Google Sheets you need INDIRECT, for example =COUNTIF(INDIRECT("List!A:A"), $A2)>0. Recent Excel versions allow direct references to other sheets.