XL: How to Create a BoxPlot/Box & Whisker Chart

ID: Q155130


The information in this article applies to:
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a


SUMMARY

Microsoft Excel charts do not include a BoxPlot/Box & Whisker chart type. However, you can create a reasonable representation of this type of chart by following the steps outlined in this article.


MORE INFORMATION

To create a sample BoxPlot/Box & Whisker chart, use the following steps:

In Microsoft Excel versions 5.0 and 7.0

  1. Type the following data into a new worksheet:
    
           A1: Statistic   B1: a    C1: b    D1: c
           A2: median      B2: 40   C2: 45   D2: 50
           A3: q1          B3: 20   C3: 22   D3: 30
           A4: min         B4: 10   C4: 15   D4: 18
           A5: max         B5: 100  C5: 110  D5: 90
           A6: q3          B6: 70   C6: 75   D6: 57 


  2. Select cells A1:D6, and on the Insert menu, point to Chart and then click On This Sheet.


  3. Click and drag the area for the chart, and in Step 1 of 5 of the ChartWizard, click Next.


  4. In the Step 2 of 5 dialog box, click the Combination chart type and click Next.


  5. In the Step 3 of 5 dialog box, click the sixth chart style and click Next.

    An Alert Box appears with the following warning:
    A volume-open-high-low-close stock chart must contain five series


  6. Click OK.


  7. In the Step 4 of 5 dialog box, click the Rows under "Data Series in:" and then click Next.


  8. In the Step 5 of 5 dialog box, click No under "Add a Legend?" and then click Finish.


  9. Double-click the chart to activate it. On the Insert menu, click Axes, and clear the check box next to "Value (Y) Axis" under Secondary Axis, and then click OK.


  10. Click once on any one of the colored columns to select the series. Do not click one of the white columns.


  11. On the Format menu, click Chart Type, click Line, and then click OK.

    A line that connects the three white columns appears in the chart.


  12. Click once on the line, and on the Format menu, click Selected Data Series.


  13. Click the Patterns tab, and under Line, click None.


  14. Under Marker, click Custom, click the plus sign in the Style list, click the black color in the Foreground list, click None in the Background list, and then Click OK.



REFERENCES

For more information about creating charts in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type:

how do I create a chart
For more information about Creating Charts in Microsoft Excel version 5.0, choose the Search button in Help and type:
charts

Additional query words: 5.00a 5.00c 7.00a

Keywords : kbtool xlchart
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0,7.0a
Platform : MACINTOSH WINDOWS
Issue type : kbhowto kbinfo


Last Reviewed: October 5, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.