In an Excel PivotTable, Var means sample variance: it measures how spread out the numeric values are around their average, using a sample-based calculation. Choose VarP instead when the values in the PivotTable are the entire population you want to describe.
What Var means in an Excel PivotTable
Var is one of the calculation options for a PivotTable value field. Rather than adding or counting the source numbers, Excel calculates their variance. A larger result indicates greater spread among the numbers; a result of zero means they are all identical. The abbreviated label may appear as Var of Sales or another field name, depending on the source column.
Variance is a measure of dispersion, not a measure of the typical value. A PivotTable showing average sales answers a different question from one showing sales variance: the average describes the center of the values, while variance describes how far the values tend to differ from that center. Variance is expressed in squared units. If the source values are dollars, the variance is in squared dollars, which can be less intuitive to interpret than a standard deviation.
How to show Var in a PivotTable
In Excel for Microsoft 365 on Windows, add the numeric field you want to analyze to the PivotTable’s Values area. Then change its summary calculation:
- Click a value in the PivotTable for the field you want to change.
- Right-click and choose Summarize Values By.
- Select Var from the list.
You can also open Value Field Settings and select Var on the Summarize Values By tab. In that dialog, you can change the custom name—for example, from Sum of Sales to Sales variance—and set the number format. Formatting changes how the result is displayed, not how Excel calculates it.
On a Mac, the Value Field Settings dialog provides the corresponding summary calculation; menu wording and placement can vary with the Excel version. If Var is unavailable or the result does not look right, first check that the field is in Values and that its source entries are numeric.
How the variance calculation works
For a sample, Excel finds the mean, subtracts that mean from each numeric value, squares each difference, adds those squared differences, and divides by one fewer than the number of values. In symbols, the sample variance is the sum of squared deviations divided by n − 1, where n is the count of numeric observations. Var uses this sample calculation.
Dividing by n − 1 rather than n is the standard sample-variance adjustment. It is used when the observations are treated as a sample from a larger group and the goal is to estimate that group’s variance. It does not mean Excel has identified a sample automatically; the choice depends on what your data represents.
VarP uses the population calculation. It divides the sum of squared deviations by n, because all the values in the population of interest are included. The two results are often close when there are many observations, but they can differ noticeably in a small dataset.
Worked example: Var versus VarP
Suppose a field contains three observations: 10, 12, and 14. Their mean is 12. The deviations from the mean are −2, 0, and 2; squaring them gives 4, 0, and 4, for a total of 8. There are three numeric observations.
- Var: 8 divided by 3 − 1 gives a sample variance of 4.
- VarP: 8 divided by 3 gives a population variance of approximately 2.67.
For an independent check in worksheet cells, assume the values 10, 12, and 14 are in A2:A4. The Excel formula =VAR.S(A2:A4) returns 4, matching the sample calculation used by Var. With the same values in A2:A4, =VAR.P(A2:A4) returns approximately 2.666666667, matching the population calculation used by VarP. These formulas can help verify a PivotTable result, but the PivotTable is useful when you want the calculation repeated across categories.
What Var shows when the PivotTable has groups
Rows and columns in a PivotTable divide the source records into groups. If Region is in Rows and Sales is in Values, summarized by Var, Excel calculates the variance of the sales records within each region. It does not calculate the variance of the regional totals unless those totals themselves are the source values being summarized.
For example, a region with sales of 10, 12, and 14 has a sample variance of 4. If another region has sales of 20, 22, and 24, its sample variance is also 4, even though its average is higher. The two groups have the same spread around their own means. A grand-total variance, when displayed, is calculated from the underlying records represented by that total; it is not generally the average of the group variances. Differences in group size and group averages matter, so do not combine variance values by simply averaging them.
This makes Var useful for comparing consistency across categories, such as variation in delivery times by carrier or variation in weekly demand by product. Interpret comparisons with care if the groups have very different sizes or if their underlying populations differ substantially.
Choosing between Var and VarP
Choose the calculation based on the question and the meaning of the records, not on which result seems preferable. Use Var when your records are a sample of a wider process or population—for example, a selection of inspected items intended to represent ongoing production. Use VarP when your records include every member of the population you are describing, such as every transaction in a complete, defined reporting period.
A complete export is not automatically a complete population for every question. All transactions from one month may be the full population for a report about that month, but only a sample if you want to estimate variation in transactions over future months. Decide what the population is before choosing the summary. Var and VarP are descriptive calculations; neither determines whether a sample represents a wider population well.
How to interpret the result—and avoid common mix-ups
Variance is always zero or positive for valid numeric observations. Zero means every included value is the same. A higher value means more squared distance from the group mean, but it is not always meaningful to compare raw variances across fields measured on different scales. For example, variance in dollars cannot be directly compared with variance in minutes as though they measured the same thing.
Variance is also not the same as standard deviation. Standard deviation is the square root of variance and is expressed in the original units, which often makes it easier to explain. A PivotTable has a StdDev summary option for sample standard deviation and StdDevp for population standard deviation. These are related to Var and VarP respectively, but are not interchangeable labels.
One common misconception is that Var reports how far each record is from the average. It reports one summary value for the set of records in each PivotTable group. To see individual differences, calculate or display record-level values separately; a single variance cannot identify which observations are unusually high or low.
Common problems and checks
- Unexpectedly blank or unavailable summary: Confirm that the field is in the Values area and contains numeric data. A field placed in Rows or Columns is used to group the report, not summarized as a value.
- Numbers stored as text: Entries that look like numbers may not be recognized as numeric values. Check the source column, correct the data type, and refresh the PivotTable. Text and blank cells do not contribute numeric observations to a variance calculation.
- Too few observations: Sample variance requires at least two numeric observations. A group with only one numeric value cannot provide a sample variance, so review small groups and the displayed result rather than treating it as a measured spread.
- Stale results: After changing the source data, refresh the PivotTable using PivotTable Analyze > Refresh (the contextual tab may be called Analyze in some layouts). If the PivotTable uses an external source or a data model, its refresh behavior can depend on that connection.
- Wrong field or grouping: Check that the value field is the intended numeric measure and that the row or column categories create the groups you mean to compare. A different filter or grouping changes which observations contribute to each result.
Frequently Asked Questions
Is Var the same as variance in Excel?
Yes. In a PivotTable, Var is Excel’s sample-variance summary for the numeric values in each group. It corresponds to the sample calculation, not the population calculation.
What is the difference between Var and VarP?
Var divides the sum of squared deviations by the numeric count minus one and treats the observations as a sample. VarP divides by the numeric count and treats them as the entire population being described.
Why is my PivotTable variance different from the average variance of its groups?
The total variance is calculated from the underlying records in the total, including differences between group means. It is not generally the simple average of the displayed group variances.
Can I use Var when a group has only one value?
No meaningful sample variance can be calculated from a single observation because there is no spread to estimate from a sample. Check the group size and whether the result is unavailable or an error before drawing conclusions.
Should I use Var or standard deviation?
Use Var when you specifically need variance, such as for a statistical calculation. Use standard deviation when you want a spread measure in the same units as the original data, which is usually easier to communicate.
Related
- Is Excel Free? Microsoft 365 Plans and Free Options
- Excel Value Formatting: What It Changes and How to Use It
- Text to Excel Converter: Import Text Into Columns
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Edit a Pivot Table in Google Sheets: Steps and TipsTo edit a pivot table in Google Sheets, click any cell inside it to open the Pivot table…
- Excel Trendline Start From a Different Point?Yes. In Excel, a trendline is fitted to a chart series, so to start it at a later…
- Create an Excel Dashboard With AI: A Practical GuideYes. To create an Excel dashboard with AI, prepare clean, column-based data, ask Microsoft Copilot or another AI…
- How to Find the Gradient of a Trendline in Excel EasilyTo find a trendline’s gradient in Excel, calculate its slope with SLOPE or display the equation on a…
- Excel Pie Chart Not Working? Fix Common Data IssuesIf an Excel pie chart is blank, incomplete, or showing the wrong slices, first check that its source…
- Histogram in Excel Using Formulas: Build Bins and ChartsTo make a histogram in Excel with formulas, list your data, set bin limits, and use FREQUENCY to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.