How to run a one-way ANOVA in Google Sheets

When to use a one-way ANOVA

Use it to compare the means of three or more independent groups on one numeric variable: scores under three teaching methods, sales in four regions, yield with five fertilisers. Comparing the groups two by two with t-tests instead raises the chance of a false positive with every extra test.

The example data

Thirty-six students were split into three groups of twelve, each taught with a different method. Scores are in columns A, B and C, rows 2 to 13, with the group names in row 1.

Step by step

1. Total variation

DEVSQ returns the sum of squared deviations from the mean. Applied to all the data at once, it gives the total sum of squares:

=DEVSQ(A2:C13)

Result: SS total = 1070.22.

2. Variation within the groups

=DEVSQ(A2:A13)+DEVSQ(B2:B13)+DEVSQ(C2:C13)

Result: SS within = 448.50.

3. Variation between the groups

SS between = SS total − SS within = 621.72.

4. Degrees of freedom

df between = number of groups − 1 = 2. df within = =COUNT(A2:C13)-3 = 33.

5. Mean squares, F and p

MS between = 621.72 / 2 = 310.86. MS within = 448.50 / 33 = 13.59. F = 310.86 / 13.59 = 22.87. Then:

=F.DIST.RT(22.87, 2, 33)

Result: p = 5.9 × 10⁻⁷, so p < .001. The critical value is =F.INV.RT(0.05, 2, 33) = 3.28, and 22.87 is far above it.

6. Effect size

Eta squared = SS between / SS total = 621.72 / 1070.22 = η² = 0.58: the teaching method accounts for 58% of the variation in scores. Common benchmarks are 0.01 small, 0.06 medium and 0.14 large.

The finished ANOVA table

This is the same layout as the classic “Anova: Single Factor” output:

Which groups are different? Post-hoc tests

The ANOVA says that at least one mean differs, not which one. A post-hoc test compares every pair while keeping the overall error rate at 5%. For this example, Tukey HSD gives:

Google Sheets cannot compute Tukey p-values with formulas, because it has no function for the studentized range distribution. ExplainStats adds Tukey HSD automatically when the ANOVA is significant, or Games-Howell when variances are unequal.

Check the assumptions

• Independent groups: each person appears in one group only. For repeated measures on the same people, use a repeated-measures design or the Friedman test.
• Normality in each group: test each group with a Shapiro-Wilk test. Here p = .92, .98 and .98, so normality is plausible.
• Equal variances: the standard deviations are 4.21, 3.92 and 2.78. A Brown-Forsythe test gives p = .32, so equal variances are plausible. If they were not, Welch's ANOVA would be the right choice.

If normality clearly fails with small groups, use the Kruskal-Wallis test instead.

How to report it (APA style)

Frequently asked questions

Does Google Sheets have an ANOVA function?

No. There is no built-in ANOVA function or menu in Google Sheets. You can build the table with DEVSQ, COUNT and F.DIST.RT, or use an add-on.

What does F crit mean in an ANOVA table?

F crit is the critical value of F at the chosen significance level, =F.INV.RT(0.05, df between, df within). If F is larger than F crit, the result is significant; this is the same decision as p < 0.05.

Can the groups have different sizes?

Yes. COUNT and DEVSQ ignore empty cells, so the formulas in this guide work with unequal group sizes. The degrees of freedom within are the total number of values minus the number of groups.

How do I know which groups differ?

A significant ANOVA only tells you that at least one mean differs. Use a post-hoc test such as Tukey HSD (equal variances) or Games-Howell (unequal variances) to find which pairs differ.