How to Configure Conditional Formatting with Custom Formulas in Google Sheets

Standard conditional formatting in Google Sheets handles basic comparisons like highlighting cells greater than a threshold or containing text keywords. However, modern collaborative reporting requires dynamic visual signaling: highlighting entire rows based on task status, flagging upcoming project deadlines within rolling 7-day windows, or comparing line items against dynamic cross-sheet benchmarks. Custom formulas provide full formula flexibility to format spreadsheet grids dynamically.

Understanding Absolute and Relative References in Formatting Rules

The single most critical concept in custom formula conditional formatting is coordinate anchoring using the dollar sign ($):

• Row-Wide Highlighting ($A2="Complete"): Anchoring the column letter with $ tells Google Sheets to evaluate column A for every cell in row 2, applying formatting uniformly across columns A through Z.
• Cell-Specific Highlighting (A2>100): Leaving both column and row relative evaluates each cell independently against its own coordinate.
• Global Threshold Reference (A2>$K$1): Absolute anchoring on both row and column evaluates each cell against a static target benchmark located in cell K1.

Pattern 1: Highlighting an Entire Row Based on Dropdown Status

To shade an entire task record green when status in column C is marked “Approved”:

1. Highlight your table data range excluding headers, for example: A2:G150.
2. Click Format in the top menu and select Conditional formatting.
3. Under the Format cells if dropdown, scroll to the bottom and select Custom formula is.
4. Enter the formula:

   =$C2="Approved"

5. Select your fill color (soft pastel green) and click Done.

Whenever an editor updates column C to “Approved”, the entire record row highlights instantly.

Pattern 2: Flagging Rolling Deadlines and Expiring Milestones

Highlight tasks where the due date in column D is within the next 7 days and has not been marked “Closed”:

=AND($D2>=TODAY(), $D2<=(TODAY()+7), $C2<>"Closed")

The dynamic function TODAY() recalculates each day automatically without requiring manual date maintenance.

Pattern 3: Alternating Row Colors with Custom Banding

While Sheets features basic alternating colors, custom formulas allow zebra striping that automatically respects filtered records or specific groupings:

=ISEVEN(ROW())

Combine with logic to band rows only when data exists:

=AND(ISEVEN(ROW()), NOT(ISBLANK($A2)))

Rule Priority and Troubleshooting Conflicts

In the Conditional format rules sidebar, rules are evaluated from top to bottom. If multiple rules evaluate to TRUE for the same cell, the topmost rule takes precedence. Drag rules up or down using the handle on the left edge of each rule card to reorder execution priority.