How to Test Variances in Excel

Use a variances test to determine whether the variability of two groups differs. In this post, we’ll work through a two-sample variances test that Excel provides. Even if Excel isn’t your primary statistical software, this post provides an excellent introduction to variance tests. Excel refers to this analysis as F-Test Two-Sample for Variances.

Variance tests are a type of hypothesis test that allows you to compare group variances. Variance is a measure of the spread, or variability, within a dataset. Like all hypothesis tests, variance tests use sample data to infer the properties of an entire population. Do the data in the groups have different spreads?

In this post, I provide step-by-step instructions for using Excel to conduct a two-sample variance test and interpreting the statistical results. I also include links to supplementary information I’ve written.

Before proceeding, ensure that Excel’s Data Analysis ToolPak is installed for Excel. Look for Data Analysis as shown below.

If you don’t see Data Analysis, you’ll have to install that ToolPak. Learn how to install it in my post about using Excel to perform t-tests. It’s free!

F-test for Two-Sample Variances in Excel

In general, variances tests assess the variability of the data in multiple groups to determine whether they are different. Variance is a measure of variability that uses squared units, which makes it hard for us humans to interpret. However, various statistical procedures include variances in their calculations. The standard deviation is a more common measure of variability, and it is simply the square root of the variance. Standard deviations are much easier to interpret because they use the same units as the original data.

Analysts frequently use F-tests to assess the differences between group means in analysis of variance (ANOVA). However, F-tests are very flexible tests that evaluate the ratio of two variances. By changing the variances in the numerator and denominator, analysts can use F-tests to assess a diverse array of properties, such as the overall statistical significance of a regression model to the differences between group means. For variance tests, we’ll use the F-test to determine whether two variances are different.

Excel can perform only two-sample variance tests, which assesses variances for precisely two groups. However, other types of tests can compare variability for more groups.

Typically, you perform this hypothesis test to determine whether two population variances are different. This form of the test uses independent samples. In other words, each group contains different people or items.

From Excel’s Data Analysis popup, choose F-test Two-Sample for Variances.

Related post: Measures of Variability

Hypotheses in Variances Tests

The standard two-tailed two-sample variance tests use the following hypotheses:

• Null: The two population variances are equal.
• Alternative: The two population variances are not equal.

If the p-value is less than your significance level (e.g., 0.05), you can reject the null hypothesis. The difference between the two variances is statistically significant. This condition indicates that your sample provides strong enough evidence to conclude that the variability in the two populations are different. In other words, their spreads differ. Hypothesis tests, like variance tests, allow you to use samples to draw conclusions about populations.

Unfortunately, Excel provides a p-value for only the one-tailed form of the variances test. One-tailed tests can detect differences between means in only one direction. For example, a one-tailed test might determine only whether Group A’s variability is greater than Group B’s variability. Two-tailed tests can detect differences in either direction—greater than or less than. There are additional drawbacks to using one-tailed tests. I wish Excel provided both one-tailed and two-tailed results as it does with two-sample t-tests.

Related posts: Hypothesis Testing Overview and One and Two-Tailed Hypothesis Tests

Performing the Two-Sample Variances Test in Excel

For this example, I’ll use data from my blog post about a Mythbusters’ Battle of the Sexes episode. In that episode, the Mythbusters evaluate whether men or women are better at parallel parking.

The Mythbusters have ten subjects per group and use a parking test that produces scores between 0 and 100. The mean difference between men and women is not statistically significant.

However, while testing the subjects, the hosts noticed that the women’s parallel parking skills appear to be more variable than the men’s abilities. The graph below shows how women have a broader range of scores than men.

While the spread of these two groups looks very different, let’s use Excel’s variances test to determine whether this difference is statistically significant.

To perform a two-sample variance test in Excel, arrange your data in two columns, as shown below. Download the CSV file that contains the data for this example: VariancesTest.

1. In Excel, click Data Analysis on the Data tab.
2. From the Data Analysis popup, choose F-Test Two-Sample for Variances.
3. Under Input, select the ranges for both Variable 1 Range and Variable 2 Range.
4. Check the Labels checkbox if you have meaningful variable names in row 1. This option makes the output easier to interpret. Ensure that you include the label row in step #3.
5. Excel uses the default Alpha value of 0.05, which you usually won’t need to change. Alpha is also known as the significance level.
6. Click OK.

For this example, the popup should look like the following:

After Excel creates the statistical output, I autofit column A to display all of it.

Interpreting the Test Results

The output indicates that the variance for Males is 131.3778, and for Females it is 982.2778. The difference between these numerical measures of variances corresponds to the difference we observed in the graph. The p-value is the most important statistic. If you want to learn about the other statistics, read my posts about F (i.e., the F-value), df (degrees of freedom), and critical values.

For our results, we’ll use the P(F<=f) one-tail row, which is the p-value for the one-tailed form of the variances test. Because our p-value (0.003094) is less than the standard significance level of 0.05, we can reject the null hypothesis. Our sample data support the hypothesis that the population variances are different. Women are more variable at parallel parking than men.

What Excel’s Variances Test Does Not Include

Excel’s only variances test is the F-test. F-tests can assess only two groups and are susceptible to departures from normality. However, other two-sample tests are less sensitive to departures from normality, such as Bonett’s and Levene’s tests. Levene’s test is particularly suitable for small samples (>20) and skewed data.

If you have more than two groups, use Bartlett’s or Levene’s test to evaluate their variances. Bartlett’s test is more sensitive to the normality assumption than Levene’s test.

As I mentioned earlier, Excel, strangely, only offers the one-tailed test result, which is often inappropriate.

Finally, it would be nice if Excel converted the variances to standard deviations and displayed confidence intervals. The variances that Excel displays are not intuitive. Additionally, they are only the point estimates from the samples. Confidence intervals help you determine a range of values that the population parameter is like to fall within.