One-way ANOVA in Excel: the F test and p-value, step by step

What a one-way ANOVA tests

The null hypothesis H₀ says that all group means are equal: the display a store uses makes no difference to how many pastries it sells. The alternative is that at least one mean differs. "One-way" means there is one factor (the display) with several levels (A, B, C and D), and each store belongs to exactly one group.

Why not run t-tests on every pair? Four groups give six pairs, and with six tests at the 5% level the chance of at least one false alarm is about 1 − 0.95⁶ = 26%. ANOVA asks one question about all groups at once and keeps the error rate at 5%.

Worked example: four pastry displays in 24 stores

Rows 2 to 7 are pastries sold per day, six stores per display. Rows 9 to 11 summarise each group, rows 14 to 17 are the ANOVA table and rows 19 to 24 the test. The data are fictional.

Worked example: four pastry displays in 24 stores

	A	B	C	D	E
1	Store	Display A	Display B	Display C	Display D
2	Store 1	42	50	40	45
3	Store 2	36	43	45	39
4	Store 3	47	54	35	48
5	Store 4	40	46	42	43
6	Store 5	45	52	46	41
7	Store 6	38	48	38	47
8
9	Group mean	41.33	48.83	41.00	43.83
10	Variance (VAR.S)	17.47	16.17	17.60	12.17
11	Within SS (DEVSQ)	87.33	80.83	88.00	60.83
12
13	Grand mean				43.75
14	Source	SS	df	MS	F
15	Between groups	235.50	3	78.50	4.95
16	Within groups	317.00	20	15.85
17	Total	552.50	23
18
19	p-value (F.DIST.RT)				0.0099
20	Critical F at 5% (F.INV.RT)				3.10
21	Eta squared (SSB ÷ SST)				42.6%
22	Omega squared (less biased)				33.1%
23	Check: SSB + SSW − SST				0.00
24	Decision at 5%				Reject H0

The p-value of 0.00988 is shown rounded to four decimals. The ToolPak's Anova: Single Factor gives the same table.

Step 1: group means and the grand mean

Put each group in its own column, as in the example, or keep the data in long format with one row per store and a group label, and use AVERAGEIF. The group means are 41.33, 48.83, 41.00 and 43.83 pastries a day, and the grand mean of all 24 stores is 43.75. Display B looks better, but the stores within each group also differ by a lot, from 43 to 54 for display B alone. ANOVA asks whether the gap between the means is large compared with that noise.

Step 2: sums of squares with DEVSQ

DEVSQ returns the sum of squared deviations from the mean, which is exactly what ANOVA needs:

• SS within: =DEVSQ(B2:B7) for each group, added up: 317.0. This is the noise inside the groups.
• SS between: the squared distance of each group mean from the grand mean, weighted by the group size. With six stores per group, =COUNT(B2:B7)*DEVSQ(B9:E9) = 235.5. With groups of different sizes use =SUMPRODUCT(n,(means-grand mean)^2) instead.
• SS total: =DEVSQ(B2:E7) over all 24 stores = 552.5.

SS between + SS within must equal SS total. The check row in the sheet makes sure no store was left out of a range.

Step 3: degrees of freedom, mean squares, F and the p-value

The degrees of freedom are k − 1 = 3 between the groups and N − k = 24 − 4 = 20 within them. Dividing each sum of squares by its degrees of freedom gives the mean squares: MS between = 235.5 ÷ 3 = 78.5 and MS within = 317 ÷ 20 = 15.85. If H₀ were true, both would estimate the same noise and F would be close to 1. Here F = 78.5 ÷ 15.85 = 4.95.

The p-value is the right tail of the F distribution: =F.DIST.RT(4.95,3,20) = 0.0099. It is below 0.05, so you reject H₀. The same decision with the critical value: =F.INV.RT(0.05,3,20) = 3.10, and 4.95 is above it. Report it as F(3, 20) = 4.95, p = 0.010.

The Data Analysis ToolPak shortcut

Excel also has a ready-made one-way ANOVA: enable the Analysis ToolPak (File, Options, Add-ins), then choose Data, Data Analysis, Anova: Single Factor, select the four columns and set alpha to 0.05. It prints the same table, with the SS, df, MS, F, p-value and F crit columns, and the numbers match the formulas above.

The difference is that the ToolPak writes static values. Change one store and the table is out of date, while the formula version updates at once and shows how every number is built. Use the formulas for a model you will reuse, and the ToolPak to check them.

Effect size, assumptions and the next step

A p-value says whether the means differ, not by how much. Eta squared = SS between ÷ SS total = 42.6% of the variation in sales is explained by the display. With small groups eta squared overstates the effect, so many reports use omega squared, (SSB − df between × MS within) ÷ (SST + MS within) = 33.1%, which is still a large effect.

ANOVA assumes independent observations, roughly normal data in each group and similar variances. The group variances here run from 12.17 to 17.60, a ratio of about 1.45, which is well inside the common rule of thumb of 4 (Levene's test is the formal check). For clearly skewed data or ordinal ratings, a rank-based test is safer, the same idea as the Mann-Whitney U test for two groups.

A significant F does not say which displays differ. That is the job of a post-hoc test such as Tukey's HSD, which compares every pair while keeping the overall error rate at 5%.

Common mistakes

• Running many t-tests instead. Six pairwise tests at 5% have about a 26% chance of at least one false positive. Run the ANOVA first and a post-hoc test after it.
• Using the left tail. F.DIST(F,df1,df2,TRUE) returns the left tail. The ANOVA p-value is F.DIST.RT, or 1 minus the cumulative value.
• Swapping the degrees of freedom. The first df belongs to the numerator (between groups, k − 1), the second to the denominator (within groups, N − k). Swapped, the p-value is wrong.
• The equal-size shortcut with unequal groups. n × DEVSQ(means) only works when every group has the same size. Otherwise weight each squared deviation by its own group size.
• Reading "significant" as "large". With large samples a tiny difference becomes significant. Report eta or omega squared with the p-value.
• Stopping at the F test. A significant F only says that some means differ. Use Tukey's HSD or Bonferroni-adjusted tests to find which pairs.