Excel Skills for Marketing Jobs

The Excel skills for marketing roles are mostly about turning exports into answers: campaign metrics such as CTR and ROAS, totals by channel with SUMIFS, lead counts with COUNTIF, week-on-week percent change and pivot table reports. This guide is for marketing assistants, coordinators, digital marketers and anyone preparing for a marketing interview with an Excel task. Every formula below works on data you could type in yourself.

How marketers use Excel#

Marketing tools all export to spreadsheets, so Excel is where data from different places comes together. Typical tasks:

• Campaign reports: spend, clicks, conversions and revenue by channel or campaign.
• Lead tracking: counting leads by source, status or region.
• Budget pacing: spend so far against the monthly budget.
• Week-on-week or month-on-month reports: what went up, what went down and by how much.
• List cleaning: fixing names, emails and campaign labels before an import.

The formulas are rarely complicated. What matters is getting the right numbers into the right ratio, and keeping labels consistent so totals add up.

Campaign metric formulas: CTR, CPC, CPA and ROAS#

Most marketing metrics are one number divided by another. Here is one campaign row:

Format rates such as CTR as percentages with Ctrl+Shift+% (Mac: Control+Shift+%) rather than multiplying by 100. When a campaign has zero clicks or conversions, the division returns #DIV/0!. Wrap it to show a blank instead: =IFERROR(D2/E2,"").

Total spend and revenue by channel with SUMIFS#

SUMIFS adds the numbers that match one or more conditions, such as a channel, a week or both. With channels in column B, spend in C and revenue in D:

=SUMIFS(D2:D7,B2:B7,"Paid search")

This totals the revenue for Paid search. Add another pair to narrow it further, for example =SUMIFS(D2:D7,B2:B7,"Paid search",A2:A7,"W2") for week 2 only.

For a channel's overall ROAS or CPA, divide the totals:

=SUMIFS(D2:D7,B2:B7,"Paid search")/SUMIFS(C2:C7,B2:B7,"Paid search")

Don't average each row's ROAS. A small campaign with an unusually high ROAS would count as much as a large one, and the result would no longer match total revenue divided by total spend. With the data in the exercise on this page, Paid search spent 1,250 and returned 3,450, a ROAS of 2.76. The SUMIFS function guide explains each argument.

Count leads by source with COUNTIF#

COUNTIF counts the cells that match one condition, which makes it the quickest way to count leads, sign-ups or responses. With lead sources in column C:

=COUNTIF(C2:C500,"Webinar")

Real exports are often inconsistent: "Google Ads", "google ads" and "Google - Search". COUNTIF ignores capitals, and wildcards catch the variations: =COUNTIF(C2:C500,"*google*") counts every source that contains "google" anywhere. For two conditions, such as source and month, use COUNTIFS. The COUNTIF function guide and the article on Excel wildcards in criteria have more examples.

Week-on-week and month-on-month change#

Percent change tells you how much a metric grew or fell. The formula is new minus old, divided by old:

=(C2-B2)/B2

If leads went from 320 last week (B2) to 368 this week (C2), the result is 15%. Always divide by the old value. A fall shows as a negative percentage, which is what you want. The percent change formula guide covers growing a number by a percentage and the common mistakes.

Pivot tables for channel and campaign reports#

A pivot table summarizes a long export by dragging fields, with no formulas. Click inside the data, choose Insert > PivotTable, then:

• Put Channel in Rows and Spend and Revenue in Values for a channel summary.
• Add Month to Columns for a channel-by-month grid.
• Change a value field from Sum to Count to count leads instead of adding them.

Don't average a ROAS column in a pivot, for the same reason as above. Total spend and revenue in the pivot, then work out ROAS from the totals beside it. Refresh the pivot (Data > Refresh All) when you paste in new data.

Clean campaign names and lists#

Totals only add up when labels are consistent. "Spring Sale", "spring sale " and "Spring-Sale" are three different campaigns to SUMIFS and to a pivot table. Clean them before you report:

• =TRIM(A2) removes extra spaces.
• =PROPER(A2) or =LOWER(A2) makes capitals consistent.
• =SUBSTITUTE(A2,"-"," ") swaps one separator for another.

Then paste the cleaned values over the originals. Our guide to cleaning messy data walks through the full process.

Excel tasks in marketing interviews#

Marketing interviews that include Excel usually give you a campaign export and ask for a short analysis: which channel has the best ROAS, how spend changed month on month, and what you would do next. That last part matters: say what the numbers mean, not only what they are. Our guide on handling an Excel case study in an interview covers how to structure the answer.

If you're weighing up roles, the Excel skills for sales and operations overlap strongly with marketing, the skills for finance jobs add more rigour around budgets, and the skills for supply chain roles show the same formulas applied to stock and orders.

Practise it#

Practise the three skills marketing reports rely on: percentages and % change, counting by a condition with COUNTIF, and summarizing by channel and month in Pivot tables 1. The exercise on this page calculates a channel's ROAS the right way, from totals.