Variance and Box Plots in Excel: What Each Shows

Excel’s Box and Whisker chart does not show variance by default. Create the chart to compare medians, quartiles, spread, and outliers, then calculate variance separately with VAR.S or VAR.P and display the result beside the chart.

What variance and a box plot describe

Variance and box plots both describe variation, but they summarize data in different ways. Variance is a single number calculated from the squared distances between observations and their mean. It is useful for comparing how dispersed datasets are, provided you use the same units and the same definition of variance.

A box plot, also called a box-and-whisker plot, is a visual summary. Its box spans the first and third quartiles, and a line inside the box marks the median. Whiskers extend to the non-outlier values under the chart’s whisker rule, while any observations classified as outliers can appear as separate points. The chart lets you compare distribution shape and typical spread across groups, but it does not calculate or label variance.

This distinction matters because a box plot is based on order and quartiles, whereas variance uses every observation’s distance from the mean. Two datasets can have similar boxes but different variances, especially if one contains extreme values. Conversely, the same variance does not guarantee that two groups have similar medians, quartiles, or outliers.

Create a Box and Whisker chart in Excel

In Excel for Microsoft 365 on Windows, arrange each group in its own column, with a group name in the first row and observations underneath. If groups have different numbers of observations, leave unused cells blank rather than filling them with zeros; zeros would be treated as real data and change the distribution.

Select the group names and the observation cells, including the headers.
Choose Insert > Insert Statistic Chart > Box and Whisker.
If Excel has interpreted the rows and columns in the wrong direction, select the chart and use Chart Design > Switch Row/Column.
To adjust the display, right-click a box and choose Format Data Series. In Series Options, review controls such as Show mean markers, Show mean line, Show inner points, and Show outlier points.

On Excel for Mac, the equivalent chart is available from Insert > Statistical Chart > Box & Whisker; some ribbon labels and pane layouts can differ from Windows. Excel for the web may not expose every chart-creation or formatting control, so use the desktop app if you cannot find the statistical chart or quartile options.

Use descriptive headers and a chart title that identifies the measure and groups. For example, a plot comparing delivery times should say whether values are minutes, hours, or days. The chart does not know what a blank cell means to your analysis, so check the source data before interpreting the result.

Read the chart’s quartiles and whiskers

The box’s lower and upper edges represent the first and third quartiles, while the line inside represents the median. The distance from the first quartile to the third is the interquartile range, or IQR. A compact box suggests the middle half of a group’s observations is relatively concentrated; a taller box indicates a wider middle spread.

Excel’s box-and-whisker chart can identify points beyond the usual 1.5-IQR fences as outliers and show them separately when outlier points are enabled. The whiskers do not necessarily extend to the absolute minimum and maximum when those extremes are plotted as outliers. Check the chart options and the underlying data before describing a whisker as the full range.

Excel offers inclusive-median and exclusive-median quartile calculations. These methods can produce different quartile boundaries, particularly in small datasets. To inspect or change the method, right-click the plotted series, choose Format Data Series, and review Quartile Calculation. Keep the same method when comparing charts or reporting results, and state it when the exact quartile values matter.

A mean marker is an optional addition, but it is not a variance marker. It may help show how the mean differs from the median, which can be informative when a distribution is skewed. It does not replace a separate variance calculation.

Calculate sample or population variance

Choose the function that matches the question. VAR.S calculates sample variance, using the data as a sample from a larger population. VAR.P calculates population variance when the listed observations are the entire population of interest. These functions return values in squared units: if the original measurements are in centimeters, variance is in square centimeters.

With the eight observations in A2:A9, use this formula for sample variance: =VAR.S(A2:A9). With those same observations, the formula returns approximately 4.5714.

With the eight observations in A2:A9, use this formula for population variance: =VAR.P(A2:A9). For the example below, it returns 4.

To calculate variance separately for multiple groups, place each group in its own column and enter the appropriate function in a summary cell for each column. For example, if Group A is in B2:B20 and Group B is in C2:C20, calculate one variance from B2:B20 and another from C2:C20. Keep the formulas outside the chart’s observation range so Excel does not mistake a summary value for an additional observation.

