How to Calculate Variance in Excel

You have a column of numbers in Excel or Google Sheets and you need the variance. You type =VAR, and the autocomplete offers VAR.S, VAR.P, VARA, and VARPA. Pick the wrong one and your result is off by a factor of n/(n-1). For a sample of 20 sales figures, that error is about 5 percent, enough to mislead a budget or a portfolio risk calculation. Variance measures how far a set of numbers spread out from their average, in squared units.

Each function, when to use it, and what happens if you do not. It is the average of the squared differences from the mean. The single biggest mistake newcomers make is thinking the square root (standard deviation) is the variance, or that variance itself is in the original units. It is not; it is in squared units, which is why standard deviation exists. In a spreadsheet, you compute variance with one of four functions, and the choice between them depends on whether your data is a sample or a full population.

Every spreadsheet calculation of variance starts with this fork. VAR.S computes sample variance, using n-1 in the denominator (Bessel's correction). VAR.P computes population variance, using n. Microsoft Support documents both formulas: VAR.S divides the sum of squared deviations by (n-1); VAR.P divides by n. The practical rule: if your data set is every member of a group you care about, say, all 50 states' GDP growth rates for a given year, use VAR.P. If your data is a subset intended to estimate a larger group, say, 200 customer satisfaction scores out of 10,000, use VAR.S.

The confusion pair between VAR.S and VAR.P is the single most common source of error in spreadsheet variance work. Using VAR.P on a sample underestimates the true population spread. Over a dataset of 30 points, that underestimation is roughly 3.4 percent. Over 10 points, it is 10 percent. The consequence: you report less risk than actually exists, which matters in finance, quality control, and any setting where spread is a threshold.

Older Excel versions used VAR and VARP. VAR is equivalent to VAR.S; VARP is equivalent to VAR.P. Both still work in current Excel, but Microsoft Support recommends the modern functions for clarity. The difference between VAR and VARA, and between VARP and VARPA, is what they count. VARA and VARPA include text entries as 0, TRUE as 1, and FALSE as 0. Use VARA when your data range might contain a mix of numbers and logical flags, such as a column where "TRUE" indicates a completed transaction. Use VARPA for the same scenario when the data is a population. Microsoft Support covers both VARA and VARPA in their function pages.

The failure case: if your spreadsheet has a text-formatted number that looks like "25" but is stored as a string, VAR.S and VAR.P skip it. VARA and VARPA treat it as 0, which drags the variance down. Always check the data type before picking the function. A quick way: use the ISNUMBER function on a sample cell.

Work Through the Numbers

Open a blank sheet. In column A, enter these 10 numbers: 45, 52, 48, 61, 55, 59, 47, 63, 50, 58. These are daily sales in units. You want to estimate the variance of the larger process that generated them.

In cell B1, type =VAR.S(A1:A10).That is the sample variance in units squared.

Now, in cell B2, type =VAR.P(A1:A10).That is 10 percent lower, which is the bias introduced by using n instead of n-1 on a sample of 10.

To verify the hand calculation, compute the mean (53.8).That matches the VAR.S output. This step-by-step approach confirms the formula works as you expect.

Google Sheets uses the same four-function system. VAR and VAR.S compute sample variance; VARP and VAR.P compute population variance. The Google Docs Editors Help documents all four under a single support page. The formulas are identical to Excel's: VAR.S divides by n-1; VAR.P divides by n.

The practical difference: Google Sheets interprets blank cells differently than Excel. In Excel, blank cells in the range are ignored. In Google Sheets, a blank cell is also ignored, but a cell containing an empty string can be treated as zero depending on how it was entered. If you copy data from a web form into Google Sheets, check for hidden empty strings before running a variance function. Use the COUNTA function to verify your count matches the number of numeric values.

When your data is already grouped into intervals with frequencies, common in survey results or published tables, you cannot feed the midpoints into VAR.S and get a correct answer.

In a spreadsheet, use SUMPRODUCT for both the weighted sum and the weighted sum of squares. Suppose column A holds midpoints (10, 30, 50, 70) and column B holds frequencies (5, 12, 8, 3). The formula for the numerator of the variance becomes: =SUMPRODUCT(B2:B5, A2:A5^2) - (SUMPRODUCT(B2:B5, A2:A5)^2 / SUM(B2:B5)).This method avoids approximation errors from treating grouped data as if every value in a class equals the midpoint, but it is still an approximation, the true variance within each class is lost.

Fix Common Pitfalls

Excel returns #DIV/0! when you give a variance function a range with only one value. VAR.S divides by n-1, so a single data point produces division by zero. VAR.P with a single value returns 0 because n is 1 and the numerator is zero. That is mathematically correct: a single-point population has zero spread.

Text-formatted numbers look like numbers but are not. If you type '45 into a cell (the apostrophe prefix), Excel treats it as text. VAR.S and VAR.P skip it, reducing n by one. The result is a variance computed on fewer points than you intended. The fix: use the VALUE function or the Convert Text to Numbers feature under the Formulas tab. A quick check: =ISNUMBER(A1) returns FALSE for text-formatted entries.