A/B Test Significance in Excel: Sample Size, Conversion Rates and Experiment Results
A/B TestingConversion RatesStatisticsExperiment AnalysisExcel Formulas

A/B Test Significance in Excel: Sample Size, Conversion Rates and Experiment Results

EExcels.uk Editorial Team
2026-11-16
6 min read

Analyse A/B tests in Excel with conversion rates, uplift, sample-size planning, two-proportion significance checks and decision guidance.

Use this Excel worksheet method to analyse an A/B test from start to finish: define the hypothesis, calculate conversion rates and uplift, estimate a sensible sample size, run a two-proportion significance check and record whether the result is both statistically and practically useful.

Overview

An A/B test compares a control version with a variant by assigning eligible visitors or users to one of two groups. The outcome might be a purchase, form submission, trial start or another clearly defined conversion. Excel can provide a repeatable A/B test calculator, but the spreadsheet is only as reliable as the experiment design and the data entered into it.

The central question is not simply whether the variant has a higher conversion rate. It is whether the observed difference is large enough to be distinguished from ordinary sampling variation, and whether that difference matters commercially. A useful worksheet therefore reports four separate measures:

  • Conversion rate: conversions divided by eligible visitors.
  • Absolute difference: the variant rate minus the control rate, expressed in percentage points.
  • Relative uplift: the absolute difference divided by the control rate.
  • Statistical test result: a p-value or confidence interval for the difference between two proportions.

Statistical significance does not guarantee a valuable result. A small improvement may be detectable with a large sample but have little financial effect. Conversely, a commercially attractive difference may remain uncertain if the sample is too small.

How to estimate an A/B test in Excel

1. Set up the experiment definition

Before entering results, write down the primary conversion event, the control and variant descriptions, the target audience, the start and end dates, and the hypothesis. For example: “The new checkout page will increase completed purchases compared with the existing checkout page.” Decide whether the test is directional before looking at the result. A two-sided test is a cautious default when either an increase or decrease would be important.

2. Create the results table

Use one row for each test group. A simple worksheet can use these columns:

GroupEligible visitorsConversionsConversion rate
Controlenter valueenter value=C2/B2
Variantenter valueenter value=C3/B3

Format the conversion-rate cells as percentages. Keep the raw visitor and conversion counts rather than entering percentages alone; significance calculations require both the number of events and the number of opportunities.

3. Calculate difference and uplift

If the control rate is in D2 and the variant rate is in D3, use:

  • Absolute difference: =D3-D2
  • Relative uplift: =(D3-D2)/D2

For example, moving from 8% to 10% is a 2 percentage-point increase and a 25% relative uplift. These are different descriptions of the same result, so label them clearly in dashboards and reports.

4. Run a two-proportion significance check

For a standard large-sample approximation, first calculate the pooled conversion rate:

pooled rate = (control conversions + variant conversions) / (control visitors + variant visitors)

Then calculate the standard error of the difference under the null hypothesis:

standard error = SQRT(pooled rate*(1-pooled rate)*(1/control visitors + 1/variant visitors))

The z statistic is:

z = (variant rate-control rate)/standard error

For a two-sided p-value in Excel, use:

=2*(1-NORM.S.DIST(ABS(z_cell),TRUE))

Some Excel versions use NORMSDIST rather than NORM.S.DIST. Check the function names in the version used by your team before distributing a reusable spreadsheet. A p-value below your chosen significance threshold is evidence that the observed difference is unlikely under the no-difference assumption, but it is not a probability that the variant is correct.

5. Add an approximate sample-size calculation

Sample size should be estimated before the test starts. Define the baseline conversion rate, the smallest improvement worth detecting, the significance level and the desired power. For a two-group comparison, an approximate per-group calculation is:

n = ((z_alpha*SQRT(2*p_bar*(1-p_bar)) + z_power*SQRT(p1*(1-p1)+p2*(1-p2)))^2)/(p2-p1)^2

