Peltier Technical Services, Inc.
- Custom Excel Solutions
Jon's Excel and Charting Pages
- Jon's Charts
- Tutorials
- Excel
- Site Index and Search
- RSS
|
Peltier Technical Services, Inc. Jon's Excel and Charting Pages |
|
Vertical Box and Whisker Plots.
Box and Whisker charts are commonly used in the display of statistical analyses. Unfortunately, Microsoft Excel does not have a built in Box and Whisker chart type. You can create your own custom Box and Whisker charts, using stacked bar or column charts and error bars, in combination with line or XY scatter chart series to show additional data. This tutorial shows the procedures used to construct a vertical box and whisker plot. In this example, the boxes show medians and upper and lower quartiles of the data, while the whiskers indicate the minimum and maximum values. This tutorial also shows how to add another series representing the means of the data. Note: there are several different ways that box and whisker plots depict sample data. The whiskers may alternatively show a more rigorously defined upper control limit, and special markers may be used to indicate outliers beyond these. Any such alternatives are beyond the scope of this exercise. We'll start with the following statistical data, which was calculated from sample data (not shown here):
Select the Green cells (A2:D2), hold down Ctrl while selecting the Yellow cells (A10:D12). A discontiguous range is now selected. Run the Chart Wizard and create a stacked column chart.
Don't select a stacked 100% column chart, which normalizes values to a total stack height of 100%.
Let's do a little simple formatting. Format the plot area: White fill, Black border. Remove the gridlines. Remove excess decimal digits in the value axis number format.
Now we'll add the error bars for min and max values. Select the "75th Pct" Series (light yellow), and press Ctrl-1 (numeral "one") to open the Format Series dialog. Select the Y Error Bars tab. Click in the Custom + (plus) box, and with the mouse choose the Orange colored range (B14:D14). Select the "25th Pct" Series (dull blue), press Ctrl-1 (numeral "one") to open the Format Series dialog. Select the Y Error Bars tab. Click in the Custom - (minus) box, and with the mouse choose the Pink colored range (B13:D13).
Set the minimum scale of the value axis to 20 to better fit the display. Make the "25th Pct" series invisible: format it (Ctrl-1) to have No Border, No Area Fill.
Select the Green and Blue ranges (A2:D3), Copy. Select the chart (not a series), Paste Special with the following options: Add Series, By Row, Series Names in First Column, Categories in First Row.
Right click the new series, select Chart Type... and Choose the Line Chart style, Line with Markers substyle.
Format the remaining series according to your favorite formats.
To add another set of markers, indicating averages from another population, values from a particular sample, or a set of targets, add the new series name and values below the data table (A15:D15). Add the series as you added the Mean above. Select the green range (A2:D2), then hold CTRL while selecting the added data in A15:D15, copy this two-area range, select the chart, and use Edit menu > Paste Special with the following options: Add Series, By Row, Series Names in First Column, Categories in First Row. You can add the new series as another set of markers.
You can also add the series as a horizontal line segment to denote some kind of target. First, change the new series to an XY type (Chart menu > Chart Type > XY Type), format the series to display no markers or lines, but add short positive and negative error bars. The error bars shown have a Fixed Value of 0.2, but you may need to adjust yours to get the desired appearance.
BoxWhiskerH.html shows a similar procedure to construct a horizontally oriented Box and Whisker plot. BoxWhisker.zip is a zipped Excel workbook with examples of and step-by-step instructions for vertically and horizontally oriented Box and Whisker plots. A utility that constructs Box and Whisker charts is described in PTS Box and Whisker Chart Utility. Brian Kelly, of Dublin, Ireland, has suggested a modified Box and Whisker chart. This version has two sets of error bars, one indicating the 10th and 90th percentiles, the other showing the minimum and maximum values. The min and max error bars, drawn here without the cross at the end, consist of the error bars shown in my exercise. The 10th and 90th percentile error bars, shown here with crosses, are constructed in the same manner, and applied to the Median series.
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Peltier Technical Services, Inc. Jon's Excel and Charting Pages Peltier Technical Services, Inc., Copyright © 2008. All rights reserved. |
Waterfall Chart
Box and Whisker
