How to Find Variance in Excel: Formulas and Examples

To find variance in Excel, enter =VAR.S(B2:B6) for a sample or =VAR.P(B2:B6) for an entire population, replacing the range with your data. In Microsoft Excel for Microsoft 365 on Windows, type the appropriate formula in an empty cell and press Enter. The result measures how spread out the numbers are around their average. Before choosing a function, decide whether your values are a sample or the complete group you want to describe.

What You’ll Need

An Excel workbook with the numerical observations arranged in a row or column. A single column is easiest to check, but a rectangular range also works.
A clear decision about whether the observations represent a sample or the whole population. This determines whether to use VAR.S or VAR.P.
An empty cell where Excel can display the result. The formula can also go on another worksheet in the same workbook if you prefer to keep calculations separate.

You do not need an add-in or a special analysis setting. VAR.S and VAR.P are built-in statistical functions in Microsoft 365. The same formulas work in current Excel for Mac and Excel for the web; the menus may look different, but the formula syntax is the same. These instructions use comma separators as shown. In some regional settings Excel may display semicolons instead, depending on the system’s list separator.

Step-by-Step

Decide what group your data represents. Use sample variance when your values are a subset selected from a larger group and you want to estimate how variable that larger group is. For instance, measurements from 30 randomly selected products might be a sample from all products made that month. Use population variance when the numbers include every member of the group you are describing, such as the scores of every student in one class. The choice is about the meaning of the data, not how many rows are in the sheet.
Arrange and check the observations. Put one observation in each cell in a single column or row, and keep labels out of the range you will calculate. For example, if the heading is in B1 and the five measurements are in B2 through B6, select only B2:B6 in the formula. Check that values intended as numbers are stored as numbers rather than text. Blank cells are ignored by these functions, while a numeric zero is a real observation and is included. Do not remove a zero merely because it makes the variance look different.
Select an empty result cell. Click a blank cell outside the data, such as D2, and type an equals sign followed by the function name. You can type the range yourself or select the cells with the mouse while entering the formula. Avoid placing the result inside the range being analyzed: including the output cell can create a circular reference or make the range include the calculated answer. If you want a label, put it in the adjacent cell, not in the numeric input range.
Calculate sample variance when your data is a sample. With the five observations in B2:B6, enter =VAR.S(B2:B6) in D2 and press Enter. For the example values 2, 4, 6, 8, and 10, Excel returns 10. Their mean is 6, and the sum of squared differences from that mean is 40; sample variance divides 40 by one less than the number of observations, or 4. If your data occupies another range, replace B2:B6 with the actual range, keeping the colon between the first and last cell.
Calculate population variance when you have the whole group. With the same values in B2:B6, enter =VAR.P(B2:B6) in another empty cell, such as D3, and press Enter. Excel returns 8 for the example. The squared deviations still total 40, but population variance divides by all five observations. If your five values are only part of a larger set, do not choose this function just because all five are visible in the workbook; use VAR.S if they are a sample of that larger set.
Check that the result makes sense. Variance cannot be negative for valid numeric data. It is zero when all included values are identical, and it grows as values become more dispersed. Because variance is based on squared differences, its units are squared: if the source data is in centimeters, the variance is in square centimeters. A result that seems large may be reasonable when the data has a wide range, so compare it with the scale of the observations and confirm the sample-versus-population choice before changing the data.
Keep the calculation readable. Add a nearby label such as Sample variance or Population variance so another person can interpret the result later. You can widen the result column if the number displays as a row of number signs, or adjust decimal places using the number-format controls on the Home tab. Formatting changes how the result is shown, not the underlying calculation. For a variance comparison between groups, use the same type of variance function and consistent measurement units for each group.

Excel’s formula autocomplete may show a function suggestion as you type. You can choose VAR.S or VAR.P from the suggestion list, then enter the range inside the parentheses. Press Enter to calculate. On Windows, F2 edits the selected cell’s formula; on Mac, use Control+U to edit the active cell. These shortcuts are optional: selecting the formula bar and editing there works as well. On Excel for the web, enter the formula directly in the cell or formula bar, as you would in the desktop app.

Common Mistakes

