# Calculate Variance Using Excel: VAR.S, VAR.P, and Step-by-Step Examples

Published: 2026-03-21
Author: Warren Team
URL: https://www.heywarren.com/blog/calculate-variance-using-excel

---
Variance is a statistical measure of how spread out a set of data points are around their mean — and Excel makes calculating it straightforward with built-in functions. The two most important Excel variance functions are **VAR.S** (sample variance — use when your data is a sample from a larger population) and **VAR.P** (population variance — use when your data represents the entire population). In finance, variance is used to measure investment volatility, portfolio risk, and the reliability of forecasts. In operations, it's used for quality control and budget-to-actual analysis. Understanding which variance function to use — and what the result means — is essential for analysts, finance professionals, and anyone doing data analysis in Excel.

## Sample Variance vs. Population Variance

| Function | Formula | When to Use |
|---|---|---|
| **VAR.S** | =VAR.S(range) | Data is a **sample** from a larger population |
| **VAR.P** | =VAR.P(range) | Data **is** the entire population |
| VAR (legacy) | =VAR(range) | Same as VAR.S; kept for backward compatibility |
| VARP (legacy) | =VARP(range) | Same as VAR.P; kept for backward compatibility |

![VAR.S and VAR.P are the two modern Excel variance functions, each suited to different data contexts.](data:image/svg+xml,%3Csvg%20xmlns%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2Fsvg%22%20viewBox%3D%220%200%20760%20211%22%20width%3D%22760%22%20height%3D%22211%22%20role%3D%22img%22%3E%3Ctitle%3EHierarchy%3C%2Ftitle%3E%3Crect%20width%3D%22100%25%22%20height%3D%22100%25%22%20fill%3D%22%23f8fafc%22%2F%3E%3Crect%20x%3D%22300%22%20y%3D%2220%22%20width%3D%22160%22%20height%3D%2258%22%20rx%3D%228%22%20fill%3D%22%232563eb%22%2F%3E%3Ctext%20x%3D%22380%22%20y%3D%2254%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2214%22%20font-weight%3D%22700%22%20fill%3D%22white%22%3EExcel%20Variance%3C%2Ftext%3E%3Cpath%20d%3D%22M%20380%2078%20L%20380%20105.5%20L%20110%20105.5%20L%20110%20133%22%20stroke%3D%22%23cbd5e1%22%20stroke-width%3D%222%22%20fill%3D%22none%22%2F%3E%3Crect%20x%3D%2230%22%20y%3D%22133%22%20width%3D%22160%22%20height%3D%2258%22%20rx%3D%228%22%20fill%3D%22white%22%20stroke%3D%22%230891b2%22%20stroke-width%3D%222%22%2F%3E%3Ctext%20x%3D%22110%22%20y%3D%22158%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2213%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3EVAR.S%3C%2Ftext%3E%3Ctext%20x%3D%22110%22%20y%3D%22176%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2210%22%20fill%3D%22%2364748b%22%3ESample%20%C3%B7%20%28n%E2%88%921%29%3C%2Ftext%3E%3Cpath%20d%3D%22M%20380%2078%20L%20380%20105.5%20L%20290%20105.5%20L%20290%20133%22%20stroke%3D%22%23cbd5e1%22%20stroke-width%3D%222%22%20fill%3D%22none%22%2F%3E%3Crect%20x%3D%22210%22%20y%3D%22133%22%20width%3D%22160%22%20height%3D%2258%22%20rx%3D%228%22%20fill%3D%22white%22%20stroke%3D%22%230891b2%22%20stroke-width%3D%222%22%2F%3E%3Ctext%20x%3D%22290%22%20y%3D%22158%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2213%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3EVAR.P%3C%2Ftext%3E%3Ctext%20x%3D%22290%22%20y%3D%22176%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2210%22%20fill%3D%22%2364748b%22%3EPopulation%20%C3%B7%20n%3C%2Ftext%3E%3Cpath%20d%3D%22M%20380%2078%20L%20380%20105.5%20L%20470%20105.5%20L%20470%20133%22%20stroke%3D%22%23cbd5e1%22%20stroke-width%3D%222%22%20fill%3D%22none%22%2F%3E%3Crect%20x%3D%22390%22%20y%3D%22133%22%20width%3D%22160%22%20height%3D%2258%22%20rx%3D%228%22%20fill%3D%22white%22%20stroke%3D%22%230891b2%22%20stroke-width%3D%222%22%2F%3E%3Ctext%20x%3D%22470%22%20y%3D%22158%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2213%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3EVAR%20%28legacy%29%3C%2Ftext%3E%3Ctext%20x%3D%22470%22%20y%3D%22176%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2210%22%20fill%3D%22%2364748b%22%3E%3D%20VAR.S%3C%2Ftext%3E%3Cpath%20d%3D%22M%20380%2078%20L%20380%20105.5%20L%20650%20105.5%20L%20650%20133%22%20stroke%3D%22%23cbd5e1%22%20stroke-width%3D%222%22%20fill%3D%22none%22%2F%3E%3Crect%20x%3D%22570%22%20y%3D%22133%22%20width%3D%22160%22%20height%3D%2258%22%20rx%3D%228%22%20fill%3D%22white%22%20stroke%3D%22%230891b2%22%20stroke-width%3D%222%22%2F%3E%3Ctext%20x%3D%22650%22%20y%3D%22158%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2213%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3EVARP%20%28legacy%29%3C%2Ftext%3E%3Ctext%20x%3D%22650%22%20y%3D%22176%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2210%22%20fill%3D%22%2364748b%22%3E%3D%20VAR.P%3C%2Ftext%3E%3C%2Fsvg%3E)

