If you want to determine if there are significant differences between groups of data, you can perform an analysis of variance (ANOVA). The XL Toolbox’ ANOVA feature offers several advantages over the ANOVA that comes with Excel’s Analysis Toolpak: It accepts data in a number of different table layouts, and it can compute post-hoc tests.


If you are new to ANOVA, the article in Wikipedia is a good starting point right at your finger tips. There are of course numerous excellent books on statistics.

Data input

Unlike many statistical packages, the XL Toolbox can accept data in a variety of layouts.

Group data can be arranged in columns or in rows (see figure, left side).

Alternatively, you can arrange your data as an unordered list (see figure, right). Of course, such an unordered list can also be oriented horizontally instead of vertically.

Daniel’s XL Toolbox will collect the correct data for each group and then perform an ANOVA.

ANOVA Assumption of Homogeneity of Variance

The XL Toolbox can test if the data meets the basic assumption of equal variance (homoscedasticity). It employs a modified version of Levene’s test, as described by Glantz & Slinker in “Primer of Applied Regression & Analysis of Variance”.

The test transforms the data by obtaining the absolute difference between each value and the corresponding group median (not the mean as in the original method), and performs an analysis of variance on the transformed data.

If the variances of the groups are statistically significantly different (p < 0.05), the test “fails” and you should interpret the ANOVA with extreme caution.

The Toolbox does currently not offer an ANOVA on ranks, which would be the alternative method to use if the assumption of homoscedasticity is not met.

Data for ANOVA in columns (left) and rows (right).

Data for ANOVA as an unordered list.

Multiple comparisons/”Post-hoc” testing

The ANOVA makes a general statement about whether or not there are significant differences between the data groups. Once the result is positive (p < 0.05), you need to employ additional tests to find out which of the multiple comparisons that are possible for your data set are significant. So-called posthoc tests perform multiple comparisons of a set of several data groups.

Screenshot of the available posthoc methods

The XL Toolbox supports the following methods:

  • Bonferroni-Holm: Generally the most conservative of all tests. In certain situations, you may end up having a “significant” ANOVA but not finding any “significant” differences between the groups using the Bonferroni-Holm test.
  • Holm-Sidak: Principally the same algorithm as the Bonferroni-Holm, but somewhat less conservative.
  • Tukey: The least conservative of the three, i.e. the one the most likely to detect significant differences in multiple comparisons.

The XL Toolbox uses algorithms according to Jerrold H. Zar, Biostatistical Analysis.

Important note regarding the Tukey test

The Tukey test requires computation of the so-called studentized range. The corresponding statistic is q, and Excel by itself provides no means to compute a p value from it. Therefore, the XL Toolbox uses an algorithm from StatLib, the library of statistical algorithms of the Royal Statistical Society (algorithm no. 190).

The computation of a p value from a given q is an approximation which should be sufficiently accurate for most applications. Note however that it slightly differs from the computation of the statistics software package “R” (function ptukey).

Here is an example of some arbitrary computations:

q Deg. of freedom No. of groups p Diff.
1.34715 9 3 0.377056 (Toolbox)
0.377143 (R)
(0.02 %)
2.88675 9 3 0.841966 (Toolbox)
0.842072 (R)
(0.01 %)
0.67934 25 5 0.0114509 (Toolbox)
0.0115197 (R)
(0.6 %)

In conclusion, if you have critical analyses, or if the p values of the multiple comparison are close to the critical alpha level, you should make sure to double-check your results with external software (such as the R-Project, which is freely available).


The ANOVA results that are presented on screen are very brief and lack detail. For a complete report, click on “Produce report”. A new worksheet will be inserted into your workbook.


It is important to realize that the XL Toolbox’ ANOVA currently does not perform tests for one assumption of an analysis of variance, i.e. normality of the data.

It is not trivial to determine the probability of normal distribution of a given data set. Furthermore, it is debatable whether a test for normal distribution makes sense for a typical biomedical data set with rather few data points. Thus, it is unlikely that the XL Toolbox will be able to do this in the future. If you need to know whether your data is normally distributed, you are kindly referred to specialized software packages, such as R, or SPSS®.