Worked example: a small dataset

Suppose A2:A9 contains 2, 4, 4, 4, 5, 5, 7, and 9. The mean is 5. The squared distances from that mean add to 32: the observations contribute 9, 1, 1, 1, 0, 0, 4, and 16. For the population, divide 32 by all eight observations to get variance 4. For a sample, divide by seven, one fewer than the number of observations, to get approximately 4.5714.

The same values can be placed in one column and used to create a Box and Whisker chart. The chart will summarize their median, quartiles, and any points treated as outliers; it will not label the chart with 4 or 4.5714. That is expected, not a chart error. The two variance results differ because one describes the entire stated population and the other estimates spread from a sample.

This example also shows why variance can feel less intuitive than the plot: the numerical result is in squared units, while the data and the box plot are in the original units. If readers need a spread measure in the same units as the observations, standard deviation is the square root of variance. The box plot remains useful alongside it because it reveals distribution features that a single spread statistic cannot.

Show variance beside the chart without confusing it

A clear presentation is usually a box plot with a small summary table next to it. Include group name, sample size, variance type, and variance value. State whether the values are sample or population variance, and label units carefully; writing square units prevents readers from mistaking variance for a measurement in the original scale.

Avoid adding variance as another observation in the box-plot source data. Variance is calculated from the observations and is expressed in squared units, so plotting it alongside the raw measurements would mix different quantities and could distort the chart’s scale. Similarly, adding a variance value as a point on the same axis can imply a direct comparison that is not meaningful.

If you want a compact visual report, put the variance in a neighboring cell or a separate summary table, then make the relationship obvious through nearby labels. You can also show the mean marker on the box plot, but explain that the marker is the mean rather than variance. A separate table is typically easier to read and maintain than trying to force a statistic into a chart type that was not designed to display it.

Common problems and interpretation checks

Using the wrong function: VAR.S and VAR.P answer different questions. Decide whether your observations represent a sample or the complete population before reporting a value.
Including blanks as zeros: Empty cells are not the same as zero-valued measurements. Check that missing entries have not been replaced with zero unless zero is genuinely observed.
Comparing groups on different scales: Variance changes with measurement scale. Converting measurements from meters to centimeters multiplies variance by 10,000, even though the underlying observations describe the same quantities.
Assuming a wider box means higher variance: The box shows the middle half of the data, while variance uses all observations and their distances from the mean. Outliers can have a large effect on variance.
Confusing a mean marker with variance: A mean marker is an average value in the original units. Variance is a separate calculation in squared units.
Changing quartile rules between charts: Inclusive and exclusive calculations can shift box edges. Use a consistent setting for fair visual comparisons.

Variance cannot be negative. If a result seems impossible or unexpectedly large, verify the selected cells, units, and function, and look for unusually extreme values. A formula range that accidentally includes a header or a summary cell can also lead to confusing results.

Frequently Asked Questions

Can Excel’s box plot show variance automatically?
No. Excel’s Box and Whisker chart displays distribution summaries such as the median, quartiles, whiskers, and optional points or mean markers. Calculate variance in a worksheet cell and show it in a nearby table or label.

Should I use VAR.S or VAR.P?
Use VAR.S when the observations are a sample and VAR.P when they represent the full population you are describing. The correct choice depends on what the data represent, not on which result looks more useful.

Is variance the same as the interquartile range?
No. The IQR is the third quartile minus the first quartile and describes the middle half of ordered observations. Variance measures squared distances from the mean using all included observations.

Why does my box plot not show every minimum and maximum?
Points beyond the chart’s outlier fences may be shown separately, leaving the whiskers to end at the most extreme non-outlier values. Check whether outlier points are displayed and inspect the source data.

Can two groups have the same variance but different box plots?
Yes. Variance is one summary of spread, while a box plot also shows median position, quartile spacing, and potential outliers. Groups with the same variance can have different distribution shapes and centers.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.