Here, p1 is the expected control rate, p2 is the target variant rate, p_bar is their average, and the z values correspond to the selected significance level and power. In Excel, commonly used planning inputs can be represented with NORM.S.INV(1-alpha/2) and NORM.S.INV(power) for a two-sided test. Round the result up with ROUNDUP(calculated_n,0). This is a planning approximation, not a guarantee that the final test will achieve the intended precision.

Inputs and assumptions

A two-proportion A/B test is most useful when the groups are comparable apart from the tested change. Review these assumptions before trusting the output:

  • Randomisation: visitors should be assigned without allowing user choice, device mix or traffic source to determine the group.
  • Stable assignment: returning users should not switch between versions in a way that contaminates outcomes.
  • One primary metric: define the main conversion before examining results; secondary metrics should be labelled as exploratory.
  • Consistent measurement: tracking rules, attribution windows and conversion definitions should be the same for both groups.
  • Independence: repeated observations from the same person, account or order can make a simple visitor-level test misleading.
  • Seasonality and external events: campaigns, outages, holidays and major product changes can affect one period or group disproportionately.
  • Sufficient counts: very small numbers of conversions can make the normal approximation unreliable. Consider specialist statistical review or an exact method when counts are sparse.

Record the significance level, confidence level, test direction, sample-size target, stopping rule and exclusion rules in the workbook. This prevents the decision process changing after the result is visible.

TermMeaning
Null hypothesisThe working assumption that the control and variant have the same conversion rate.
p-valueHow unusual the observed difference would be if the null hypothesis were true.
Confidence intervalA range of plausible values for the conversion-rate difference under the chosen method.
Statistical significanceEvidence against the no-difference assumption at a selected threshold.
Practical significanceWhether the size of the change justifies action, considering revenue, cost, risk and implementation effort.

Worked example

Suppose the control receives 1,000 eligible visitors and records 80 conversions. The variant receives 1,000 visitors and records 100 conversions.

  • Control rate: 80/1000 = 8%
  • Variant rate: 100/1000 = 10%
  • Absolute difference: 10%-8% = 2 percentage points
  • Relative uplift: 2%/8% = 25%

The pooled rate is 9%. Using the pooled standard error formula gives a z statistic of approximately 1.56. The corresponding two-sided p-value is approximately 0.12, so this example would not meet a 5% significance threshold. The correct conclusion is not that the variant has no effect; it is that the collected evidence is not sufficiently precise to rule out ordinary variation at that threshold.

If the planning assumptions were an 8% baseline, a target rate of 10%, a two-sided 5% significance level and 80% power, the approximate formula produces about 3,200 observations per group. The exact requirement depends on the method, assumptions and chosen design. A team should also consider the value of the conversion before committing to that traffic and test duration.

Use a decision log with fields for result, p-value, confidence interval, business impact, decision and follow-up. If the test is inconclusive, record whether to continue collecting data, redesign the experiment or stop because the potential benefit is too small.

When to recalculate

Recalculate the worksheet whenever new observations are added, the conversion definition changes, tracking is corrected, traffic allocation changes materially or an external event affects the test period. Do not repeatedly check significance and stop immediately after a favourable result without a pre-agreed stopping rule; that can increase the chance of a misleading conclusion.

At the end of each experiment, save a dated copy of the raw counts and decision. Compare later tests by audience, device, campaign and implementation rather than combining incompatible experiments. Revisit the sample-size assumptions when the baseline rate or minimum worthwhile improvement changes. Review the formulas at least annually, and test the workbook after Excel updates or when moving between desktop, web and older versions.

A practical next step is to create a reusable Excel template with separate tabs for Plan, Results, Calculations and Decision log. Lock formula cells, highlight input cells, validate that conversions do not exceed visitors, and add a visible warning when the sample is below the planned target. Used this way, an A/B test calculator becomes part of a consistent reporting workflow rather than a one-off significance check.

Related Topics

#A/B Testing#Conversion Rates#Statistics#Experiment Analysis#Excel Formulas
E

Excels.uk Editorial Team

Statistics and Spreadsheet Editor

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.