Formula guide · Excel and Google Sheets · Updated May 13, 2026
Conditional Formatting Formulas
Use formulas in conditional formatting to highlight rows, duplicates, overdue dates, and matching values.
Quick Answer
Conditional formatting formulas should be written from the perspective of the top-left cell in the selected range.
Copyable Formula
=COUNTIF($A:$A,$A2)>1Syntax
=AND($C2="Open",$D2<TODAY())Excel and Google Sheets
Worked Example
| Goal | Formula |
|---|---|
| Highlight duplicate IDs | =COUNTIF($A:$A,$A2)>1 |
| Highlight overdue open tasks | =AND($C2="Open",$D2<TODAY()) |
Result: Rows or cells are highlighted when the formula returns TRUE.
Steps
- Select the range to format.
- Write the formula for the first row or cell in that range.
- Use dollar signs to lock columns or rows intentionally.
Common Mistakes
- Writing the formula for the wrong starting row.
- Forgetting absolute references when applying a rule across rows.
- Applying too many overlapping rules without checking priority.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.