Making a Box and Whisker Plot in Excel

Making a Box and Whisker Plot

Getting the Data Ready

Step 1: Prepare the Data for the Chart. Insert several blank rows to set up range for calculations. This is represented by cells A16 – G24 in the example above.

Step 2: Calculate the data needed for construction of the box plots.

Step 3: Copy these calculations to columns C through G. Cells C17 through G24 in the example above.

Step 4: Determine the values to plot. This is represented by cells A27 through G31. In the above example.

A: The bottom of the lower box rests on the first quartile.

B: Each box plot in the chart above has a box for the second quartile, which shows the difference between the median and first quartile calculated in the “Calculations” table above.

C: Each box plot also has a box for the third quartile, which shows the difference between the third quartile and the median calculated in the “Calculation” table above.

D: The down whisker is as long as the first quartile minus the minimum value

E: The up whisker is as long as the maximum value minus the third quartile.

Step 5: Copy these calculations to columns C through G. Cells C27 through G31 in the example above.

Now you are ready to start building the chart.

Making the Chart

Step 1: Create your Chart. Highlight cells associated with Bottom, 2Q, 3Q (cells A27 through G29 in the example above) and click “Insert” tab above. In the “Charts” menu click “Column” and select “2D Stacked Column Chart”.

Step 2: Add the Down Whisker. Select the bottom series (blue boxes), click “Chart Tools” and click “Layout Tab”. Click “Error Bars” and select “More Error Bar Options” from the bottom of the menu. Under the “Vertical Error Bars” section select the “Minus” direction, under the “Error Amount” section select “Custom” and click “Specify Value”. In the pop up box leave the “Positive Error Value” box alone. In the “Negative Error Value” box clear the error value and click on the spreadsheet icon in the right hand side of the box and highlight cells B30 through G30 for the “Whisker -” values. Click the spreadsheet icon in the right hand side of the box. Click “OK”. Click “Close”.

Step 3: Add the Up Whisker. Select the 3Q box series (the green boxes) and click “Chart Tools”. Click “Layout Tab” and click “Error Bars”. Select “More Error Bar Options” from the bottom of the menu. Under the Vertical error Bars section select the “Plus” direction, under the “Error Amount” section select “Custom” and click “Specify Value”. In the pop up box leave the “Negative Error Value” box alone. In the “Positive Error Value” box clear the error value. Click the spreadsheet icon in the right hand side of the box and highlight cells B31 through G31 for the “Whisker +” values. Click the spreadsheet icon in the right hand side of the box. Click “OK”. Click “Close”.

Step 4: Format the Boxes. Select the bottom series (blue boxes), right click and select “Format Data Series”. Under “Fill” select “No fill” and under “Border Color” select “No Line”. Click “Close”.

Step 5: Format the Boxes. Select the bottom series (red box) and right click. Select “Format Data Series”. Under “Fill” select solid fill and change to a light blue. Click “Close”. Repeat this step for the green box and select the same color.

Step 6: Add the Mean. To add the mean as a series of markers on the box, click the chart and click “Chart Tools” above. Click “Select Data”. In the pop up box under the “Legend Entries” section click “Add”. In the pop up box under “Series Name” click the spreadsheet icon on the right hand side of the box and highlight cell A18 (Mean) then click the spreadsheet icon. Under “Series Values” section click on the spreadsheet icon on the right hand side of the box and highlight cells B18 – G18. Click the spreadsheet icon. Click “OK”, then click “OK”.

Step 7: Format the Mean Data Series. Select the purple boxes, click “Chart Tools”, click “Change Chart Type” and select a Line chart using the first icon under the Line option. Click “OK”.

Step 8: Reformat the Line. Click the line you just made and right click. Click “Format Data Series” and in the pop up box under “Marker Options” select “Built in” and change to a diamond (in this example). Under “Line Color” select “No Line”. Click “Close”.

Step 9: Add Data labels to the Horizontal Axis. Click the horizontal axis and right click. Select “Select Data” under the “Horizontal Axis Labels” section of the pop up box and click “Edit”. In the pop up box click the spreadsheet icon on the right hand side of the box and highlight cells B16 – G16 above. Click the spreadsheet icon. Click “OK”. Right click the labels, select “Font” and change the font size to 8 (as in this example).

Step 10: Delete the Legend. Click the legend and press the delete key (DEL).

Step 11: Add a Title. Click the chart and then click “Chart Tools”. Click the “Layout” tab, click “Chart Title” and select “Above Chart”. Click the title and change text to “Average Number of Emergency Patients Arriving by Time of Day”. Click the chart title, right click and select “Font”. Change the size to 10 (as in this example). To change the vertical axis scale, click the vertical axis and right click. Select “Format Axis” and under “Axis Options” change the “Maximum” to “Fixed” and change “Number” to 35. Change “Major Unit” to “Fixed” and change number to 5. Click “Close”.

Learn more about Box and Whisker plots by checking out my newsletter here.

FacebookTwitterLinkedInGoogle+EmailShare
This entry was posted in Excel Tips & Tricks, Graphs. Bookmark the permalink.

