Image by Editor

A/B testing (also called split testing) is a controlled experiment that compares two versions of a variable, such as a survey question, closing message, pricing page, or user interface, to determine which performs better on a key metric (e.g., satisfaction score, conversion rate, or recommendation likelihood). If you can do it correctly, it enables causal inference and data-driven decisions.

In this tutorial, we will show A/B testing with Microsoft Stack. From Microsoft Forms to Excel to Power BI, it gives you everything you need to design, analyze, and communicate experiments with statistical integrity.

Step 1: Planning the Experiment (Before Building Anything)

Define your hypothesis, primary metric, population, and minimum detectable effect (MDE) upfront. Consider a scenario of a customer satisfaction survey, where you want to test two versions of a closing message:

• Version A (control): “Thanks for contacting support—please rate your experience”
• Version B (treatment): “We’d love feedback—please rate how we did today”
• Primary metric:  Average satisfaction score (Likert scale)
• Secondary metrics: % who would recommend you (Net Promoter Score style), completion rate.
• Null hypothesis (H₀): No difference in mean satisfaction between A and B
• Alternative hypothesis (H₁): There is a difference (two-tailed test)
• Significance level (α): 0.05
• Desired power (1 – β): 80% (standard for most business tests)
• Minimum detectable effect: 0.5-point difference on a 5-point scale (practical importance)

Step 2: Implementing Randomization Mechanics in Microsoft Forms

Microsoft Forms does not provide native true randomization for A/B assignment, but you can reliably simulate it using two proven methods.

Method A: Branching Logic + Pseudo-Randomizer Question

• Create a new form titled “Customer Satisfaction Survey – A/B Test”.
• Add four sections:
  • Section 1: Introduction + Demographics + Randomizer
  • Section 2: Version A questions
  • Section 3: Version B questions
  • Section 4: Closing / shared questions
• In Section 1, add a required randomizer question (use one of these):
  • “What is the last digit of your mobile phone number?” (options 0–9)
  • “Please select a range that comes to mind right now (think randomly!)” (1–50 = Group A; 51–100 = Group B)
• Add your version-specific questions (e.g., satisfaction rating) in Sections 2 and 3.
• Set up branching:
  • Even digits (0,2,4,6,8) or 1–50 → Section 2 (Version A)
  • Odd digits (1,3,5,7,9) or 51–100 → Section 3 (Version B)
  • After the version-specific question, branch to Section 4

• Preview, test thoroughly, and share one single link

Approximately 50/50 split with minimal user influence when using the phone-digit method.

For a full step-by-step guide on randomization in Forms, see the related article on survey design basics.

Method B: Duplicate Forms + Excel Random Assignment

• Build the base form with shared questions
• Duplicate it (⋯ → Duplicate) and edit one copy for Version B
• Export your contact list to Excel
• In Excel, add a column to sort/filter to assign groups:

=IF(RAND()<0.5,"A","B")

• Send the appropriate Form link to each group

Best practice: Always use a single neutral entry-point link when possible to reduce selection bias. Monitor response rates for balance.

Step 3: Collect Responses and Export to Excel

• Share the Form link via email, Teams, QR code, or embedded page
• Responses appear in real time
• Go to the Collect responses tab >> select Open results in Excel
• Select Open in Excel Desktop

• Save a local copy to freeze the dataset

Step 4: Calculate Statistical Power, Significance, and Perform Balance Checks in Excel

4.1 Power Analysis (Sample Size Planning)

Before launching (or mid-test), calculate the required sample size. This is the step most practitioners skip, and it’s the most important one. Running a test until you see a significant result (known as “peeking”) inflates your false positive rate dramatically.

Simple Excel formulas: For a two-sample t-test on means.

• Use the formula for approximate sample size per group:

n = (2 * (Z_{1-α/2} + Z_{1-β})^2 * σ^2) / δ^2

Where:

• Z_{1-α/2} ≈ 1.96 (95% confidence)

• Z_{1-β} ≈ 0.84 (80% power)

