Click-through rate
What is the click-through rate of the Search campaign, clicks divided by impressions, as a percent rounded to two decimals?
Formula: =ROUND(C2/B2*100,2) → 4
Hands-on Excel exercises
Excel Skills for Marketing
Practice Excel for marketing: ROI, CTR, and ROAS from a campaign table, with IFERROR for zero-impression rows, plus worked, checked answers.
Marketing spreadsheets turn exports into ratios: click-through rate, return on investment, cost per lead, return on ad spend. The arithmetic is simple. The catch is that the denominators are sometimes zero, because a paused campaign has no impressions. Marketing hiring tests and interviews therefore like to see the ratio calculated correctly and the zero case handled without an ugly error.
The live sheet is a four-campaign table with impressions, clicks, spend, and revenue, including an email row with zero impressions. Your task is the percent return on investment for the social campaign, defined as revenue minus spend divided by spend, wrapped in IFERROR so a zero spend would show 0 instead of an error. The worked answers add the click-through rate for search, the email rate with a safe fallback, and the total return on ad spend across all campaigns.
Before comparing channels, agree on the definitions and the date window. A ratio of totals is not the average of ratios, and the difference matters. Keep the raw export untouched and label the assumptions beside your result. When campaigns need grouping by channel or month, the sales operations page covers conditional totals and the data analysts page covers joins to reference tables.
Your turn
Your turn: in F2, calculate the percent return on investment for the Social campaign, (revenue - spend) / spend x 100, rounded to one decimal, and show 0 if the calculation fails.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Campaign | Impressions | Clicks | Spend | Revenue | |
| 2 | Search | 12000 | 480 | 900 | 2700 | |
| 3 | Social | 20000 | 300 | 700 | 1050 | |
| 4 | 0 | 0 | 150 | 0 | ||
| 5 | Display | 30000 | 150 | 600 | 450 | |
| 6 |
Not yet solved.
Answer: =IFERROR(ROUND((E3-D3)/D3*100,1),0)
Practice under a time limit
Try the free Sample mock exam with 4 tasks in 8 minutes, or browse the level and interview mock exams included with Pro + Exams.
Related exercises
Open a task in its lesson.
- In H2:H6, get numbers out of "$1,250": remove the $ sign and the commas.
- In E6, count how many deals are worth more than $800.
- In F3, count how many deals in Chicago are worth more than $1,000.
- In F2:F4, look up the item name for each code in column E.
- Build a second PivotTable: the average deal amount by city, with the result in F8.
- Build a line chart of revenue and expenses together (A1:C7).
- In J2:J4, calculate the average deal (revenue / deals), rounded to whole dollars.
Worked answers
Try the spreadsheet first, then compare your formula and result.
Handle a zero-impression row
Calculate the click-through rate of Email, clicks divided by impressions, and return 0 when the result is an error.
Formula: =IFERROR(C4/B4,0) → 0
Total return on ad spend
What is total revenue divided by total spend, rounded to two decimals?
Formula: =ROUND(SUM(E2:E5)/SUM(D2:D5),2) → 1.79
Frequently asked questions
Why use IFERROR for a ratio?
Dividing by zero returns the error #DIV/0!. IFERROR lets you show a fallback, but make sure that fallback is the honest value and not a mask for another error.
Is a total ratio the same as an average of ratios?
No. Divide total revenue by total spend for the whole portfolio; averaging campaign ratios weights small campaigns too heavily.
What definitions should I agree first?
Which conversions count, the date window, and whether spend includes fees. State them next to the result.
Can the zero-impression row mean something else?
Yes, it may be a paused campaign or missing data. Investigate before reporting a zero rate.