27 Responses to Making a Box and Whisker Plot in Excel

  1. Rama Krishnan says:

    Thank you for your detailed tutorial. Very helpful!

  2. If you have Excel 2010 and you try to replicate the above steps, you’ll hit a roadblock when writing the formulas for the Quartiles. Excel 2010 has updated their Quartile formulas. Here’s the 411 on what’s new:

    Excel 2010 has replaced the QUARTILE formula with two options: QUARTILE.INC and QUARTILE.EXC.

    QUARTILE.INC replaces the 2007 QUARTILE formula. The INC stands for Inclusive — meaning if you use this Quartile formula, it will work on older versions of Excel data, such as 2007. So if you are updating an Excel report that you produced with Excel 2007 (or older), you’ll want to use QUARTILE.INC to perform the calculation.

    QUARTILE.EXC is new and is said to be the more precise of the two quartile formulas. EXC stands for Exclusive — meaning if you use this Quartile formula, it will NOT work on older versions of Excel data (it is ‘exclusive’ of prior Excel versions).

    So… if you are performing a quartile calculation utilizing Excel data from a prior version (e.g. 2007), you’ll want to utilize QUARTILE.INC to perform the same calculation in Excel 2010. If you are just practicing with the data from this blog or producing a new report, you can utilize QUARTILE.EXC as it is the more precise calculation.

    Hope this helps.

  3. Pooja says:

    Excellen – thanks very much it was really easy to follow the steps and create the graph.

  4. SJ says:

    Was able to plot the minus error bar but when i plotted the plus error bar, the whisker ended at the top of the 3rd box i.e. 75th percentile. The value in the data for the positive whisker is positive, tried using the max instead of the whisker but the whisker still ended at the top of the 3rd box. What am I missing?

  5. leroy tyrone says:

    you rock! other tutorial had me doing sideways box plots and omitted the mean. totally fantastic.

  6. Mina says:

    Thank you for this post, It’s very useful.

  7. Pingback: Banana Boxes and Box Plots | Katherine S. Rowell & Associates

  8. frk7777 says:

    Thank you very much for this post. It was clear, detailed and straightforward. I really appreciate your effort.

  9. Alain Girard says:

    Thanks your the explanations, it was very clear.

    I thinks box plot like those are very useful to show distrubution, although I don’t think they are suitable to every audience. For example, if I need to address upper management, it’s very unlikely they will be able to interpret the graph properly without explanations.

    I find the 3 values box plot (without the whiskers) easier to understand and more straightforward. I tried to tweek your example to create a 3 value version, but couldn’t make it. :-( Maybe, it could be the topic of another of your useful post !

    Thanks

  10. VIKAS says:

    THANKS A LOT………….A GREAT HELP!!!

  11. ewa-aurelia says:

    Hey Katherine, thanks very much for this beautiful tutorial! I’m very pleased with the look of it. However, I found that when you have negative values (e.g. the 1st quartile is a negative number), I end up with and empty space between Q2 and the mean and median, and then the Whisker- is attached to nothing.

    I decided to keep the Q1 filled with color – that does the trick and my graph looks normal now.

  12. Hesmond says:

    Thanks for the tutorial, I was having lots of trouble trying to figure out how to do the box plot until I found this blog, so thanks!!

  13. Kristina says:

    This is an Awesome tutorial! Thank you so much!!!!!! Very clear and easy to follow.

  14. Andrew says:

    This was exactly what I was looking for and the instructions were great. This is a great tutorial–keep up the great work!

  15. Karen says:

    We also added a performance comparison to our box and whisker plot: using the method for adding a mean, we added our performance to see how we looked against national data. We just used a different marker than the mean marker.

  16. Mireaux says:

    I have absolutely no idea how I would’ve figured this all out on my own. You’re a lifesaver! It’s a little embarrassing to admit, but I’m using your tutorial to build charts for my thesis. I guess you’re never too old to learn. :)

  17. Luis says:

    Hi,

    I have the same issues as SJ above.

    SJ says:
    May 17, 2013 at 3:57 am
    Was able to plot the minus error bar but when i plotted the plus error bar, the whisker ended at the top of the 3rd box i.e. 75th percentile. The value in the data for the positive whisker is positive, tried using the max instead of the whisker but the whisker still ended at the top of the 3rd box. What am I missing?

  18. Alice says:

    This is the most helpful tutorial I’ve found–thanks!

  19. Thank you very much. I can make the graph easily by following your instructions.

  20. Bhupander Kumar says:

    For long time, I was searching this type of easy demo to plot.
    Thank you very much

  21. Razuwika C says:

    I didn’t know that I was so behind in the uses of excel. Thank you for the eye opener

  22. Derek says:

    Brilliant stuff! Thanks so much!

  23. Jon Lorens says:

    Thanks, great instructions, but how do you add the median line?

  24. Enrique Ramirez says:

    Hello.

    How do you add the median line?

    Thanks
    Enrique

  25. Maria Diamond says:

    How do you change a vertical box and whisker plot into a horizontal box and whisker plot on excel.

  26. Adam Eslick says:

    Thank you. This was extremely helpful. Really logical.

  27. Taras says:

    Thank you!
    “Step 7: Format the Mean Data Series” was extremely helpful for me.

Leave a Reply

Your email address will not be published. Required fields are marked *


− 7 = one

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>