How It Works

Conditional formatting applies colors or styles to cells based on their values. Custom formulas give you precise control over when formatting applies.

Basic Setup

Select the range to format
Format → Conditional formatting
Choose “Custom formula is”
Enter your formula
Set the formatting style

Key Rule: The formula should return TRUE or FALSE. TRUE applies the formatting.

Example

Highlight Overspending:

Budget in column B, Actual in column C. Highlight C when over budget:

Formula: =C2>B2

Apply red background - any row where actual exceeds budget turns red.

Common Scenarios

Overspending Alert (Red)

=Actual > Budget
=C2 > B2

Highlights when you’ve spent more than planned.

Under Budget (Green)

=Actual < Budget * 0.9
=C2 < B2 * 0.9

Highlights when spending is under 90% of budget.

Approaching Limit (Yellow)

=AND(C2 >= B2*0.8, C2 <= B2)

Highlights when between 80-100% of budget.

Negative Values (Red Text)

=A2 < 0

Format negative numbers in red.

Due Date Passed

=A2 < TODAY()

Highlights dates in the past.

Due Within 7 Days

=AND(A2 >= TODAY(), A2 <= TODAY()+7)

Highlights upcoming due dates.

Budget Dashboard Formulas

Color Scale for Percentage Used

For a column showing % of budget used:

0-50%: Green =A2<=0.5
51-80%: Yellow =AND(A2>0.5, A2<=0.8)
81-100%: Orange =AND(A2>0.8, A2<=1)
Over 100%: Red =A2>1

Savings Goal Progress

=A2 >= 1  (100% or more of goal - green)
=A2 >= 0.75  (75%+ - light green)
=A2 < 0.25  (under 25% - needs attention)

Income vs. Expenses

=Income > Expenses  (surplus - green)
=Income < Expenses  (deficit - red)

Pro Tips

Reference the first row in your formula (e.g., =C2>B2 not =C:C>B:B)
Don’t use $ on row numbers when you want the rule to apply to each row
Use $ on columns if comparing to a fixed column: =$D2>$E2
Order matters - rules are evaluated top to bottom, first match wins
Test with one cell before applying to a range

Visual Budget System

Create a traffic light system:

Formula	Color	Meaning
`=C2/B2 <= 0.5`	Green	Well under budget
`=AND(C2/B2 > 0.5, C2/B2 <= 0.9)`	Yellow	On track
`=AND(C2/B2 > 0.9, C2/B2 <= 1)`	Orange	Approaching limit
`=C2/B2 > 1`	Red	Over budget

Common Mistakes

Using the wrong row reference (should match first row of selection)
Forgetting to adjust cell references for relative formatting
Overlapping rules with unexpected priority