How to Calculate Conversion Rate in Excel by Channel

Conversion rate is the share of visitors who did what you wanted: bought, signed up or booked a call. In Excel you calculate it as conversions divided by visitors, then format the result as a percentage. This guide shows the conversion rate formula in Excel for one channel and for every channel at once, how to stop #DIV/0! errors, and the averaging mistake that quietly gives the wrong answer.

The conversion rate formula#

Conversion rate is conversions ÷ visitors. "Visitors" can be sessions, clicks, leads or emails delivered: use whatever your report counts as the starting point, and keep it the same across channels.

If C2 holds sessions and D2 holds orders:

=D2/C2

With 400 sessions and 12 orders, the result is 0.03. Format it as a percentage to show 3%: press Ctrl+Shift+% on Windows or ⌃⇧% (Control+Shift+%) on a Mac, or use Home > Number > %. Excel multiplies by 100 for display, so don't multiply by 100 yourself. The guide to percentage formulas in Excel covers the basics.

Conversion rate by channel with SUMIFS#

When your data has one row per day per channel, total the conversions and the sessions for one channel, then divide. Here is the sample data:

The Email conversion rate is:

=SUMIFS(D2:D7,B2:B7,"Email")/SUMIFS(C2:C7,B2:B7,"Email")

The first SUMIFS adds the Email orders (12 + 14 = 26). The second adds the Email sessions (400 + 350 = 750). The result is 26 ÷ 750 = 3.47%. The SUMIFS function guide explains each argument.

To build a summary table, list the channels in F2:F4 and point the formula at the cell, with the ranges locked:

=SUMIFS($D$2:$D$7,$B$2:$B$7,F2)/SUMIFS($C$2:$C$7,$B$2:$B$7,F2)

Fill it down to get:

Add a date condition to look at one period, for example B2:B7,F2,A2:A7,">="&DATE(2026,11,2). The guide to COUNTIFS and SUMIFS with several conditions shows more patterns.

Conversion rate from a list of leads with COUNTIFS#

When each row is one visitor or lead, with a Channel column and a Converted column holding Yes or No, count instead of add. For Email:

=COUNTIFS(B2:B500,"Email",C2:C500,"Yes")/COUNTIFS(B2:B500,"Email")

The top half counts Email leads that converted. The bottom half counts all Email leads. If 8 of 40 Email leads say Yes, the result is 20%. This is the same idea as using COUNTIF to find a percentage, with one extra condition. The COUNTIFS function guide has the full syntax.

Don't average conversion rates#

The most common mistake is to average the rates instead of dividing the totals. In the table above, the average of the three channel rates is (3.47% + 2.74% + 1.00%) ÷ 3 = 2.40%. The true overall rate is 106 ÷ 4,750 = 2.23%.

The average is wrong because it gives each channel the same weight. Email had only 750 sessions, but in the average it counts as much as Search with 2,300. The same problem happens when you average daily rates across a month: a quiet Sunday counts as much as a busy Monday.

The rule: add up first, divide once. For the overall rate, use =SUM(D2:D7)/SUM(C2:C7).

Handle zero visitors with IFERROR#

A channel with no sessions makes the formula divide by zero, and Excel shows #DIV/0!. This happens with a new channel, a paused campaign or a date range with no data yet. Wrap the formula in IFERROR to show 0 instead:

=IFERROR(SUMIFS(D2:D7,B2:B7,"Email")/SUMIFS(C2:C7,B2:B7,"Email"),0)

IFERROR returns the formula's result if it works and the second argument if it fails. See the IFERROR function guide for details.

Be aware that IFERROR hides every error, including a typo in a range. If you would rather show a blank only when there is no traffic, test the sessions directly: =IF(SUMIFS(C2:C7,B2:B7,"Email")=0,"",SUMIFS(D2:D7,B2:B7,"Email")/SUMIFS(C2:C7,B2:B7,"Email")). The guide to the #DIV/0! error compares both approaches.

Compare conversion rates over time#

To compare two periods, decide whether you mean percentage points or percent change. Say Email converted at 3.0% on 1 November (12 ÷ 400) and 4.0% on 2 November (14 ÷ 350).

• Percentage points is the simple difference: 4.0% − 3.0% = 1 percentage point. Formula: =E3-E2.
• Percent change is the relative change: (4.0% − 3.0%) ÷ 3.0% = 33.3%. Formula: =(E3-E2)/E2.

Both are correct, but they tell different stories, so label which one you report. "Conversion rose 33%" sounds much bigger than "conversion rose 1 point", even though it's the same change. Reports like this are everyday work in Excel for marketing jobs.

Common conversion rate mistakes#

Most wrong conversion rates come from a few slips. Check these before you share a report:

1. Dividing the wrong way round. Visitors ÷ conversions gives a number like 28.8 instead of 3.47%. Conversions always go on top.
2. Mixing definitions. If Search counts sessions and Email counts emails delivered, the rates are not comparable. Pick one starting point per report.
3. Ranges of different sizes. SUMIFS(D2:D7,B2:B8,"Email") returns #VALUE!. Every range in one SUMIFS must cover the same rows.
4. Extra spaces in channel names. "Email " with a trailing space doesn't match "Email", so its rows drop out. Clean the column with TRIM.
5. Multiplying by 100 and formatting as %. That shows 347% instead of 3.47%. Do one or the other, not both.

Practise it#

Conversion rate is a SUMIFS or COUNTIFS divided by another, so the core skill is conditional totals. Practise COUNTIFS and SUMIFS in a live spreadsheet: you type each formula and it checks it instantly. Then use the percent change lesson to compare periods, and the IFERROR lesson to handle channels with no traffic. Or start with the exercise on this page.