Cellwise Try it free

Excel for your field

Excel for marketers

For marketers who pull numbers from four ad platforms, an email tool and analytics, and have to turn them into one honest answer by Monday. Excel is where the channels finally sit side by side: CTR, CPC, CPA and ROAS calculated the same way for all of them, rollups by channel, a sanity check on that A/B test, and a warning before the budget runs out on the 27th. Every formula below runs on a month of campaign results you can edit.

Sample data

Every result on this page comes from this sheet, calculated by the same engine the Cellwise app uses. Change a number and watch the formulas react.

ABCDEFGH
1CampaignChannelSpendImpressionsClicksConversionsRevenueTarget CPA
2fall-sale-searchGoogle Ads285048200241096912045
3fall-sale-shoppingGoogle Ads1920615001845716390Monthly budget
4brand-searchGoogle Ads4109800126088792015000
5fall-sale-prospectingMeta Ads22401860002050393120Days elapsed
6retargeting-30dMeta Ads96052400113047446520
7retargeting-7dMeta Ads64028100690312945Days in month
8creator-collabTikTok Ads1500240000192022165031
9fall-sale-pinsPinterest Ads780950001140181530
10newsletter-oct-aEmail609000342211995
11newsletter-oct-bEmail609000459262470

What Excel does for marketing

Click-through rate and cost per click

CTR is clicks divided by impressions: how often people who saw the ad acted on it. CPC is spend divided by clicks. Format CTR as a percentage with two decimals, because the interesting differences are often between 0.8% and 1.2%.

Compare CTR within a channel and ad type, not across them. Search ads answer a query someone typed, so 5% is normal there; a video in a scrolling feed at 0.8% can be perfectly healthy.

=E8/D8

Result: 0.008 shows as 0.80% with Percent format

0.80% CTR for the creator video on TikTok, against 5.00% for the fall-sale search campaign. The video still buys clicks cheaply: $0.78 each, against $1.18 for search.

Cost per acquisition against your target

CPA (cost per acquisition, or cost per conversion) is spend divided by conversions. It is the number to compare with what a customer is worth to you. Put your target CPA in a cell (H2 here) so every check points at it.

Conversion rate (conversions divided by clicks) explains a bad CPA: lots of cheap clicks that do not buy usually means the audience or the landing page is wrong, not the bid.

=C5/F5

Result: 57.435897436 shows as $57.44 with Currency format

$57.44 per conversion for Meta prospecting, above the $45 target. It converts only 1.9% of its 2,050 clicks, against 4.0% for fall-sale search.

Roll up by channel with SUMIFS (and never average ROAS)

ROAS (return on ad spend) is revenue divided by spend. For a channel total, add the revenue and the spend separately with SUMIFS, then divide. Averaging the campaign ROAS figures gives every campaign equal weight, so a small campaign counts as much as a big one.

The same rule applies to CTR, CPC, CPA and conversion rate: always total the top and the bottom, then divide.

=SUMIFS(G2:G11,B2:B11,"Meta Ads")/SUMIFS(C2:C11,B2:B11,"Meta Ads")

Result: 2.7421875 shows as 2.74 with Number format

2.74: Meta brought in $10,530 on $3,840 of spend. Averaging the three campaign ROAS figures would say 3.55, because the two small retargeting campaigns count as much as the $2,240 prospecting one.

List the campaigns that miss your CPA target

FILTER returns the rows that meet a condition and TEXTJOIN turns them into one readable line for a report or a Slack message. Both are in Excel 365 and Excel 2021.

The condition is written as spend greater than target times conversions, which is the same test as CPA above target but without dividing. A campaign with zero conversions would make spend/conversions a #DIV/0! error and break the whole list; this version still works, and correctly flags it.

=TEXTJOIN(", ",TRUE,FILTER(A2:A11,C2:C11>H2*F2:F11))

Result: fall-sale-prospecting, creator-collab

fall-sale-prospecting ($57.44) and creator-collab ($68.18) are above the $45 target. Everything else is under it.

Take brand search out before you judge search

Brand search (people typing your company name) converts so well that it flatters the whole channel. Many of those buyers were already looking for you. Report Google Ads with and without it, so a strong brand campaign does not hide weak prospecting campaigns.

Subtract the brand row from the channel totals, then divide. With a Type column (Brand or Non-brand) you would add it as a second SUMIFS condition instead.

=(SUMIFS(G2:G11,B2:B11,"Google Ads")-G4)/(SUMIFS(C2:C11,B2:B11,"Google Ads")-C4)

Result: 3.251572327 shows as 3.25 with Number format

3.25 ROAS without brand, against 4.52 with it. Brand search alone returned 19.3x on $410, which is a measure of your brand, not of the ads.

Check whether an A/B test result is real

The newsletter went to two halves of the list, 9,000 people each. Version B got 459 clicks against 342, a 34% relative lift in click rate. Before you declare a winner, check whether the gap is bigger than chance would make it.

