A/B Testing Demystified: Beginner's Guide using Excel
By: Vyshnavi Andhavarapu
I've always wondered how big companies like Amazon or Netflix decide which version of a webpage to show you. Is it solely a gut feeling? A meeting or debate? Turns out, data is their source. Companies measure performance as a decision maker when it comes to campaigns. A/B Testing is a highly used technique for data analysis.
In this blog, I'll walk you through what A/B testing actually is, why it matters, and how I ran one myself using Excel and a public dataset.
What is A/B Testing?
It is an experiment-oriented technique where two versions of something are compared to see which comes out with a better outcome. Version A to one group of users and version B to another, and then both are measured to see which provides a better response.
Primarily used in marketing, product design, and e-commerce.
A simple example:
Imagine your company sends out two versions of a promotional email. Version A has the subject line "Sale ends tonight!" and version B says "Don't miss out — 30% off today only." You send version A to half your mailing list and version B to the other half, then check which one got more opens. That's an A/B test.
The reason why this is proven to work is due to both groups receiving different messages at the same time and under the same conditions. Given that it's a fair comparison, companies can infer insights.
Why does it matter?
Before A/B Testing became common practice, product and marketing decisions were largely driven by opinions. A senior person in the room would say, “I think the green button looks better,” and that would be that.
The problem is that what feels right doesn’t always work. A/B Testing removes that personal bias altogether. You let your real users decide, through their behavior.
I think this is extremely powerful in data analytics because it gives you a framework for making recommendations with confidence. You’re not just saying “I think this is better”; you’re saying the data shows this is better, and here’s the statistical evidence.
Decisions made on data are more relevant to companies than gut feeling. With data-provided insights, companies can have more of a behavioral understanding to make a decision.
Key concepts
I want to briefly go over a few terms that will be used in the analysis.
Alternative and null hypothesis
You propose two hypotheses before doing any tests. The null hypothesis (H0) is that there is no real difference between your two groups; any difference is just random variation. The alternative hypothesis (H1) says the opposite: there is a meaningful difference. Your job is to figure out which one the data fits into.
p-value
This is the number everybody talks about in statistics, and it's actually easier than it sounds. The p-value tells you how likely it is that the difference you saw occurred just by chance. If the p-value is less than 0.05, the result is statistically significant — the difference is probably real. If the p-value is greater than 0.05, you cannot rule out that it was just luck.
Sample size
This is simple: the more participants in your test, the more trustworthy your findings will be. I've witnessed individuals conclude A/B testing conducted on thirty users. That's an error. For the statistics to be meaningful, the sample size must be sufficiently large.
I'm using a dataset that includes 500 users, 250 in the control group and the other half in the treatment group. Each row informs us whether the user converted (made a purchase) or not.
Steps
Count users in each group.
I always start by making sure the divide is correct. For your comparison to be fair, both groups must be nearly equal. For this, I used COUNTIF:
=COUNTIF(B:B,"control")
=COUNTIF(B:B,"treatment")
Calculate conversion rates
Next, I calculated what percentage of each group actually converted. This is the core metric we're comparing:
=COUNTIFS(B:B,"control",C:C,1)/COUNTIF(B:B,"control")
=COUNTIFS(B:B,"treatment",C:C,1)/COUNTIF(B:B,"treatment")
Then formatted in percentages
After formatting as percentages, I got 12.4% for the control group and 13.6% for the treatment group. Treatment looks better — but is that difference real?
Bar Chart
I always make a brief chart before working with any numbers. It makes it much simpler to share your study with others and provides you with a fast visual read of the data.
After highlighting the two conversion rates, I selected Insert → Bar Chart → Clustered Bar and gave it the caption "Conversion Rate: Control vs Treatment."
Contingency Table
Formula
=COUNTIFS(B:B,"control",C:C,1) → Control converted
=COUNTIFS(B:B,"control",C:C,0) → Control not converted
=COUNTIFS(B:B,"treatment",C:C,1) → Treatment converted
=COUNTIFS(B:B,"treatment",C:C,0) → Treatment not converted
Expected Values:
Formulas
K25= (M17*K19)/M19
L25= (M17*L19)/M19
K26= (M18*K19)/M19
L26= (M18*L19)/M19
Chi-square Test:
Formula
=CHISQ.TEST(K17:L18, K25:L26)
Results:
After the Chi-square test, the p-value came out to 0.69. This is larger than the threshold of 0.05; the result isn't significant. We can draw from the observation that we shouldn't roll out the new design because the treatment page is not fully outperforming the control.
One of those abilities that subtly strengthens you as a data analyst is A/B testing. After you grasp the framework, you begin to make decisions based on facts rather than opinions.
The thing that shocked me the most about this exercise was how easily accessible it is in Excel. R, Python, or any other specific tool is not required. CHISQ, a contingency table, and several COUNTIFS algorithms. To conduct a statistically valid experiment, all you need is TEST.
Get a dataset and try it if you haven't before. It sticks because you have to run the calculations yourself.
