# How to calculate variance in Excel

Use `=VAR.S(B2:B7)` for a sample, or `=VAR.P(B2:B7)` for a whole population.

**Excel:** `=VAR.S(B2:B7)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Student | Score |  | Variance |
| A | 72 |  |  |
| B | 85 |  |  |
| C | 90 |  |  |
| D | 64 |  |  |
| E | 78 |  |  |
| F | 81 |  |  |

Formula in E1: `=VAR.S(B2:B7)`

Result: 86.67

## How it works

Variance is the average squared distance from the mean (78.33 here). VAR.S adds the squared differences (433.3) and divides by n − 1 = 5, giving 86.67. It is in squared units, which is why the standard deviation, its square root, is easier to read.

## Other ways

- `=STDEV.S(B2:B7)^2`: Variance is the standard deviation squared.
- `=VAR.P(B2:B7)`: For a complete population. (Divides by n: 72.22.)

## Common mistakes

- In budgeting, "variance" means actual minus budget, which is a subtraction, not VAR.S. See the first FAQ.
- VAR (without .S) is the older name for VAR.S and gives the same result.

## Questions

**How do I calculate a budget variance?**

Subtract: `=actual-budget`, and divide by the budget for a percentage variance.

**How do I get the standard deviation from the variance?**

Take the square root: `=SQRT(VAR.S(B2:B7))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/calculate-variance