Keywords: Boxplot, box plot, stem and leaf plots, Excel 2007, how to make
Version: Excel 2007
Dowload:
Since the previous entriesI have recieved quite a few questions about Box-plots in Excel 2007, so I decided I should describe one way to create decent looking box plots in Excel 2007. In my example I start with a set of data containing six samples with ten replicates each, and from this I want to create a box plot showing the extremes, median and the quartiles.
I create five new rows (12-16), max, 3rd quartile, median, 1st quartile and min and then calculate the statistics accordingly in cells B12:B16:
=MAX(B2:B10)
=PERCENTILE(B2:B10,0.75)
=MEDIAN(B2:B10)
=PERCENTILE(B2:B10,0.25)
=MIN(B2:B10)
Then copy to cells C12:G16.
Since we will “trick” Excel to draw a box-plot and use a stacked column chart we have to modify our data slightly. The first segment of the stacked column will be invisible and end where the lower boundary of the 2nd quartile begins ( =PERCENTILE(B2:B10,0.25) ). The next segment will consist of the 2nd quartile (median-1st quartile, or B14-B15). The third segment is the 3rd quartile (3rd quartile – median, or B13-B14). The length of the whiskers representing the max and min values are calculated as 1st quartile – min or B15-B16 and max – 3rd quartile, or B12-B13.
These values are calculated in a new range, see image below.
Now I’m ready to insert the chart. I select the range B19:G21 (see image below) and select a 2D stacked column from the Insert–>Table menu.
Next we add the whiskers. Select the second segment, click on Chart Tools –> Layou –> Select Error bars –> More error bars options and pick the Display Direction: Minus, indicate the Error Amount: Custom and click the Specify Value button. Leave the Positive Error Value as is and select the range containing the Min values for the Negative Error bar.
Repeat for the max value whiskers. The chart now should look like the one in the image below.
To make the chart a bit neater, right-click the lower segment series (green series in the image) and select properties and make invisible. Format the rest of the chart to your liking. Done!
Good luck, and enjoy your new Box plots.
Popularity: 100% [?]
I’m working with standardized data, and I’m getting my max showing as equaling my 75th percentile (3rd quartile); I’m also having the same problem where my min equals my 25th percentile. Help???
Hey Jesper
Awesome tool! I found the graph in your example didn’t quite match the data you had for the whiskers. Not sure why it was different, but I found I had to get the ‘max’ value in the ‘trick’ data block to be Max-Median from the ‘true’ data block, and ‘min’ value had to be Median-Min.
This produced the ‘trick’ data block below which produces a graph matching the graphic you have.
Hope that helps!
Max 4.7 1.3 1.9 4.2 2.4 3
Upper 1.1 0.3 1.3 2.5 1.7 1.9
Median 3.7 2.2 0.9 1.5 0.5 1.2
Lower 51.3 46.3 43.2 62.7 52.1 54.5
Min 4.7 3 1 2 1.4 2.5
I can’t get the whiskers to show the same way. I saw that Carla stated that you have to select the bottom but I don’t have that option.
@viajy – you have to go to select data and change the order of the series to get it right.
Thank you!
Thank you so much, i needed this for a physics lab, but the instructor figured we already knew how to do this. Very helpful.
Very Very helpful thank you very much
Wonderful instructions. The only set I have found that actually works.
Very Useful and easy to follow!
Help. I am a newbie….how did you calculate the median in the 2nd group of numbers; 3.7, 2.2, 0.9, etc.
This made my day. I find it amazing how create, innovative and tenacious people can be. You are quite a guy to figure out how to create a box plot using such a complicated method on your own. I’ll bookmark the page but I won’t attempt to do this now, it’ so complicated that I’m sure my teacher won’t ask us to do this for our upcoming class. Thanks though.
Dear Jesper
I still cannot get this to work—I am using Excel 2002 however, I tried to follow but my graphs come inverted, the largest portion is on top and the smallest portions are on the bottom, if you can help me any way I would so very grateful
sincerely yours,
vijay
This is brilliant as I am useless with stats packages and find excel much easier to use! Done fairly easily, apart from when the differences between values is zero. Read up on the plotting negative values but that didn’t really fix it for me :S
But thanks for the tips in ‘tricking’!
Nice guide, thanks. I added another series in which allows me to indicate the median with a bar rather than the intersection of the two series.
I calculate the max and min range across the data set, divide by a number to get a thickness for the series which equates to a thick line (dividing by 80 worked for me), take half of this thickness off the median series, take the other half from the 3rd Quartile series, and then add the series into the graph.
To minimize chart junk I then color this series in red, the quartiles in shades of grey (no outline), thicken the error bars up a little, remove the caps, and put them in a light grey as well, remove the axes lines, and change the gridlines to a very light grey.
Thanks again.
@Matts: I think this post will help you handle negative values in the data set.
Thanks a ton for this guide! I made very good use of this. I was looking at other “how to make a box and whisker plot guides,” but this is the only one I found that actually works and is sorta simple. I hope I get an A on the assignment!
how do you use MIN>0 when you have a series of numbers both in positive and negative values? Ex. 45, 5, -6, -2. MIN in this case is 5. How do i do that?
I am thankful for the help because I really needed these boxplots and your post helped me finish my work, but I think there are mistakes.
For instance
1)the values should be written starting with the min value down to the max value.
2)the quartile formula is =QUARTILE(B3:B55, 1) where “1″ is the first quartile and when you replace it with 2 or three its the median or the 3rd quartile.
3)when adding the error bars you need to choose the first bar if your adding the lower limit and the last bar if your adding the upper limit. For some reason I think you stated that we need to choose the 2nd bar.
other than that i think the rest are ok but the differences part was a bit confusing and needed some testing before I got it right.
Thanks again though because I would have been in trouble without this help anyway!
How do you make multiple boxplots in same scale using ddxl???
you choose fill and choose no fill…
Hey,
I’m having problems when I’m trying to make the segment ‘invisible’, by using properties, except when I right-click on it, there is nothing to click which says properties. Can anyone tell me where to find it please?
Thank-you in advance.