*VAR.S and VAR.P are the two modern Excel variance functions, each suited to different data contexts.*

**The difference**: VAR.S divides by (n−1) — Bessel's correction — to give an unbiased estimate of population variance from a sample. VAR.P divides by n.

**Practical rule**:
- Analysing a sample of monthly returns from a stock's history → VAR.S
- Analysing all test scores in a single classroom (the whole population) → VAR.P
- In finance, VAR.S is almost always correct because you're working with historical samples

## Step-by-Step: Calculate Variance in Excel

**Example dataset**: Monthly returns for a stock (%) over 6 months:
- Jan: 3.2%, Feb: −1.5%, Mar: 5.8%, Apr: 2.1%, May: −0.8%, Jun: 4.4%

**Step 1**: Enter data in a column (A1:A6)

**Step 2**: Calculate the mean: `=AVERAGE(A1:A6)` → **2.20%**

**Step 3a — Sample variance (VAR.S)**:
`=VAR.S(A1:A6)` → **5.89** (percent² — in this case, the variance is in units of %²)

**Step 3b — Manual verification**:

| Month | Return (x) | Deviation (x − x̄) | Deviation² |
|---|---|---|---|
| Jan | 3.2% | +1.0% | 1.00 |
| Feb | −1.5% | −3.7% | 13.69 |
| Mar | 5.8% | +3.6% | 12.96 |
| Apr | 2.1% | −0.1% | 0.01 |
| May | −0.8% | −3.0% | 9.00 |
| Jun | 4.4% | +2.2% | 4.84 |
| **Sum** | | | **41.50** |

VAR.S = 41.50 / (6−1) = **8.30** (Note: slight differences due to rounding in this illustration)

**Step 4 — Standard deviation (more intuitive than variance)**:
`=STDEV.S(A1:A6)` → square root of variance → gives standard deviation in same units as data (%)

## Related Excel Functions

| Function | Description | Use Case |
|---|---|---|
| `STDEV.S(range)` | Sample standard deviation | Most common; same units as data |
| `STDEV.P(range)` | Population standard deviation | When data = full population |
| `AVERAGE(range)` | Mean | Calculate mean before manual variance check |
| `DEVSQ(range)` | Sum of squared deviations | Manual variance step; VAR.S = DEVSQ / (n−1) |
| `COVARIANCE.S(r1, r2)` | Sample covariance | Relationship between two variables |
| `CORREL(r1, r2)` | Correlation coefficient | Standardised covariance (−1 to +1) |

## Variance vs. Standard Deviation: Which to Use?

**Variance** is in squared units (% returns → %² variance) — harder to interpret directly