• σ = expected standard deviation (from pilot data)
• δ = minimum detectable effect (e.g., 0.5)

=CEILING.MATH(2*(1.96+0.84)^2 * 1.2^2 / 0.5^2)

For proportions (e.g., % recommend), use the two-proportion sample size formula or online calculators validated against Statology resources.

Rule of thumb: Aim for ≥30–50 responses per group minimum; higher for smaller effects.

To understand probability vs. non-probability sampling and how it affects your respondent pool, explore the following article: Types of Sampling Methods (With Examples)

4.2 Data Preparation & Balance Checks

Create / Verify Group Column:

• Create a Group column using formulas if not already present

=IF(MOD([@[Last digit of mobile number]],2)=0,"A","B")

This assigns even digits to Group A and odd digits to Group B.

Create Pivot Table:

You can compare demographics (age, gender, etc.) across A and B.

• Select the form’s data
• Go to the Insert tab >> select PivotTable
  • Rows: Gender
  • Columns: Group (A/B)
  • Values: Count of ID

You can create another PivotTable or summary formulas for the average age by group.

Statistical Balance Tests:

• Go to the Data tab>> select Data Analysis >> select t-Test: Two-Sample Assuming Unequal Variances
• Or use the worksheet formula directly:

For Age (continuous):

=T.TEST(age_range_A, age_range_B, 2, 3)

=T.TEST(FILTER(Forms!F2:F21, Forms!E2:E21="A"), FILTER(Forms!F2:F21, Forms!E2:E21="B"), 2, 3)

Here:

• 2 = two-tailed test
• 3 = two-sample unequal variance

If the p-value is greater than 0.05, the groups are reasonably balanced on that variable. The p-value is 0.998, which means it is balanced. If imbalanced, note it as a limitation or add covariates in the analysis.

Check Balance on Gender:

For a categorical covariate such as Gender, create a contingency table and use a Chi-square test.

• Create a small contingency table and use:

=CHISQ.TEST(observed_range, expected_range)

If both p-values are > 0.05, the groups are reasonably balanced.

If this p-value is greater than 0.05, there is no strong evidence of imbalance in that categorical variable.

For a structured approach to detecting and correcting for bias in experimental data, go through this article: 5 Types of Data Bias and How to Address Them

4.3 Hypothesis Testing & Significance

Once the data is prepared and the groups look reasonably comparable, test whether the outcomes differ in a meaningful way.

Mean Comparison Test:

For mean-based outcomes such as Overall satisfaction (1–5) or Completion time (seconds), use a two-sample t-test.

Satisfaction p-value:

=T.TEST(FILTER(Forms!H2:H21, Forms!E2:E21="A"), FILTER(Forms!H2:H21, Forms!E2:E21="B"), 2, 3)

Here, 2 means a two-tailed test, and 3 represents two-sample unequal variances.

Recommendation p-value:

=T.TEST(FILTER(Forms!I2:I21, Forms!E2:E21="A"), FILTER(Forms!I2:I21, Forms!E2:E21="B"), 2, 3)

95% Confidence Interval:

To add a 95% confidence interval, use Excel’s margin-of-error formula:

=CONFIDENCE.NORM(0.05,STDEV.S(range),COUNT(range))

Then calculate the lower and upper bounds like this:

=AVERAGE(range)-CONFIDENCE.NORM(0.05,STDEV.S(range),COUNT(range))
=AVERAGE(range)+CONFIDENCE.NORM(0.05,STDEV.S(range),COUNT(range))

Satisfaction – Group A:

Lower CI A:

=AVERAGEIFS(Forms!H2:H21,Forms!E2:E21,"A") - CONFIDENCE.NORM(0.05, STDEV.S(FILTER(Forms!H2:H21,Forms!E2:E21="A")), COUNTIFS(Forms!E2:E21,"A"))

Upper CI A:

=AVERAGEIFS(Forms!H2:H21,Forms!E2:E21,"A") + CONFIDENCE.NORM(0.05, STDEV.S(FILTER(Forms!H2:H21,Forms!E2:E21="A")), COUNTIFS(Forms!E2:E21,"A"))

