Excel Format Data Series Pane Series That Shows

He is a former member of the American Statistical Association and has taught statistics at the undergraduate, honors undergraduate, and graduate levels.

Instead, you can cajole a type of Excel chart into boxes and whiskers.

Instead of showing the mean and the standard error, the box-and-whisker plot shows the minimum, first quartile, median, third quartile, and maximum of a set of data.

Statisticians refer to this set of statistics as a five-number summary.

The box is bounded on the top by the third quartile, and on the bottom by the first quartile.

The whiskers are error bars: One extends upward from the third quartile to the maximum, and the other extends downward from the first quartile to the minimum.

The following figure shows the worksheet and the statistics.

You can use MIN to find the minimum value for each year, and MAX to find the maximum value.

QUARTILE.INC computes the first quartile and the third quartile.

In a stacked column, each segments size is proportional to how much it contributes to the size of the column.

In a box-and-whisker box, however, the size of a segment represents a difference between one value and another like the difference between the quartile and the median, or between the median and the first quartile.

The second is the difference between the median and the first quartile.

The third is the difference between the third quartile and the median.

The figure also shows the chart toolset to right of the chart.

With the bars corresponding to Q1 selected (the lowest portion of each stacked column), click the Plus Sign in the chart toolset.

Select the Minus radio button, the Cap radio button, and the Custom radio button.

Leaving the Positive Error Value as is, specify the cell range for the Negative Error Value.

This time select the part of the stacked columns corresponding to Q3-Median (the upper portion of each stacked column).

This time, specify the cell range for the Positive Error Value.

In the Format Data Series panel, select Fill and select the No Fill radio button in the Fill area.

Click the Color Button and select black from the Theme Colors palette.

Excel Format Data Series Pane Series That Shows

You can add another data series that shows where the means are, and another that would allow me to connect the medians, but this is enough for now.

Then select another data series in the chart and start formatting it.

Excel For Mac Format Data Series Pane