Making a Box-and-Whiskers Plot with Excel
1. Enter your data sets in the columns of the spreadsheet, with headings.
2. You will need to find the values for the Minimum, Q1, Median, Q3, and Maximum. You
can set up the Excel spreadsheet to calculate these for you. Set it up like this;
A / B / C / D / E / F1 / Data Set 1 / Data Set 2 / Data Set 1 / Data Set 2
2 / Q1
3 / MEDIAN
4 / Q3
5 / MIN
6 / MAX
3. Click on Cell E2, then choose Formulas Insert Function. In the dialog box, select statistical
category and QUARTILE. Click OK.
4. A new box will appear. For “Array”, highlight all of the data for one of your variables.
For “Quart”, type in “1”. Your value for Quartile 1 will appear in cell E2.
5. Continue inserting the functions for data set 1. Follow the same instructions in step 4 except change the number for “Quart”, typing in 2 for median, 3 for Q3, 0 for minimum, and 4 for maximum.
6. Highlight cells E2- E6 and F2-F6. In the Home tab, choose and then Right. This will drag the functions to the next cells and calculate these values for the second data set.
7. Make new column headings for each of the data sets in columns G and H. Fill them in according to this chart:
D / E / F / G / H1 / Data Set 1 / Data Set 2 / Data Set 1 / Data Set 2
2 / Q1 / 21 / 13 / Q1 / Q1
3 / MEDIAN / 24 / 14 / MEDIAN - Q1 / MEDIAN - Q1
4 / Q3 / 26 / 15.5 / Q3 - MEDIAN / Q3 - MEDIAN
5 / MIN / 20 / 12 / Q1 - MIN / Q1 - MIN
6 / MAX / 28 / 18 / MAX - Q3 / MAX - Q3
8. Select cells G2:H4. Select the Insert tabColumn 2D Column Stacked Column.
On the tool bar, choose Switch Row/Column. On the graph that appears click on the small box that
lists series and delete it.
9. On the tool bar, select Chart Layouts. Click on the down arrow and choose the layout that has a title and axes labels. Click Select Data tab, then Horizontal Axis Labels Edit. Type in the names of your categories, separated by commas.
10. Format the plot area by right clicking on the plot area. Choose Format Plot Area and choose a
Black border and White fill.
11. Place your cursor on the top shaded area of a bar(Q3). On the tool bar, choose Layout Error bars more error bar options. Check the box next to “plus” and “Custom”, then“specify value”. For “positive error value”, highlight cells G6:H6 on the spreadsheet (Max – Q3). DO NOT change negative error value. Click on the icon to return to the dialog box and click OK.
12. Place your cursor on the bottom shaded area of a bar (Q1). On the tool bar, choose Layout Error bars more error bar options. Check the box next to “minus” and “Custom”, then“specify value”. For “negative error value”, highlight cells G5:H5 on the spreadsheet (Q1-Min). DO NOT change positive error value. Click on the icon to return to the dialog box and click OK.
13. Right click on the bottom shaded area of a bar and choose Format Data Series. Choose Fill and then No Fill.
14. Right click on each of the remaining shaded areas and change their fill color so that they are the same color. Also make sure they each have a border that is darker than their fill.
15. If you want to change the width of the boxes; right click on a box and choose Format Data Series. Increasing the value on the gap width will decrease the width of the box.