How to Perform ANOVA: Two-Factor With Replication in Excel – Step-by-Step with Example

Jun 29, 2025

—

by

ANOVA (Analysis of Variance) is used to test if there are statistically significant differences between group means. The two-factor with replication version checks:

The impact of two independent variables (factors)
Whether there’s an interaction between them
When each combination of factor levels has multiple observations (i.e., replication)

📚 Real-Life Scenario Example (Indian Context)

Imagine you’re testing the performance of two different teaching methods (Factor A) across 3 schools (Factor B), and each method was tested on 3 students per school.

Your data table would look like:

	School A	School B	School C
Method 1	75, 78, 74	80, 82, 81	77, 76, 78
Method 2	70, 69, 68	72, 74, 73	71, 72, 70

Each cell contains replications (3 values) for that combination of method & school.

✅ How to Perform ANOVA: Two-Factor With Replication in Excel

🔹 Step 1: Organize Your Data

Your data must be arranged like this:

	School A			School B			School C
	Rep1	Rep2	Rep3	Rep1	Rep2	Rep3	Rep1	Rep2	Rep3
Method 1	75	78	74	80	82	81	77	76	78
Method 2	70	69	68	72	74	73	71	72	70

🧠 Each row = one level of Factor A (e.g., teaching method)
Each group of columns = one level of Factor B (e.g., school)
Each cell = a replicated value (score)

🔹 Step 2: Load the Data Analysis Toolpak

If not yet enabled:

Go to File → Options → Add-ins
In Manage, select Excel Add-ins → Click Go
Check Analysis ToolPak → Click OK
Go to the Data tab → Click Data Analysis

🔹 Step 3: Run ANOVA: Two-Factor With Replication

Click Data → Data Analysis → Choose ANOVA: Two-Factor With Replication
Click OK
Input Range: Select your full data including labels
Rows per Sample: Enter the number of replications (e.g., 3)
Choose Output Range or New Worksheet
Click OK

📊 Understanding the Output

Excel gives a detailed ANOVA table with 3 key sections:

Source of Variation	SS	df	MS	F	P-value	F crit
Rows (Factor A)	Differences due to methods
Columns (Factor B)	Differences due to schools
Interaction	Combined effect
Within	Residual error
Total	Total variation

🧠 Key Columns:

F-value: The test statistic
P-value: If P < 0.05 → statistically significant
F crit: Threshold from F-distribution

✅ What the Output Tells You

If P-value for Rows < 0.05 → significant difference between teaching methods
If P-value for Columns < 0.05 → significant difference between schools
If P-value for Interaction < 0.05 → method effectiveness varies across schools