luvv to helpDiscover the Best Free Online Tools

Spreadsheet Proficiency

Learn Spreadsheet Proficiency for Marketing Analyst for free: roadmap, examples, subskills, and a skill exam.

Published: December 22, 2025 | Updated: December 22, 2025

Table of Contents

Why this matters for Marketing Analysts

Spreadsheets are your fastest way to turn messy marketing data into answers. You’ll clean UTM tags, build budget trackers, create pivot tables for channel performance, and deliver clear campaign reports—without waiting on engineering. Solid spreadsheet skills help you validate ideas quickly, communicate results, and automate recurring work.

Who this is for

  • Aspiring or junior Marketing Analysts
  • Marketers who need data-driven reporting without heavy tooling
  • Analysts who want faster iteration before moving to BI tools

Prerequisites

  • Basic math and familiarity with marketing metrics (impressions, clicks, spend, conversions, CPA/ROAS)
  • Comfort navigating Excel or Google Sheets (open files, enter formulas)

Learning path

  1. Clean and prepare campaign data (fix casing, split UTMs, normalize dates)
  2. Lookups to connect data (budgets, targets, channels)
  3. Pivot tables and summaries (CPA, CTR, ROAS by channel/campaign)
  4. Budget tracking and forecasting basics
  5. Reporting templates and conditional formatting for monitoring
  6. Sharing and version control so teams can collaborate safely

Practical roadmap

Step 1 — Setup & shortcuts (1–2 hours)
  • Create sheets: Raw_Data, Clean_Data, Lookup_Tables, Pivot, Report
  • Learn quick wins: fill down, freeze headers, filter views, named ranges
  • Mini task: Turn a CSV of campaign data into a filterable table with formatted dates
Step 2 — Cleaning essentials (2–3 hours)
  • TRIM, CLEAN, PROPER/LOWER, SUBSTITUTE, SPLIT
  • Convert text to dates, handle blanks and errors (IFERROR)
  • Mini task: Standardize UTMs and split into source/medium/campaign
Step 3 — Lookups that never break (2 hours)
  • XLOOKUP or INDEX+MATCH with exact and approximate matches
  • Safe defaults with IFERROR and default values
  • Mini task: Map each campaign to its budget and target CPA from a master table
Step 4 — Pivots for marketing metrics (2–3 hours)
  • Summarize by channel, campaign, week
  • Calculated fields for CPA, CTR, ROAS
  • Mini task: Build a weekly performance pivot with a date slicer or helper week column
Step 5 — Reporting & monitoring (2 hours)
  • Conditional formatting for thresholds (CPA vs target, spend pacing)
  • Reusable report template with key KPIs
  • Mini task: Create an executive summary tab with auto-updating highlights
Step 6 — Sharing & version control (45–60 minutes)
  • Protect formulas, separate inputs from outputs
  • Change log and version naming (YYYY-MM-DD)
  • Mini task: Add a Readme tab with instructions and assumptions

Worked examples (copy/paste ready)

1) CPA by Channel with a Pivot

Data columns (Clean_Data): Date, Channel, Campaign, Spend, Clicks, Conversions, Revenue

  1. Insert pivot from Clean_Data
  2. Rows: Channel
  3. Values: Sum of Spend, Sum of Conversions
  4. Calculated field: CPA = Spend/Conversions
  5. Format CPA as currency; sort by CPA ascending

Tip: If Conversions can be zero, add a safe calculated field: IF(Conversions=0,0,Spend/Conversions).

2) XLOOKUP to attach budgets

Goal: Bring monthly budget from Budget tab (A: Campaign_ID, B: Monthly_Budget) to Clean_Data!H2.

=IFERROR(XLOOKUP(Clean_Data!C2, Budget!A:A, Budget!B:B, "Not found", 0), "Not found")

Drag down. Set number format to currency.

3) INDEX+MATCH for partial campaign names

When IDs aren’t consistent but names contain a keyword, return a target CPA from Lookup_Tables.

=IFERROR(INDEX(Lookup_Tables!B:B, MATCH("*" & Clean_Data!C2 & "*", Lookup_Tables!A:A, 0)), "No match")

This finds the first row where column A contains the campaign text.

4) Clean and split UTMs

Standardize campaign names and split utm parameters (URL in A2).

