# Click-Through Rate Formula: How to Calculate CTR (Excel)

- URL: https://betteranalyst.com/metrics/click-through-rate
- Published: 2026-10-28
- Updated: 2026-10-28
- Author: David Bressler, Founder (https://betteranalyst.com/authors/david-bressler)

> **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.

Category: [Marketing](https://betteranalyst.com/metrics#marketing)

CTR formula with a search-ads example by ad group, the Excel formula, why averaging CTRs is wrong, and how to read CTR for ads, email and organic search.

## 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.

*Search campaign, last 30 days (table starts in cell A1)*

| Ad group | Impressions | Clicks |
| --- | --- | --- |
| Payroll software | 48,200 | 2,169 |
| Payroll for small business | 21,600 | 1,382 |
| Competitor terms | 15,900 | 397 |

1. Total clicks = 2,169 + 1,382 + 397 = 3,948.
2. Total impressions = 48,200 + 21,600 + 15,900 = 85,700.
3. Campaign CTR = 3,948 ÷ 85,700 = 4.61%.
4. 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](https://betteranalyst.com/data-connectors/google-search-console), [Google Analytics](https://betteranalyst.com/data-connectors/google-analytics), [Google Sheets](https://betteranalyst.com/data-connectors/google-sheets), [Google BigQuery](https://betteranalyst.com/data-connectors/bigquery).

[Try Better Analyst free](https://betteranalyst.com/signup)

## Further reading

- [ROAS formula](https://betteranalyst.com/metrics/roas)
- [Connect Google Search Console](https://betteranalyst.com/data-connectors/google-search-console)
- [How to divide in Excel](https://betteranalyst.com/excel/how-to-divide-in-excel)
- [SUMIF function](https://betteranalyst.com/excel/sumif-function)
- [Better Analyst for marketing teams](https://betteranalyst.com/solutions/marketing-teams)

## Frequently asked questions

### How do I calculate CTR in Excel?

Put impressions in B and clicks in C, then use =C2/B2 for one row or =SUM(C2:C4)/SUM(B2:B4) for the total, and format as a percentage. In Google Sheets the formulas are identical.

### Why is my Search Console CTR different from my average of page CTRs?

Search Console's site CTR is total clicks ÷ total impressions, so high-impression pages dominate it. Averaging page CTRs gives every page equal weight.

### Is a higher CTR always better?

No. A misleading headline can lift CTR while conversion rate and revenue fall. Judge CTR together with what happens after the click.

## Related metrics

- [ROAS Formula: How to Calculate Return on Ad Spend (Excel)](https://betteranalyst.com/metrics/roas)