Click-Through Rate Formula: How to Calculate CTR (Excel)
Summary
CTR = Clicks ÷ Impressions × 100. It is the share of people who saw an ad, listing or email link and clicked it. A campaign with 3,948 clicks from 85,700 impressions has a 4.61% CTR. Always calculate it from totals, never by averaging the CTRs of individual rows.
On this page
Click-through rate (CTR) formula
CTR % = Clicks ÷ Impressions × 100- Clicks: Number of clicks on the ad, search result or link in the period.
- Impressions: Number of times it was shown. For email, use delivered emails (or opens, for click-to-open rate) instead.
How to calculate click-through rate (CTR) in Excel and Google Sheets
Excel, using the cells in the worked example below:
=SUM(C2:C4)/SUM(B2:B4)Worked example
A B2B payroll-software company reviews one Google Ads search campaign by ad group to decide where to rewrite ad copy.
| Ad group | Impressions | Clicks |
|---|---|---|
| Payroll software | 48,200 | 2,169 |
| Payroll for small business | 21,600 | 1,382 |
| Competitor terms | 15,900 | 397 |
- Total clicks = 2,169 + 1,382 + 397 = 3,948.
- Total impressions = 48,200 + 21,600 + 15,900 = 85,700.
- Campaign CTR = 3,948 ÷ 85,700 = 4.61%.
- By ad group (=C2/B2 filled down): 4.50%, 6.40% and 2.50%. The simple average of those three is 4.47%, which understates the real campaign CTR.
Result: 4.61% CTR for the campaign
How to interpret click-through rate (CTR)
CTR measures how relevant and compelling something looked at the moment it was shown, not whether it produced value. Expectations differ a lot by placement: display and most social placements are typically well under 1–2%, search ads commonly land in the low-to-mid single digits, and branded search or the top organic result can be far higher. Compare CTR within the same placement and position over time. A high CTR with a poor conversion rate usually means the ad promises something the landing page doesn't deliver.
Common mistakes
- Averaging row-level CTRs. Small ad groups get the same weight as big ones; divide total clicks by total impressions.
- Comparing CTR across positions. A result in position 1 and one in position 6 aren't comparable; segment by position or placement first.
- Treating competitor-term CTR as a copy problem. Low CTR on rivals' brand names is expected, because most searchers want the brand they typed.
- Mixing email definitions: click rate (clicks ÷ delivered) and click-to-open rate (clicks ÷ opens) differ by a large factor.
Calculate click-through rate (CTR) with Better Analyst
Connect your data or upload a spreadsheet, then ask in plain English. Better Analyst writes and runs the calculation and shows the steps, so you can check the number.
- “Calculate CTR by ad group and flag groups more than 30% below the campaign average.”
- “Show organic CTR by average position bucket for our top 200 Search Console queries.”
- “Which landing pages have high CTR but low conversion rate?”
Usually calculated from: Google Search Console, Google Analytics, Google Sheets, Google BigQuery.