Standardize: =LOWER(SUBSTITUTE(TRIM(A2), " ", "-"))
Source (Sheets): =REGEXEXTRACT(A2, "utm_source=([^&]+)")
Medium (Sheets): =REGEXEXTRACT(A2, "utm_medium=([^&]+)")
Campaign (Sheets): =REGEXEXTRACT(A2, "utm_campaign=([^&]+)")
5) Weekly cohorts and reporting dates

Compute week number and week start (date in B2).

WeekNum (Mon-based): =WEEKNUM(B2, 2)
WeekStart (Mon): =B2 - WEEKDAY(B2, 2) + 1

Use WeekStart in pivots for stable weekly aggregation.

6) Conditional formatting for CPA targets

Range A2:H100. Highlight rows where CPA (F) > Target (G).

Custom formula: =$F2 > $G2

Choose a light red fill to flag issues.

Drills / exercises

Common mistakes and debugging tips

1) Mixed text and date formats

Fix: Use DATEVALUE on text dates and set explicit date format. Check for leading/trailing spaces with TRIM.

2) Lookups failing due to extra spaces or case

Fix: Wrap lookup keys in TRIM and LOWER on both sides. Example: XLOOKUP(LOWER(TRIM(A2)), LOWER(TRIM(Budget!A:A)), Budget!B:B)

3) Dividing by zero in calculated fields

Fix: Use IF(Conversions=0, 0, Spend/Conversions) to avoid errors and keep pivots stable.

4) Incorrect conditional formatting range

Fix: Apply rules to the full table range and lock columns with $ in formulas (e.g., =$F2>$G2).

5) Overwriting formulas during updates

Fix: Separate inputs and outputs on different tabs, protect formula columns, and use Paste Values when importing.

Mini project: Campaign Performance Dashboard

  1. Data prep
    • Import last 3 months of campaign data into Raw_Data
    • Create Clean_Data with standardized columns and UTMs split
  2. Enrich
    • Lookup Monthly Budget and Target CPA
    • Create WeekStart and Channel Group (e.g., Paid Social, Search)
  3. Summarize
    • Pivot by Channel Group and WeekStart with Spend, Conversions, CPA, ROAS
    • Add calculated fields and sorting
  4. Report
    • Build a clean Report tab with key KPIs, trends, and highlights
    • Conditional formatting: CPA vs Target, Spend pacing
  5. Share
    • Add a Readme tab (purpose, instructions, change log)
    • Protect pivot and formula ranges; allow inputs only where needed
Deliverables checklist
  • Clean_Data tab with no errors (#N/A/#DIV/0!)
  • Pivot with weekly Channel Group results
  • Report tab with three highlights: Best channel by ROAS, CPA outliers, Budget remaining

Practical projects (expand as you learn)

  • Budget Pacing Model: Track planned vs actual spend and forecast end-of-month
  • UTM Auditor: Scan URL lists, flag malformed UTMs, auto-fix casing
  • Executive Snapshot: One-pager with weekly KPIs and a short narrative

Subskills

  • Pivot Tables For Marketing Data — Summarize performance by channel, campaign, and week with calculated fields
  • Lookups Xlookup Index Match — Connect budgets, targets, and mappings reliably
  • Cleaning Data In Sheets — Standardize dates, casing, and UTMs; remove noise
  • Budget Tracking Models — Monitor pacing, remaining budget, and simple forecasts
  • Campaign Reporting Templates — Reusable layouts for weekly/monthly updates
  • Date And Text Functions For UTMs — Extract, split, and normalize URL parameters
  • Conditional Formatting For Monitoring — Visual alerts for thresholds and anomalies
  • Sharing And Version Control Basics — Protect formulas, document changes, and share safely

Next steps

  • Automate repetitive imports with consistent column headers and a paste-values routine
  • Standardize a monthly reporting template your team can reuse
  • When ready, complement spreadsheets with SQL and a BI tool for scale

Topics

8 topics to cover

1

Cleaning Data In Sheets

2

Budget Tracking Models

3

Campaign Reporting Templates

4

Date And Text Functions For UTMs

5

Conditional Formatting For Monitoring

6

Pivot Tables For Marketing Data

7

Lookups Xlookup Index Match

8

Sharing And Version Control Basics

Back to Marketing Analyst

Have questions about Spreadsheet Proficiency?

AI Assistant

Ask questions about this tool