ANOVA in Excel: one-way analysis of variance

In short: one-way ANOVA lives under Data, Data Analysis, Anova: Single Factor, once the Analysis ToolPak is switched on. It compares the variation between groups with the variation within groups, and the ratio of the two is the F value. If the p-value is below 0.05, at least one group mean differs from the others.

Before you start: variance and VAR.S from the standard deviation lesson, and the p-value from the t-test lesson.

Try it now: calculate the F value

This table is the heart of every ANOVA output: sums of squares (SS) and degrees of freedom (df) between and within the groups. Divide each sum of squares by its degrees of freedom to get the mean square (MS). F is the mean square between groups divided by the mean square within groups. Click the yellow cell and type the formula.

Task This table comes from a one-way ANOVA with three groups: SS is the sum of squares, df the degrees of freedom. In F2, calculate the F value: the mean square between groups divided by the mean square within groups.

	A	B	C	D	E	F
1	Source	SS	df
2	Between groups	166.53	2		F value
3	Within groups	86.4	12

What F means

The numbers come from the layout test: three layouts with five weeks each, group means of 43.4, 51.4 and 46.0, and a grand mean of 46.93.

Part	Meaning	In the example
SS between	How far the group means sit from the grand mean, squared and weighted by group size	166.53
SS within	How far each value sits from its own group mean, squared and summed	86.4
df	Groups minus 1 (between) and values minus groups (within)	2 and 12
MS	Mean square: SS divided by df	83.27 and 7.2
F	MS between divided by MS within	11.56
P-value	Probability of an F at least this large if all groups were really the same	0.0016

If the layouts made no real difference, both mean squares would be about the same size and F would be close to 1. Here the group means vary more than eleven times as much. A p-value of 0.0016 says an F this large would be very unlikely with identical layouts. The 5% critical value for 2 and 12 degrees of freedom is 3.89, and anything above it is significant.

How to do ANOVA in Excel with the Analysis ToolPak

1. Enable the add-in once: File, Options, Add-ins. In the Manage box choose Excel Add-ins, select Go and tick Analysis ToolPak. On a Mac: Tools, Excel Add-ins.
2. Put your data in columns, one column per group, with headers in the first row.
3. On the Data tab, select Data Analysis, then Anova: Single Factor.
4. Set the Input Range to the whole table including headers, choose Grouped By Columns and tick Labels in First Row.
5. Leave Alpha at 0.05, pick an output range and click OK.

Excel writes two tables. The SUMMARY table lists Count, Sum, Average and Variance for each group. The ANOVA table has the rows Between Groups, Within Groups and Total, and the columns SS, df, MS, F, P-value and F crit. The output is plain numbers: it does not recalculate when your data change.

One more exercise: the within-groups sum of squares

To see where the numbers come from, rebuild them with formulas. The sum of squares within each group equals (n-1) times its sample variance, which is exactly what VAR.S works out before it divides. The standard deviation lesson covers variance in more detail.

Task A retailer is testing three store layouts; the table shows weekly revenue in $k. In G2, calculate the sum of squares within groups. A shortcut from statistics: for each group it equals (n-1) times the sample variance, so here 4 times VAR.S of the column.

	A	B	C	D	E	F	G
1	Week	Layout A	Layout B	Layout C
2	1	42	51	46		SS within
3	2	47	48	43
4	3	39	55	49
5	4	45	50	47
6	5	44	53	45

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

The between-groups sum of squares follows the same idea: =5*((AVERAGE(B2:B6)-AVERAGE(B2:D6))^2+(AVERAGE(C2:C6)-AVERAGE(B2:D6))^2+(AVERAGE(D2:D6)-AVERAGE(B2:D6))^2) returns 166.53. F follows as in the first exercise, and in Excel itself =F.DIST.RT(11.56,2,12) gives the p-value of about 0.0016. The practice sheets on this site do not include F.DIST.RT, so for the p-value rely on the ToolPak output.

What an ANOVA does not tell you

A significant result only means that at least one layout differs. A one-way ANOVA does not say which one. A simple follow-up is pairwise t-tests with a Bonferroni correction: with three comparisons, use 0.05 / 3, about 0.017, as the cutoff. Statistics packages offer dedicated post hoc tests such as Tukey's, which Excel does not have built in. In this data, layout B at 51.4 is clearly ahead.

Assumptions

• The measurements are independent of each other.
• The values in each group are roughly normally distributed, or the groups are not too small.
• The groups have a similar spread. Compare the variances in the SUMMARY table.

Common mistakes

Several t-tests instead of one ANOVA

Every extra test raises the chance of a significant result by pure luck. With more than two groups, run the ANOVA first.

Text in the input range

Apart from the headers, the input range must contain numbers only. Without Labels in First Row ticked, Excel treats the headers as data and shows an error.

No Data Analysis button

The button only appears once the Analysis ToolPak is enabled.

ANOVA in German Excel

English	German
Anova: Single Factor	Anova: Einfaktorielle Varianzanalyse
Analysis ToolPak, Data Analysis	Analyse-Funktionen, Datenanalyse
=F.DIST.RT(11.56,2,12)	=F.VERT.RE(11,56;2;12)
F.INV.RT, F.TEST, VAR.S	F.INV.RE, F.TEST, VAR.S

Which Excel versions can run an ANOVA?

The Analysis ToolPak is an add-in that ships with Excel for Windows and Mac, and Microsoft describes how to enable it on both. Microsoft lists F.DIST.RT, F.INV.RT and F.TEST as new in Excel 2010.

FAQ