Microsoft Office Tutorials and References
In Depth Information
50th Percentile =QUARTILE(A2:A26,2)
75th Percentile =QUARTILE(A2:A26,3)
Follow these steps to create the box plot:
1. Select the range F1:J7 and create a line chart with markers.
2. Choose Chart Tools ⇒ Design ⇒ Data ⇒ Switch Row/Column to change the orientation of the chart.
3. Choose Chart Tools ⇒ Design ⇒ Chart Layouts ⇒ Add Chart Element ⇒ Up/Down Bars to add up/down bars
that connect the first data series (25th Percentile) with the last data series (75th Percentile).
4. Remove the markers from the 25th Percentile series and the 75th Percentile series.
5. Choose Chart Tools ⇒ Design ⇒ Chart Layouts ⇒ Add Chart Element ⇒ Lines ⇒ High-Low Lines to add a vertic-
al line between each point to connect the Minimum and Maximum data series.
6. Remove the lines from each of the six data series.
7. Change the series marker to a horizontal line for the following series: Minimum, Maximum, and 50th Per-
8. Make other formatting changes as required.
The chart shown here does not have a legend. I replaced it with a graphic that I made that explains how to read
the chart. The legend for this chart displays the series in the order in which they are plotted — which is not the
optimal order and can be very confusing. Unfortunately, you can't change the plot order because the order is im-
portant (the up/down bars use the first and last series). Creating a descriptive graphic seems like a good alternat-
ive to a confusing legend.
After performing all these steps, you may want to create a template to simplify the cre-
ation of additional box plots. Right-click the chart and choose Save as Template.
Plotting every nth data point
Normally, Excel doesn't plot data that resides in a hidden row or column. You can sometimes use this to your
advantage because it's an easy way to control what data appears in the chart.
Suppose you have a lot of data in a column and you want to plot only every 10th data point. One way to accom-
plish this is to use filtering in conjunction with a formula. Figure 17-11 shows a two-column table with filtering
in effect. The chart plots only the data in the visible (filtered) rows and ignores the values in the hidden rows.