![Excel variance functions feed into standard deviation by taking the square root of the result.](data:image/svg+xml,%3Csvg%20xmlns%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2Fsvg%22%20viewBox%3D%220%200%20875%20125%22%20width%3D%22875%22%20height%3D%22125%22%20role%3D%22img%22%3E%3Ctitle%3EFlow%20diagram%3C%2Ftitle%3E%3Crect%20width%3D%22100%25%22%20height%3D%22100%25%22%20fill%3D%22%23f8fafc%22%2F%3E%3Crect%20x%3D%2230%22%20y%3D%2225%22%20width%3D%22170%22%20height%3D%2275%22%20rx%3D%2210%22%20fill%3D%22white%22%20stroke%3D%22%232563eb%22%20stroke-width%3D%222%22%2F%3E%3Ctext%20x%3D%22115%22%20y%3D%2258.5%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2214%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3ERaw%20Returns%3C%2Ftext%3E%3Ctext%20x%3D%22115%22%20y%3D%2278.5%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2211%22%20fill%3D%22%2364748b%22%3Ee.g.%20monthly%20%25%3C%2Ftext%3E%3Cline%20x1%3D%22205%22%20y1%3D%2262.5%22%20x2%3D%22237%22%20y2%3D%2262.5%22%20stroke%3D%22%2364748b%22%20stroke-width%3D%222%22%2F%3E%3Cpolygon%20points%3D%22244%2C62.5%20235%2C57.5%20235%2C67.5%22%20fill%3D%22%2364748b%22%2F%3E%3Crect%20x%3D%22245%22%20y%3D%2225%22%20width%3D%22170%22%20height%3D%2275%22%20rx%3D%2210%22%20fill%3D%22white%22%20stroke%3D%22%232563eb%22%20stroke-width%3D%222%22%2F%3E%3Ctext%20x%3D%22330%22%20y%3D%2258.5%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2214%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3EVAR.S%3C%2Ftext%3E%3Ctext%20x%3D%22330%22%20y%3D%2278.5%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2211%22%20fill%3D%22%2364748b%22%3Esquared%20units%20%28%25%C2%B2%29%3C%2Ftext%3E%3Cline%20x1%3D%22420%22%20y1%3D%2262.5%22%20x2%3D%22452%22%20y2%3D%2262.5%22%20stroke%3D%22%2364748b%22%20stroke-width%3D%222%22%2F%3E%3Cpolygon%20points%3D%22459%2C62.5%20450%2C57.5%20450%2C67.5%22%20fill%3D%22%2364748b%22%2F%3E%3Crect%20x%3D%22460%22%20y%3D%2225%22%20width%3D%22170%22%20height%3D%2275%22%20rx%3D%2210%22%20fill%3D%22white%22%20stroke%3D%22%232563eb%22%20stroke-width%3D%222%22%2F%3E%3Ctext%20x%3D%22545%22%20y%3D%2258.5%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2214%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3ESTDEV.S%3C%2Ftext%3E%3Ctext%20x%3D%22545%22%20y%3D%2278.5%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2211%22%20fill%3D%22%2364748b%22%3E%E2%88%9Avariance%3C%2Ftext%3E%3Cline%20x1%3D%22635%22%20y1%3D%2262.5%22%20x2%3D%22667%22%20y2%3D%2262.5%22%20stroke%3D%22%2364748b%22%20stroke-width%3D%222%22%2F%3E%3Cpolygon%20points%3D%22674%2C62.5%20665%2C57.5%20665%2C67.5%22%20fill%3D%22%2364748b%22%2F%3E%3Crect%20x%3D%22675%22%20y%3D%2225%22%20width%3D%22170%22%20height%3D%2275%22%20rx%3D%2210%22%20fill%3D%22white%22%20stroke%3D%22%232563eb%22%20stroke-width%3D%222%22%2F%3E%3Ctext%20x%3D%22760%22%20y%3D%2258.5%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2214%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3EStd%20Deviation%3C%2Ftext%3E%3Ctext%20x%3D%22760%22%20y%3D%2278.5%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2211%22%20fill%3D%22%2364748b%22%3Esame%20units%20as%20data%3C%2Ftext%3E%3C%2Fsvg%3E)

*Excel variance functions feed into standard deviation by taking the square root of the result.*

**Standard deviation** (square root of variance) is in the same units as the original data — much more intuitive

**When to use variance directly**:
- Portfolio variance calculations: σ²p = w₁²σ₁² + w₂²σ₂² + 2w₁w₂ρσ₁σ₂ (must use variance, not standard deviation, in this formula)
- ANOVA (Analysis of Variance) and regression analysis
- When mathematically combining multiple sources of variability

**When standard deviation is more useful**:
- Describing the spread of investment returns (e.g., "this stock has a 15% annual standard deviation")
- Visualising data distributions and comparing to benchmarks

## Calculating Portfolio Variance in Excel

For a two-asset portfolio, Excel can calculate portfolio variance using:

`=w1^2*VARP(r1)+w2^2*VARP(r2)+2*w1*w2*COVARIANCE.P(r1,r2)`

Where w1, w2 are portfolio weights and r1, r2 are the return arrays for each asset.

## Authoritative Sources

For deeper background and primary-source data on this topic, the following authoritative sources are useful starting points:

- [IRS](https://www.irs.gov/)
- [SEC](https://www.sec.gov/)

## Conclusion

Excel's VAR.S calculates sample variance (use this for most financial analysis) and VAR.P calculates population variance. The square root — STDEV.S — is the standard deviation and typically more intuitive. Variance is foundational to portfolio risk analysis, budget variance analysis, and any statistical work in finance. For related quantitative finance concepts, see our guides on [DuPont formulas](/blog/dupont-formulas) and [EBITDA margin](/blog/ebitda-margin).

Warren at [heywarren.com](https://heywarren.com) helps analysts, finance professionals, and students use Excel for financial analysis — from basic variance calculations to portfolio risk modelling and statistical forecasting.

---


## Related Reading

**More from Warren**:
- [DuPont Formula: Breaking Down ROE into Its Three Components](/blog/dupont-formulas)
- [EBITDA Margin: What It Is, How to Calculate It, and What It Tells You](/blog/ebitda-margin)
- [YoY Means: What Year-Over-Year Growth Tells You](/blog/yoy-means)

**Authoritative sources**:
- [Microsoft Excel — VAR.S Function Documentation](https://support.microsoft.com/en-us/office/var-s-function)
- [CFA Institute — Quantitative Methods](https://www.cfainstitute.org/en/membership/professional-development/refresher-readings)
- [Khan Academy — Statistics and Probability](https://www.khanacademy.org/math/statistics-probability)