A two-proportion z-score does that: the difference in rates divided by its standard error, using the pooled rate of both versions. As a rule of thumb, a z-score beyond about 1.96 (either sign) means the difference would be unlikely if the versions really performed the same, which is the usual 95% confidence test. It assumes the two groups were split randomly.

=(E11/D11-E10/D10)/SQRT((E10+E11)/(D10+D11)*(1-(E10+E11)/(D10+D11))*(1/D10+1/D11))

Result: 4.2291613835 shows as 4.23 with Number format

4.23: B's higher click rate (5.10% against 3.80%) is very unlikely to be chance. Run the same formula on conversions (column F) and you get 0.73: 26 against 21 sales is too few to call, so B wins on clicks but not yet on revenue.

Pace the monthly budget

Ad platforms spend unevenly, and a campaign that is 18% ahead on day 20 runs out before the end of the month. Divide what you have spent by the days elapsed, multiply by the days in the month, and you have a projected month-end spend to compare with the budget.

Compare it with the even pace too: budget times days elapsed divided by days in the month, $9,677 by day 20 here.

=SUM(C2:C11)/H6*H8

Result: 17701

$17,701 projected against a $15,000 budget. $11,420 is already gone by day 20, leaving $3,580 for the last 11 days: about $325 a day, against the $571 a day you are spending now.

Practice: 8 exercises

Use the sample data above. Type a formula and press Check: it is graded by a real spreadsheet engine, the way Cellwise grades lessons.

  1. 1What is the total spend across all campaigns?

    Show answer

    =SUM(C2:C11) = 11420. Every campaign row is added: 11,420 so far this month.

  2. 2What is the cost per click for fall-sale-search (row 2)?

    Show answer

    =C2/E2 = 1.1825726141. 2,850 / 2,410 = $1.18 per click.

  3. 3What is the overall click-through rate across every campaign?

    Show answer

    =SUM(E2:E11)/SUM(D2:D11) = 0.017924221922. Adding first and dividing once weights each campaign by its size: 1.79% overall.

  4. 4How many conversions did Google Ads bring in?

    Show answer

    =SUMIFS(F2:F11,B2:B11,"Google Ads") = 255. The three Google rows add up: 96 + 71 + 88 = 255.

  5. 5What is the blended CPA for Meta Ads?

    Show answer

    =SUMIFS(C2:C11,B2:B11,"Meta Ads")/SUMIFS(F2:F11,B2:B11,"Meta Ads") = 32.820512821. 3,840 / 117 = $32.82: retargeting pulls the channel under target even though prospecting is above it.

  6. 6How many campaigns returned less than $2 of revenue for every $1 spent?

    Show answer

    =SUMPRODUCT(--(G2:G11<2*C2:C11)) = 3. Prospecting (1.39), Pinterest (1.96) and the TikTok creator video (1.10) fall below 2.

  7. 7By how much did newsletter B beat A on conversions per email delivered, as a relative lift?

    Show answer

    =(F11/D11)/(F10/D10)-1 = 0.2380952381. 26 against 21 conversions from the same 9,000 sends is a 23.8% lift, but too few sales to be sure it is real.

  8. 8At the current rate, what will total spend be at the end of the month (days elapsed in H6, days in month in H8)?

    Show answer

    =SUM(C2:C11)/H6*H8 = 17701. 11,420 / 20 x 31 = 17,701, which is $2,701 over the budget in H4.

Free templates for marketing

Guided project: UTM channel rollup

Clean up inconsistent UTM tags, then roll sessions and revenue up by medium and find the medium that earns most per visit.

5 steps in the Cellwise app. Each step checks your formula in a real grid, with hints when you get stuck. Part of Cellwise Plus.

Open guided projects

Guides for these formulas

FAQ

How do I calculate ROAS in Excel?

Divide revenue by ad spend: =G2/C2 for one campaign. For a channel or a whole account, add revenue and spend separately first, for example =SUMIFS(revenue,channel,"Meta Ads")/SUMIFS(spend,channel,"Meta Ads"), rather than averaging the campaign ROAS values.

How do I calculate CTR, CPC and CPA in Excel?

CTR is clicks divided by impressions, CPC is spend divided by clicks, and CPA is spend divided by conversions. Format CTR as a percentage. Wrap the divisions in IFERROR, or test the bottom number with IF, so a campaign with no clicks or conversions shows a blank instead of #DIV/0!.

Can Excel tell me if my A/B test is significant?

You can calculate a two-proportion z-score with basic functions: the difference in rates divided by SQRT(p*(1-p)*(1/n1+1/n2)), where p is the pooled rate. Beyond about 1.96 in either direction matches the common 95% confidence test. Excel's NORM.S.DIST function can turn the z-score into a p-value. Small tests with only a few conversions rarely give a reliable answer.

Should marketers use Excel or a dashboard tool?

Dashboards are good for watching the same numbers every day. Excel is better for questions the dashboard was not built for: blending several platforms, removing brand search, checking a test, or projecting spend. Many marketers export from the platforms into Excel for exactly that.

Excel for other fields

Updated