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.

- In Excel, click
*Data Analysis*on the Data tab. - From the Data Analysis popup, choose
*F-Test Two-Sample for Variances*. - Under
**Input**, select the ranges for both*Variable 1 Range*and*Variable 2 Range*. - 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. - 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. - 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.

Kenneth Tuttle Wilhelm says

It appears that Excel 2016 calculates the F test for two tails.

Prof C says

Thank you for your explanation of Excel’s F-test for equal variances. However, there is an issue with Excel’s calculation that I was recently made aware of by one of my students. If you note in your results table, the observed F-value (0.13) is smaller than the critical F-value (0.31), but the p-value is quite small (0.003).

Excel recommends that the group with the larger variance be selected as the first group (or first column) and the other group with smaller variance selected as the second column data. This would provide F-calculations that are more consistent with p-values.

Jim Frost says

Hi Prof C,

I don’t think that’s actually a problem. Excel performs only one-tailed tests for variance tests as I describe in this article. In this case, the test is looking in the left (lower) tail. The observed F-value is less than the critical value (0.133 < 0.315). Because it's in the left tail, that places the observed F-value in the critical region. The notation that Excel uses for the p-value doesn't always make sense but for this analysis it does. The p-value is listed as "P(F<=f) one-tail." As it indicates, it's a one-tailed test. Specifically, it's the probability of observing an F-value is less than or equal to the observed F-value, which is the correct definition for the p-value for this test. Additionally, I performed this analysis in my statistics software (Minitab). While Minitab doesn't use F-tests for testing the equality of variances (Levene's and Bonnett's), those tests produce very similar result--p=0.000, which means that p is less than 0.0005. Same neighborhood as p=0.003. If you switched columns as you suggest, I'd assume that Excel would place the critical region in the right tail instead of the left tail. In fact, I just did that and got the same results. Although, I notice that while it did look in the right-hand tail, it did not change the label for the p-value! However, for this test the F-value is 7.48 and the critical value is 3.18. It's still in the critical region and it still has a p-value of 0.003. It just flipped the tails around. Outside of getting the label for the p-value wrong, Excel seems to calculate the correct results either way you set up the test.

Peter says

Hey Jim!

Thank you for some fantastic books, introducing myself and others to the magical world of statistics!

I have a question that I hope you can help me with. The statistical software I use, do not have a function for Bonett Hypothesis test, why I thought I would create one. So far, I’ve done the calculations for the confidence interval and they are spot on (when I use the data you provide in your hypothesis book at page 238 and compare the Bonett confidence interval-result to my own).

However, I can’t seem to figure out how to calculate the p-value. I’ve found documentation at minitab.coms webpage, https://support.minitab.com/en-us/minitab/20/help-and-how-to/statistics/basic-statistics/how-to/1-variance/methods-and-formulas/methods-and-formulas/#hypothesis-test-for-the-chi-square-method, for how to calculate the p-value, but I’m still unsure how it is done, since the equation to solve for alpha contains inverse cumulative probability.

Can you help me out here?

Thanks in advance and thank you for some really good books!

Sincerely,

Peter.

Pierre Celestin Ndayisaba says

I liked it very much

Govinda bhattarai says

Valuable informations

Gebrehanna says

very interesting and important