Choosing the wrong denominator. VAR.S and VAR.P can return different answers for the same values. VAR.S divides the squared deviations by the number of observations minus one; VAR.P divides by the number of observations. Do not switch functions just to get a preferred-looking result. Decide whether the data is a sample or a complete population first.
Including a heading or the result cell. A text heading is generally ignored in a referenced range, but including it can hide a range-selection mistake. Including the formula cell itself can cause a circular reference. Select only the cells containing observations.
Removing zero values or treating blanks as zeros. A zero is included as a numeric observation, but an empty cell is ignored. These are not interchangeable. If a blank actually means zero in your source data, enter a zero only when that interpretation is correct.
Expecting variance to use the original units. Variance is in squared units, not the units used for the measurements. If you want a measure of spread in the original units, use a standard deviation function instead; standard deviation is the square root of variance.
Rounding or typing over source data too early. Display formatting can make a value look rounded without changing its stored value. Avoid replacing the original observations with rounded versions unless that is intentional, because doing so can change the calculated variance.

Tips

Keep raw observations in one clearly identified range and put summary calculations elsewhere. This makes it easier to audit the cell references and update the calculation when new observations arrive.
Use the formula bar or click the result cell to confirm which range the formula references. If you add more data later, check whether the range includes the new rows; a formula that still ends at B6 will not automatically include a new value in B7.
For a range that will grow, consider converting the data into an Excel table using Insert > Table. Table references can make formulas easier to maintain, but confirm the reference Excel inserts and keep headings separate from the observation values.
When reporting a result, state whether it is sample or population variance and include the relevant units squared. A bare number is difficult to interpret when someone reviews the workbook later.
If the result is needed for a plain-language comparison, consider showing standard deviation as well. It is expressed in the same units as the original observations, which can be easier to explain. Keep the variance calculation available if it is required for a statistical method or report.

Troubleshooting

You see #DIV/0! with VAR.S. Sample variance needs at least two numeric observations. Check whether the selected range contains two or more numbers, and whether the cells that look numeric are actually stored as numbers. VAR.P also returns an error when there are no numeric observations in the referenced range; with one valid numeric value, population variance is zero.
You see #NAME? or a function-name error. Check the spelling and punctuation of the function name and make sure the formula begins with an equals sign. In current Excel, use VAR.S or VAR.P. Older workbooks may contain VAR or VARP, which are legacy function names retained for compatibility; use the newer names for new calculations.
The result is unexpectedly zero or much smaller than expected. Confirm that the range includes all intended values and that the observations are not identical. Check whether numbers imported from another file are stored as text; a cell can look like a number while being excluded from a referenced calculation. Select a suspicious cell and check its alignment or try entering the value again as a number.
The result changes when you edit the data. That is expected if an included value changes. If you add a new observation and the result does not change, inspect the formula’s range and extend it to include the new cell. If you did not expect a change, look for accidental edits, hidden rows, or values that were pasted into the range.
Excel rejects commas in the formula. Some regional configurations use semicolons as the argument separator. If Excel reports a formula error when you type the comma-separated example, use the separator shown in your Excel installation or by its function helper. The function name and range logic remain the same.
The result looks like #######. This is usually a display-width issue rather than a variance error. Widen the column by dragging its right border or double-clicking the border between its column letters. You can also reduce displayed decimal places, while remembering that formatting does not change the stored result.

Frequently Asked Questions

Should I use VAR.S or VAR.P?
Use VAR.S for a sample intended to represent a larger group. Use VAR.P when the range contains the entire population you want to describe. The right choice depends on the question and how the data was collected.

Can I calculate variance for data arranged across a row?
Yes. Both functions accept a row range as well as a column range. Select the cells containing the observations and exclude any labels or unrelated values.

Does Excel include blank cells and zeros?
Blank cells in a referenced range are ignored, while numeric zeros are included. If a cell contains text that looks like a number, Excel may not treat it as a numeric observation, so check imported data if the result seems wrong.

Why is my variance not in the same units as my data?
Variance squares each difference from the mean, so the result has squared units. For example, measurements in meters produce variance in square meters. Standard deviation is in the original units.

Can I use the same functions in Excel for Mac or the web?
Yes. VAR.S and VAR.P are available in current Excel for Mac and Excel for the web, and the formulas work the same way. The interface and keyboard shortcuts can differ, but typing the function and selecting its range is essentially the same.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.