Conditional Formatting For Monitoring

Why this matters

Marketing Analysts monitor live performance: spend pacing, CPA/CAC against targets, CTR drops after creative changes, and duplicate UTMs that skew reports. Conditional formatting turns raw numbers into instant visual alerts so you catch issues early and act faster.

• Spot overspend before budgets blow up.
• Catch conversion rate dips right after landing-page edits.
• Flag mis-tagged links and duplicate UTMs that break attribution.
• Highlight stale campaigns that haven’t been touched recently.

Concept explained simply

Conditional formatting changes a cell’s appearance when a rule is true. Think: “If CPA > Target, paint the cell red.” You can use simple rules (greater than, top/bottom percent) or custom formulas (more flexible, combines multiple conditions).

Mental model

Imagine a clear sheet of glass placed over your data. Rules are colored stickers placed on top when conditions are true. The data doesn’t change—just the overlay. Your job: write precise rules so the right stickers appear at the right time.

Core patterns you’ll use

• Threshold alerts: highlight when metric crosses a limit (e.g., CPA > Target).
• Pacing vs plan: compare cumulative spend to cumulative plan (tolerance like ±10%).
• Sudden change: highlight big day-over-day drops or spikes (e.g., CTR change < -30%).
• Duplicates: surface repeated UTMs or keywords using COUNTIF.
• Staleness: mark items not updated in last N days with TODAY().
• Outliers: flag z-scores beyond ±2 using AVERAGE and STDEV.

Worked examples

Example 1 — CPA alert (Target breach OR too few conversions)

Data columns (row 1 headers): Campaign (A), Spend (B), Conversions (C), CPA (D), Target CPA (E).

1. Compute CPA in D2: =IFERROR(B2/C2,"") and copy down.
2. Select D2:D100 (CPA column).
3. Add a custom formula rule: =OR($D2>$E2,$C2<5)
4. Format fill: light red; bold text.

Meaning: highlight if CPA exceeds target or conversions are too low to trust the CPA.

Example 2 — Spend pacing vs cumulative plan (+10% tolerance)

Data columns: Date (A), Daily Spend (B), Daily Plan (C), Cum Spend (D), Cum Plan (E).

1. D2: =SUM($B$2:B2), E2: =SUM($C$2:C2). Copy down.
2. Select D2:D100 (cumulative spend).
3. Custom formula: =$D2>1.1*$E2
4. Format fill: red for over-pacing. Add a second rule: =$D2<0.9*$E2 (amber) for under-pacing.

Example 3 — CTR day-over-day drop > 30%

Data columns by day: Impressions (B), Clicks (C), CTR (D). D2: =IFERROR(C2/B2,"")

1. E2 (DoD change): =IFERROR((D2-D1)/D1,"") Copy down.
2. Select E2:E100.
3. Custom formula: =$E2<-0.3
4. Format fill: red; add note column with brief cause once investigated.

Example 4 — Duplicate UTM detection

Data columns: UTM_Source (A), UTM_Medium (B), UTM_Campaign (C), Key (D).

1. D2: =A2&"|"&B2&"|"&C2
2. Select D2:D100 and add rule: =COUNTIF($D:$D,$D2)>1
3. Format fill: yellow to investigate duplicates.

Example 5 — Stale creatives (no update in 14+ days)

Data columns: Creative (A), Last_Update (B).

1. Select B2:B100.
2. Custom formula: =TODAY()-$B2>14
3. Format fill: gray; add italic text.

How to apply rules (common steps)

1. Select the range you want to monitor (be precise).
2. Choose Conditional formatting.
3. Pick a rule type: simple comparison or custom formula.
4. Use absolute ($) anchors for columns/rows that must not shift (e.g., $E2).
5. Pick clear colors/icons and write a brief legend in the sheet.
6. Check rule order and stop-if-true (if available) so the most critical rule wins.

Exercises

Do these in any spreadsheet app. Keep sample data small. Recreate exactly; then adapt to your real datasets.

Exercise ex1 — Campaign health rules

• Columns: Campaign (A), Spend (B), Conversions (C), CPA (D), Target CPA (E), Daily Plan (F), Cum Spend (G), Cum Plan (H).
• Fill 7–10 rows with mock data. Compute D=B/C, G=SUM($B$2:B2), H=SUM($F$2:F2).
• Add rules:
  • CPA alert: =OR($D2>$E2,$C2<5)
  • Over-pacing: =$G2>1.1*$H2
  • Under-pacing: =$G2<0.9*$H2
• Expected: some rows red (CPA over or low volume), others amber for under-pacing.

Exercise ex2 — UTM hygiene and staleness

• Columns: Source (A), Medium (B), Campaign (C), Key (D), Sessions (E), Last Seen (F).
• D2: =A2&"|"&B2&"|"&C2
• Rules:
  • Duplicate key: =COUNTIF($D:$D,$D2)>1 (yellow)
  • Stale (30+ days): =TODAY()-$F2>30 (gray)
  • Zero sessions: =$E2=0 (red text)

Common mistakes and self-check

• Wrong range selection: rule applied to headers or extra columns. Self-check: reselect exact data range.
• Missing $ anchors: formulas shift incorrectly. Self-check: preview on a few rows to confirm.
• Conflicting rule order: lower-priority rule hides critical one. Self-check: move critical rules above and enable stop-if-true if available.
• Only colors, no legend: teammates confused. Self-check: add a small legend box on the sheet.
• Not handling zeros/blank: division errors. Self-check: wrap metrics with IFERROR and set minimum volume thresholds.
• Static targets: thresholds not updated monthly. Self-check: reference targets from a dedicated table and use named ranges.

Who this is for

• Marketing Analysts and Growth Marketers monitoring daily performance.
• Anyone building lightweight alerting dashboards in spreadsheets.

Prerequisites

• Basic formulas (SUM, AVERAGE, IF, IFERROR).
• Comfort with relative/absolute references (A2 vs $A$2).
• Know how to sort/filter and freeze headers.

Learning path

1. Basics: simple greater-than/less-than rules on a small dataset.
2. Custom formulas: OR/AND with anchored references.
3. Pacing sheets: cumulative math and tolerance bands.
4. Quality checks: duplicates, zeros, staleness.
5. Advanced: outliers via z-score; rule priority; sheet-wide legends.

Practical projects

• Campaign Health Monitor: one tab per channel, unified color rules (CPA, ROAS, pacing, CTR swings). Deliverable: a weekly review sheet with clear legends.
• Attribution Hygiene Checker: UTM key builder, duplicate highlights, zero-session flags. Deliverable: a shareable QA checklist tab.
• Guardrail Dashboard: daily spend vs plan with ±10% bands and auto-highlight days needing attention. Deliverable: a month-view grid with rollups.

Next steps

• Turn your rules into templates for new campaigns.
• Document the legend and rule logic in a small “READ ME” tab.
• Combine conditional formatting with simple notes/comments for quick triage.
• Review thresholds monthly to reflect new targets and seasonality.

Mini challenge

Create a weekly monitoring sheet that highlights a row when both conditions hold:

• CPC has increased by 50% vs last week’s average for that campaign, and
• Sessions are down 20% vs the 7-day average.

Acceptance criteria: red row highlight only when both are true; amber when just one is true; legend explaining both colors.

Before you take the quick test

The test below is available to everyone. Log in to save your progress and see your history.