Lower CI B:

=AVERAGEIFS(Forms!H2:H21,Forms!E2:E21,"B") - CONFIDENCE.NORM(0.05, STDEV.S(FILTER(Forms!H2:H21,Forms!E2:E21="B")), COUNTIFS(Forms!E2:E21,"B"))

Upper CI B:

=AVERAGEIFS(Forms!H2:H21,Forms!E2:E21,"B") + CONFIDENCE.NORM(0.05, STDEV.S(FILTER(Forms!H2:H21,Forms!E2:E21="B")), COUNTIFS(Forms!E2:E21,"B"))

If the range contains the binary 1/0 values for one group, this gives the 95% confidence interval for that group’s proportion. If it contains a numeric outcome such as satisfaction, it gives the 95% confidence interval for that group’s mean.

You can also explore Statistical Tests, it Covers t-tests, Chi-square, and other tests you’ll use directly on Forms-exported data.

Interpret Rules:

When writing up results, follow these interpretation rules:

• p < 0.05 means the difference is statistically significant, so you reject the null hypothesis.
• Always report the effect size, such as the absolute difference in means or proportions, not just the p-value.
• Check the confidence interval width. A wide interval means the estimate is still uncertain.
• Practical significance matters more than the p-value alone. A result can be statistically significant but too small to matter.
• Do not make strong causal claims unless the assignment method is genuinely random and the balance checks support comparability.

Best practices for interpreting and presenting A/B test findings.

Step 5: Visualize Experimental Outcomes in Power BI

Import Data:

• Open Power BI Desktop
• Go to the Home tab >> select Get Data >> select Excel Workbook
• Select your cleaned workbook

• Click Load or Transform Data to use Power Query to clean further:
  • Confirm column types (Group should be text; conversion should be 0/1 integer)
  • Filter out any incomplete or test responses
  • Add a calculated column for subgroup analysis if needed
•  Add DAX measures:

Avg Satisfaction A = CALCULATE(AVERAGE('Forms'[Overall satisfaction (1-5)]), 'Forms'[Group (A/B)] = "A")

Avg Satisfaction B = CALCULATE(AVERAGE('Forms'[Overall satisfaction (1-5)]), 'Forms'[Group (A/B)] = "B")

Lift % = DIVIDE([Avg Satisfaction B] - [Avg Satisfaction A], [Avg Satisfaction A])

Build Dashboard:

• Cards: Overall metrics + cards for A vs B
• Clustered Bar/Column charts: Satisfaction by Group
• Slicers: Gender and Age
• Table/matrix: Table with means, counts, and p-values
• Line chart: Cumulative responses over time

• Publish to the Power BI Service for sharing
• Set up a scheduled refresh if the Excel file is in OneDrive

Pro tip: Use conditional formatting (green/red) on KPIs and tooltips with full statistical footnotes.

Step 6: Proper Interpretation and Reporting Results

Based on our sample form, Version B achieved a higher average satisfaction (4.56 vs 4.25). The difference is statistically significant (p < 0.05 via two-sample t-test). Groups were well balanced in age. We recommend adopting Version B while monitoring for larger sample validation.

• Never say “Version B is 15% better” without context, report confidence intervals, and p-values
• Highlight limitations (e.g., voluntary response bias, pseudo-randomization)
• Recommend next steps: “Roll out Version B to 10% more users and re-test”
• Export the Power BI report as PDF/PowerPoint with embedded Statology-style explanations

Conclusion

By following this Microsoft Stack workflow, you get production-ready A/B testing without leaving the Microsoft 365 ecosystem. The combination of Forms’ accessibility, Excel’s statistical depth, and Power BI’s visualization power makes sophisticated experimentation available to any analyst or business user. The best way to use the Microsoft stack for A/B testing is to treat Microsoft Forms as the collection layer, Excel as the statistical engine, and Power BI as the communication layer.  Start with the branching method in Forms today and iterate from there.