Written and maintained by CASRAI Editorial Board
Last updated
Excel gives you two routes to an F-test for two variances: the F.TEST function and the F-Test Two-Sample for Variances tool in the Analysis ToolPak. Both ask the same question: are the variances of two samples different enough that they probably do not come from populations with equal variance? One detail trips people up. F.TEST returns a two-tailed probability, not the F statistic itself, so if you want F (the number that appears in a write-up) you compute it separately. This guide shows both routes, reproduces Microsoft’s own worked example by hand so you can see exactly what F.TEST returns, and covers the errors you are likely to meet.
What it is used for in research
An F-test for two variances compares the spread of two groups. In the NIST/SEMATECH e-Handbook of Statistical Methods, the null hypothesis is that the two population variances are equal (σ₁² = σ₂²). The alternative can be two-sided (not equal) or one-sided (σ₁² < σ₂² or σ₁² > σ₂²). The test statistic is the ratio of the sample variances, F = s₁² / s₂², and, in the handbook’s words, the more this ratio deviates from 1, the stronger the evidence for unequal population variances.
Researchers typically run it to check whether two groups have similar variability, for example before choosing between a pooled-variance and an unequal-variance two-sample t-test. The F-statistic you meet in ANOVA is a different application of the same distribution; see how to interpret the F-statistic for that case.
Install or access (official source only)
F.TEST is a built-in worksheet function and needs no setup. The Data Analysis tool needs the Analysis ToolPak add-in, which Microsoft documents on its support page “Use the Analysis ToolPak to perform complex data analysis”:
- Excel for Windows: File > Options > Add-Ins. In the Manage box select Excel Add-ins, click Go, tick Analysis ToolPak, and click OK.
- Excel for Mac: Tools > Excel Add-ins, then tick Analysis ToolPak.
Once it is loaded, the Data Analysis command appears on the Data tab.
Step-by-step task
Route 1: the F.TEST function
- Put the first sample in one column (for example A2:A6) and the second in another (B2:B6).
- In an empty cell, enter
=F.TEST(A2:A6,B2:B6). Both arguments (array1 and array2) are required. - Read the result as a two-tailed p-value (see the next section).
Per Microsoft’s documentation of F.TEST, the function returns the two-tailed probability that the variances in array1 and array2 are not significantly different. Arguments must be numbers, or names, arrays or references containing numbers. Text, logical values and empty cells are ignored; cells containing zero are included.
Route 2: the F-Test Two-Sample for Variances tool
- Load the Analysis ToolPak as described above.
- On the Data tab choose Data Analysis.
- Select F-Test Two-Sample for Variances from the list, which Microsoft describes as performing a two-sample F-test to compare two population variances.
- Enter the two input ranges and an output location, then confirm.
Route 3: F statistic in Excel by formula
To get the F statistic itself (the “f statistic in excel” most people are after), divide the two sample variances. With the samples in A2:A6 and B2:B6:
- F statistic:
=VAR.S(A2:A6)/VAR.S(B2:B6) - Degrees of freedom: each sample size minus 1, so
=COUNT(A2:A6)-1and=COUNT(B2:B6)-1 - Lower-tail probability:
=F.DIST(F, df1, df2, TRUE), where F, df1 and df2 are the cells holding the values above. Microsoft documentsF.DIST(x,deg_freedom1,deg_freedom2,cumulative)as returning the F probability distribution; with cumulative set to TRUE it returns the cumulative distribution function.
Output and how to read it
What F.TEST returns (a two-tailed p-value, not F)
The number in the cell is a probability. A small value (for example below your chosen α, such as 0.05) is evidence that the two variances differ; a large value means the data do not show a difference in variance. It does not tell you which sample is more variable.
Worked example you can reproduce
Microsoft’s F.TEST page uses A2:A6 = 6, 7, 9, 15, 21 and B2:B6 = 20, 28, 31, 38, 40, and states that =F.TEST(A2:A6,B2:B6) returns 0.64831785. The same number comes out of the manual route:
| Quantity | Sample A | Sample B |
|---|---|---|
| Mean | 11.6 | 31.4 |
| Sample variance (n − 1) | 39.8 | 64.8 |
| Degrees of freedom | 4 | 4 |
F = 39.8 / 64.8 ≈ 0.6142. With 4 and 4 degrees of freedom, the cumulative probability F.DIST(0.6142,4,4,TRUE) is about 0.3242. Doubling the lower-tail probability gives 2 × 0.3242 ≈ 0.6483, which matches the 0.64831785 that Microsoft reports for F.TEST. This is consistent with F.TEST being a two-tailed result, and a two-tailed result does not depend on which range you list first. The manual ratio and the ToolPak tool do depend on which variance is the numerator, so state the order in your write-up.
Reading the example: a p-value of 0.65 gives no evidence that the variances differ, which is unsurprising with five observations per group.
Reporting it
Report F, both degrees of freedom and the p-value, for example: F(4, 4) = 0.61, two-tailed p = 0.65. If you used the F-Test Two-Sample for Variances tool, report the F statistic and probability it gives you, and state whether you used a one-tailed or two-tailed reading.
The F-test for two variances is built on an assumption of normality. R’s documentation for var.test describes its F test as comparing the variances of two samples “from normal populations”, so check that assumption first; see normality testing in Excel. For comparing variances across more than two groups, see Levene’s test.
Troubleshooting
| Symptom | Cause according to Microsoft’s F.TEST page |
|---|---|
#DIV/0! |
Either array has fewer than 2 data points, or the variance of either array is zero. |
| Result looks like it ignored some rows | Text, logical values and empty cells in the ranges are ignored. Cells containing zero are included, so a stray 0 changes the variance. |
| Data Analysis is missing from the Data tab | The Analysis ToolPak is not loaded; enable it as shown under Install or access. |
For the F.DIST function used in the manual route, Microsoft documents that a nonnumeric argument returns #VALUE!, a negative x returns #NUM!, non-integer degrees of freedom are truncated, and degrees of freedom below 1 return #NUM!.
F.TEST versus F-Test Two-Sample for Variances
Both compare two variances, but they deliver different things. F.TEST is a single worksheet formula returning a two-tailed p-value that updates when the data change. The ToolPak tool is a menu-driven analysis that writes its results to the output location you choose. If you need the F statistic and degrees of freedom for a report, compute them with the formulas in Route 3 (the ratio is F = s₁² / s₂², with each sample size minus 1 as its degrees of freedom).
Free alternatives
| Tool | Function | What the documentation says |
|---|---|---|
| Excel (worksheet) | F.TEST(array1,array2) |
Returns the two-tailed probability that the variances are not significantly different. |
| Excel (Analysis ToolPak) | F-Test Two-Sample for Variances | Performs a two-sample F-test to compare two population variances. |
| R | var.test(x, y, ratio = 1, alternative = c("two.sided", "less", "greater"), conf.level = 0.95, ...) |
Performs an F test to compare the variances of two samples from normal populations. Returns the F statistic, degrees of freedom, p-value and the ratio of the sample variances. |
R is free software. For a one-sided alternative, var.test takes an alternative argument; in Excel, derive the one-sided probability from F.DIST as in Route 3.
FAQ
What does F.TEST return in Excel?
The two-tailed probability that the variances in the two arrays are not significantly different. It is a p-value, not the F statistic.
How do I get the F statistic in Excel?
Divide one sample variance by the other, for example =VAR.S(A2:A6)/VAR.S(B2:B6), or use the F-Test Two-Sample for Variances tool. Degrees of freedom are each sample size minus 1.
Is “f.test in excel” the same as “f-test in excel”?
F.TEST is the worksheet function; “F-test” is the statistical procedure, which Excel also offers through the Analysis ToolPak. For two variances they test the same hypothesis.
Does the order of the two ranges matter?
Not for F.TEST, which returns a two-tailed result. It does matter when you compute F = s₁² / s₂² by hand, because swapping the samples inverts the ratio.
Why do I get #DIV/0!?
One of the arrays has fewer than 2 data points, or one of the variances is zero.
What should I do after an F-test?
The result informs your choice of t-test conditions. For comparing means across several groups, see ANOVA in Excel, and for other worksheet statistics, Excel statistics functions.
Sources (official docs)
All sources checked 2026-10-29.
- Microsoft Support, “F.TEST function”: https://support.microsoft.com/en-us/office/f-test-function-100a59e7-4108-46f8-8443-78ffacb6c0a7
- Microsoft Support, “Use the Analysis ToolPak to perform complex data analysis”: https://support.microsoft.com/en-us/office/use-the-analysis-toolpak-to-perform-complex-data-analysis-6c67ccf0-f4a9-487c-8dec-bdb5a2cefab6
- Microsoft Support, “F.DIST function”: https://support.microsoft.com/en-us/office/f-dist-function-a887efdc-7c8e-46cb-a74a-f884cd29b25d
- NIST/SEMATECH e-Handbook of Statistical Methods, “F-Test for Equality of Two Variances”: https://www.itl.nist.gov/div898/handbook/eda/section3/eda359.htm
- R Documentation, “var.test: F Test to Compare Two Variances”: https://stat.ethz.ch/R-manual/R-devel/library/stats/html/var.test.html
