Keywords: Excel, box-plot,box plot, whiskers, quartile chart.
Version: Excel 97 or later
UPDATE: See also the Improved advanced box-plot and whiskers chart in Excel.
UPDATE 2: This method is largely outdated, please see the latest entry about box plot and whiskers chart in Excel 2010.
In this tutorial I will describe how to create neat looking box-plot graphs with whiskers in Excel. This chart type is particularly useful when presenting data series representing many replicate samples and you want to quickly give the audience a idea on the spread of the data. My example are from the field of natural science, but the chart type is equally useful in other fields.
To create this type of box-plot chart we need four values from our data; the first and third quartile and the max and min values of the data set. Fortunately these functions are all included in Excel, so that makes our life a lot easier.
1. Start by creating two data series in column B and C, like the example in picture 1. In cell E3:E6 enter Q1, Min, Max and Q3.
Picture 1.
2. In cell F3 you enter =QUARTILE(B2:B19,1) which will give the 25th percentile of the population (the Sample 1 replicates). In F4 you enter =MIN(B2:B19). This will give the minimum values in the population. In a similar manner you enter =MAX(B2:B19) in F5 and =QUARTILE(B2:B19,3) in F6 to get the max value and the 75th percentile respectively (see picture 1).
3. Now select cell range E3:E6, press Ctrl and select cell range F3:G6. Click the Chart wizard button and select the Stock Open-High-Low-Close type diagram (picture 2, click Next and then in the Data Range tab select Series in: Rows. Click finish.
Picture 2.
4. That’s basically it! You now have a basic box-plot and whiskers diagram which you can format to your liking, see picture 3.
Picture 3.
This article was originally posted on BloggPro.com.
Popularity: 27% [?]
I prefer to use bar charts with strategically placed error bars to accomplish this. I’ve built an open source excel 2007+ ribbon add-in to automate box plotting with the bar chart method. It’s free & I’d love help making it better. Give it a try at http://sourceforge.net/projects/excelstats/
Extremely helpful and easy-to-follow. Thanks a lot.
Thanks, this was helpful!
I did find that I had to do a couple more things to get a traditional box-and-whisker plot.
1. Change the scale of the right Y-axis to be the same as the left Y-axis (since it’s a stock plot, it assumes the volume is on a different scale than the rest of the data and determines the range of that axis separately from the other axis — but of course I want the volume value to align with the “open/close/low/high” values).
2. Unfortunately, the default is that the volume is shown (on my version) in dark purple, *under* the box for the open/close/low/high. It also wouldn’t let me make the box fill transparent in order to show the upper line of the volume box. So I couldn’t see the “median” line that normally goes across the center of the box. The only way I could find to do this was in three steps:
(a) change the fill of the box to “none” so you can see the volume bar underneath
(b) use the drawing tool to draw lines across each box at the right place
(c) change the fill and border of the volume bar to “none” so that it won’t show up at all.
Pretty clunky and tedious but it works as long as our data doesn’t ever change.
You are amazing. I spent hours trying to figure this out and I ended up creating a much less visually-appealing box-plot. This method is simple and looks great. Thanks again
Great, it works. Thanks.
nm. I think I found it! thnx.
can you do an example like this for Office Excel 2007? I tried it with this and it didn’t work.
This helped, thanks!
you’re the 6th site i’ve been too, and the first that has actually worked. thanks so much!
Thanks for the explanation
thnx, good to hear that it helped someone!
Cheers!!
found this site very useful thanks mate