A/B Test in Excel: Compare Conversion Rates and Significance

An A/B test in Excel comes down to three numbers: the conversion rate of each version, the lift of B over A, and a p-value that tells you whether the gap is bigger than chance would usually produce. COUNTIFS gets the counts, simple division gives the rates, and a two-proportion z-test with SQRT and NORM.S.DIST gives the significance. This guide works through one example, for marketers, product analysts and anyone preparing for a data analyst interview.

What an A/B test measures#

In an A/B test, visitors are randomly split between two versions of a page, email or offer. Version A is usually the current one, called the control. Version B is the change. A conversion is the action you want, such as a sign-up or a purchase.

You compare the conversion rate, conversions divided by visitors, of the two versions. The question is whether B's rate is really different, or whether the gap could easily be random noise from which visitors happened to land in each group.

The example below uses these counts, which are made up for illustration:

Count visitors and conversions with COUNTIFS#

If your data is one row per visitor, COUNTIFS builds the summary table. With the version in column B and "Yes" or "No" in column C of a raw data sheet:

Fill down for B, changing "A" to "B", or put the version names in column A and use A2 instead of typing them. Format column D as a percentage. A is 5.0% (200 / 4,000) and B is 6.2% (248 / 4,000).

Count people, not page views. If one visitor can appear many times, remove the duplicates first. The COUNTIFS function guide and our article on COUNTIFS and SUMIFS cover the criteria, and COUNTIF as a percentage shows the same count-and-divide pattern.

Calculate the difference and the lift#

Report the gap two ways. The absolute difference is B's rate minus A's: 6.2% − 5.0% = 1.2 percentage points. The relative lift compares that gap with the control:

=(D3-D2)/D2

The result is 24%: B converted 24% more often than A. Lift is a percent change, so the percent change lesson and our guide to percentage formulas apply directly. Always say which you mean, because "up 1.2%" and "up 24%" describe the same result.

Test significance with a two-proportion z-test#

A two-proportion z-test asks how many standard errors apart the two rates are. Build it in four cells:

• The pooled rate treats both groups as one, because the test starts by assuming A and B convert at the same rate.
• The standard error is how much the gap between two rates would normally wobble by chance at these sample sizes. It uses the same idea as standard deviation.
• z is the gap measured in standard errors. Here the 1.2-point gap is about 2.33 standard errors.
• NORM.S.DIST with TRUE gives the area under the normal curve below z. 1- that, doubled, is the chance of a gap at least this large in either direction, if there were no real difference.

NORM.S.DIST needs Excel 2010 or later. The z-test relies on a normal approximation, so it works best with reasonably large groups. A common rule of thumb is at least 10 conversions and 10 non-conversions in each version.

The same answer with CHISQ.TEST#

You can also put the counts in a 2×2 table of converted and not converted (200 and 3,800 for A; 248 and 3,752 for B), build the expected counts (224 and 3,776 for each), and use =CHISQ.TEST(observed,expected). It returns the same 0.020, because for a 2×2 table the chi-square test and the two-sided z-test are equivalent. Our guide to the chi-square test in Excel shows how to build the expected table.

How to read the p-value#

The p-value is 0.020. That means: if A and B truly had the same conversion rate, a gap of 1.2 points or more would show up in only about 2% of tests this size. If you set 0.05 as your cut-off before the test, this result counts as statistically significant.

Be careful what you claim:

• It is not a 98% chance that B is better. A p-value is about the data, assuming no real difference.
• It does not say the lift is exactly 24%. A 95% confidence interval for the gap is roughly 0.2 to 2.2 percentage points, so the true improvement could be small. See confidence intervals in Excel for the method.
• Significant isn't the same as important. Ask whether the gap is big enough to matter for the business.

Sample size changes everything. With the same rates but only 1,000 visitors per version (50 and 62 conversions), z falls to about 1.17 and the p-value rises to about 0.24. The gap looks identical, but there is too little data to tell it apart from chance.

Common A/B testing mistakes#

These mistakes make a result look more certain than it is:

1. Stopping as soon as it looks significant. Checking every day and stopping at the first p below 0.05 raises the false-positive rate. Decide the sample size or test length in advance.
2. Testing many metrics or versions. With 20 comparisons, about one may cross 0.05 by chance alone. Pick one main metric before you start.
3. Uneven splits. If a 50/50 test ends at 4,000 against 3,500 visitors, check the setup before trusting the result.
4. Counting sessions instead of people. Repeat visits break the assumption that each row is independent.
5. Running too short a test. A few days may miss weekday and weekend differences.

A/B test questions are common in analyst interviews. The data analyst Excel test guide covers the other skills they check, and the data analyst mock test lets you try a timed version.

Practise it#

Try the exercise on this page to calculate a conversion rate from raw rows with COUNTIFS. Then practise COUNTIFS and SUMIFS in a live spreadsheet, and practise percent change so lift calculations become automatic. Each lesson checks your formula as soon as you press Enter.