Formula guide · Excel and Google Sheets · Updated May 13, 2026
Conditional Formatting Not Working
Fix conditional formatting rules that do not apply because of wrong ranges, relative references, or rule priority.
Quick Answer
Conditional formatting usually fails because the formula is written for the wrong first cell or the apply range does not match the rule.
Copyable Formula
=AND($C2="Open",$D2<TODAY())Syntax
Check apply range, formula starting row, absolute references, and rule order.Excel and Google Sheets
Worked Example
| Problem | Fix |
|---|---|
| Wrong row highlighted | Check starting row |
| Only one cell changes | Check apply range |
| No highlight | Check rule returns TRUE |
Result: When the formula matches the first cell in the apply range, the rule applies predictably.
Steps
- Check the exact apply range.
- Write the formula from the perspective of the top-left cell in that range.
- Use dollar signs only where references should stay locked.
- Review rule order if multiple rules overlap.
Common Mistakes
- Writing a rule for row 2 while the apply range starts on row 1.
- Locking row numbers when the rule needs to move down.
- Letting a higher-priority rule override the expected formatting.
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.