Quick answer
The VAR.S formula estimates variance for a sample.
=VAR.S(B2:B100)Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=VAR.S(number1, [number2], ...)When to use it
Statistics formulas help turn raw data into summary numbers. They are useful for dashboards, survey analysis, quality checks, sales reports, performance tracking, and operational reviews.
- Count rows that match a rule
- Find average performance
- Rank top products or employees
- Measure variation in results
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Group, status, or dimension |
| B | Numeric value |
| C | Summary result |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Basic VAR.S example
=VAR.S(B2:B100)Start with a clean numeric range and verify the result manually on a few values.
Ignore blank rows
=VAR.S(B2:B100)Most summary formulas ignore blank cells, but check whether zeros should be included.
Apply one condition
=COUNTIF(A2:A100,"Open")Use criteria to summarize only the rows you need.
Apply multiple conditions
=COUNTIFS(A2:A100,"West",C2:C100,"Open")Use multiple criteria for dashboard filters and operational reports.
Use with FILTER
=VAR.S(B2:B100)Calculate the statistic after dynamically filtering the source data.
Protect reports from empty filters
=IFERROR(VAR.S(B2:B100),"")Return blank when no rows qualify.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- Blank cells and zeros are treated differently, so check which ones should be included.
- Criteria text does not match because of extra spaces or spelling differences.
- The summary range and criteria range are not the same size.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the VAR.S formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.