How to Run a One-Way ANOVA in Excel
By ExcelReady TeamUpdated 6 min read
The short answer
To run a one-way ANOVA in Excel, turn on the Analysis ToolPak, then go to Data > Data Analysis > Anova: Single Factor. Select your groups (one column each, with labels), set Alpha to 0.05 and choose an output cell. If the P-value in the result is below 0.05, at least one group mean is likely different from the others.
Key takeaways
- A one-way ANOVA tests whether three or more group means differ by more than random variation would explain.
- In desktop Excel, use Data > Data Analysis > Anova: Single Factor after turning on the Analysis ToolPak.
- By formula, F = (between-groups SS ÷ (k − 1)) ÷ (within-groups SS ÷ (N − k)), and =F.DIST.RT(F, df1, df2) gives the p-value.
- A small p-value says at least one mean differs, not which one. You need a follow-up test for that.
- ToolPak results are fixed values; run the tool again when the data changes.
On this page+
A one-way ANOVA (analysis of variance) in Excel tells you whether three or more groups have different averages, or whether the differences could just be random. The quickest way is the Analysis ToolPak's Anova: Single Factor tool, which gives an F value and a p-value in one table. You can also build it with AVERAGE, COUNT and a few formulas. This guide shows both on the same data, for students, analysts and anyone comparing groups.
What a one-way ANOVA tells you#
ANOVA compares two kinds of variation:
- Between groups: how far each group's average is from the overall average.
- Within groups: how much the values vary inside each group.
If the between-groups variation is large compared with the within-groups variation, the group averages probably aren't all equal. The ratio of the two is the F statistic, and the p-value says how likely an F at least that large would be if all the true means were the same.
The example uses test scores from 15 learners, trained with three methods:
| A | B | C | |
|---|---|---|---|
| 1 | Method A | Method B | Method C |
| 2 | 72 | 80 | 74 |
| 3 | 75 | 85 | 76 |
| 4 | 78 | 83 | 79 |
| 5 | 70 | 78 | 73 |
| 6 | 80 | 84 | 78 |
The averages are 75, 82 and 76. Method B looks better, but with only five scores per group, is the gap bigger than chance? That's the question ANOVA answers. Comparing averages by group is the first step; ANOVA adds the test.
Step 1: Turn on the Analysis ToolPak#
The ToolPak is an add-in that comes with desktop Excel but is off by default.
- Windows: go to File > Options > Add-ins. At the bottom, choose Excel Add-ins in the Manage box and click Go. Tick Analysis ToolPak and click OK.
- Mac: go to Tools > Excel Add-ins, tick Analysis ToolPak and click OK.
A Data Analysis button now appears at the right end of the Data tab.
Step 2: Run Anova: Single Factor#
- Go to Data > Data Analysis, choose Anova: Single Factor and click OK.
- Input Range: select A1:C6, including the labels.
- Grouped By: choose Columns, since each group is a column.
- Tick Labels in first row.
- Alpha: leave it at 0.05 unless you chose another cut-off in advance.
- Output options: choose New Worksheet Ply to put the result on its own sheet, or pick an empty Output Range well away from the data.
- Click OK.
The output values are fixed numbers, not formulas. If your data changes, run the tool again.
Step 3: Read the ANOVA output#
Excel produces two tables. The SUMMARY table describes each group:
| Groups | Count | Sum | Average | Variance |
|---|---|---|---|---|
| Method A | 5 | 375 | 75 | 17 |
| Method B | 5 | 410 | 82 | 8.5 |
| Method C | 5 | 380 | 76 | 6.5 |
The ANOVA table holds the test:
| Source of Variation | SS | df | MS | F | P-value | F crit |
|---|---|---|---|---|---|---|
| Between Groups | 143.33 | 2 | 71.67 | 6.72 | 0.011 | 3.89 |
| Within Groups | 128 | 12 | 10.67 | |||
| Total | 271.33 | 14 |
What each column means:
- SS (sum of squares): the total squared variation from each source.
- df (degrees of freedom): groups − 1 = 2 for between; values − groups = 15 − 3 = 12 for within.
- MS (mean square): SS ÷ df.
- F: MS between ÷ MS within = 71.67 ÷ 10.67 = 6.72.
- P-value: about 0.011.
- F crit: the F value you'd need to pass the 0.05 cut-off, 3.89 here.
Since the p-value (0.011) is below 0.05, and F (6.72) is above F crit (3.89), the result is significant at the 5% level: the three methods are unlikely to share the same true average.
How to calculate ANOVA with formulas#
Building ANOVA from formulas shows how it works and updates when the data changes.
| Step | Formula | Result |
|---|---|---|
| Grand mean | =AVERAGE(A2:C6) | 77.67 |
| Between-groups SS | =COUNT(A2:A6)*((AVERAGE(A2:A6)-E1)^2+(AVERAGE(B2:B6)-E1)^2+(AVERAGE(C2:C6)-E1)^2) | 143.33 |
| Within-groups SS | =DEVSQ(A2:A6)+DEVSQ(B2:B6)+DEVSQ(C2:C6) | 128 |
| df between | =3-1 | 2 |
| df within | =COUNT(A2:C6)-3 | 12 |
| F | =(143.33/2)/(128/12) | 6.72 |
| p-value | =F.DIST.RT(6.72,2,12) | 0.011 |
| F crit | =F.INV.RT(0.05,2,12) | 3.89 |
The grand mean is in E1 in this layout, as in the exercise on this page. In a real sheet, point the F, p-value and F crit formulas at the cells that hold SS and df instead of typing the numbers.
The between-groups formula multiplies by COUNT(A2:A6) because every group has 5 values. If your groups are different sizes, multiply each squared gap by that group's own count. DEVSQ returns the sum of squared differences from the group's mean, which is the within-groups variation for that group. The AVERAGE function guide and the COUNT function guide cover the building blocks.
How to interpret the result honestly#
A significant ANOVA tells you something, but not everything:
- It doesn't say which group differs. Method B has the highest average, but ANOVA alone doesn't prove B beats both A and C. Pairwise comparisons need a follow-up (post-hoc) test, such as Tukey's HSD, which Excel doesn't include as a built-in tool.
- Significant doesn't mean large. Report the averages and the gap in points, not only the p-value.
- A p-value above 0.05 isn't proof that the means are equal. With small groups, real differences can go undetected.
- Five values per group is a small sample. Treat the example as a demonstration of the method.
Check the assumptions before you trust it#
One-way ANOVA relies on three assumptions:
- Independent observations. Each score comes from a different learner, and no one appears in two groups.
- Roughly normal data within each group. With small groups, look for extreme outliers; a median compared with the average is a quick check for skew.
- Similar spread across groups. A common rule of thumb is that the largest standard deviation shouldn't be more than about twice the smallest. Here they are about 4.1, 2.9 and 2.5, so that's fine. The guide to standard deviation in Excel shows how to calculate them, and STDEV.S vs STDEV.P explains which to use.
Other common mistakes: running several two-group t-tests instead of one ANOVA (which raises the chance of a false positive), selecting labels without ticking Labels in first row, and leaving blank or text cells inside the input range.
Practise it#
ANOVA rests on two everyday skills: averaging groups and counting values correctly. Practise AVERAGE, MIN and MAX in a live spreadsheet, then practise COUNT, COUNTA and COUNTBLANK so you always know how many values each group really has. Try the exercise on this page to calculate the between-groups sum of squares yourself.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Group means are in row 7 and the grand mean is in E1. Click E2 and calculate the between-groups sum of squares: the group size (use COUNT on A2:A6) times the sum of each squared gap between a group mean and the grand mean. Press Enter.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ||||||
| 2 | Between SS | |||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 | ||||||
| 10 |
Tip: Each group has the same size, so multiply COUNT(A2:A6) by (A7-E1)^2 + (B7-E1)^2 + (C7-E1)^2.
Practise this in a lesson
Frequently asked questions
How do I do ANOVA in Excel?
Turn on the Analysis ToolPak, put each group in its own column with a label, then go to Data > Data Analysis > Anova: Single Factor. Select the data, tick Labels in first row, set Alpha and choose where the output goes.
How do I interpret ANOVA results in Excel?
Look at the Between Groups row. If the P-value is below your Alpha, usually 0.05, or F is larger than F crit, the group means are unlikely to all be equal. Then compare the averages in the SUMMARY table to see which groups stand out.
Where is Data Analysis in Excel?
It appears at the right end of the Data tab once the Analysis ToolPak is on. On Windows, turn it on in File > Options > Add-ins > Excel Add-ins > Go. On a Mac, use Tools > Excel Add-ins.
Does ANOVA tell you which group is different?
No. A one-way ANOVA only tells you whether at least one mean differs. To find which pairs differ, you need a follow-up (post-hoc) test, which Excel doesn't include as a built-in tool.
What is the difference between single factor and two factor ANOVA?
Single factor ANOVA compares groups defined by one thing, such as training method. Two factor ANOVA looks at two things at once, such as method and shift, and the ToolPak offers it with or without replication.