Click-Through Rate (CTR)

Excel Formulas › E-commerce & Marketing

All versions

CTR is clicks over impressions — how compelling an ad, email, or link is. It’s the first conversion in the funnel and a fast read on creative and targeting.

Quick formula: CTR from clicks and impressions:

=clicks / impressions

Clicks divided by impressions, as a percentage. Display CTRs are often well under 1%.

The example

250 clicks from 50,000 impressions.

	A	B
1	Item	Value
2	Clicks	250
3	Impressions	50000 → 0.5%

The formula

The formula:

=B2 / B3 // clicks ÷ impressions

How it works

How it works:

Divide clicks by impressions and format as a percentage.
CTR gauges creative and targeting — the share who found the message relevant enough to click.
Benchmarks vary widely: email CTRs run a few percent; display often under 1%.
CTR × conversion = the click-to-customer path; both must work.

High CTR, low conversion = a mismatch. A clickbait ad can post a great CTR but waste spend if the landing page doesn’t deliver. Track CTR and post-click conversion together — the goal is qualified clicks that convert, not the most clicks. CTR optimizes the ad; conversion optimizes the page.

Try it: interactive demo

Live demo

Clicks and impressions.

Clicks

Impressions

CTR:

Variations

Click-to-customer

CTR × conversion:

=CTR * conversion_rate

Clicks from a target CTR

Forecast:

=target_CTR * impressions

Email click rate

Of delivered:

=email_clicks / emails_delivered

Pitfalls & errors

Clicks vs unique clicks. Decide whether repeats count.

Channel benchmarks differ. Email, search, and display CTRs aren’t comparable.

CTR ≠ quality. Pair with conversion — clicks that don’t convert waste spend.

Practice workbook

📊

Download the free Click-Through Rate (CTR) practice workbook

A CTR sheet with the click-to-customer, forecast, and email variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate click-through rate in Excel?

Divide clicks by impressions: =clicks / impressions. 250 of 50,000 is 0.5%.

What's a good CTR?

It depends on the channel — email CTRs run a few percent, display often under 1%. Compare within a channel.

Why pair CTR with conversion?

A high CTR with low conversion signals an ad/landing-page mismatch — you want qualified clicks that convert.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class