Keywords: Boxplot, box plot, stem and leaf plots, Excel 2007, how to make
Version: Excel 2007
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% [?]
Dear Sir,
i have been trying to find a solution to draw a continuous graph , for programs, on a continious timeline.
for eg, the tv channel programming starts at 6 am and goes on till 12 midnight. i would like to plot two variables on a bar chart – the x coordinate of individual bars should represent the time duration of the program and the Y coordinate of the bar should represent the viewership.
my question is – would it be possible in Excel ?
are there any solutions for this ?
would be much obliged if you could help me .
thanks and regards,
Praveen
Hi
Yes it is possible, you use the same basic techniqe as described here. Chose a horizontal bar chart and trick Excel by hiding part of the bar with a dummy series to achieve the effect you are looking for.
If you need more hands on help please contact me via the Contact page.
great…i make it…
))
Hi Jesper, great stuff, however, the error bars are not the max and min values in a box plot, they are 1.5 times the inter-quartile range. With your approach there is no room for outliers.
Have a great day
Cute trick…and very useful. What about the instance where there are negative values? Your method of tricking excel be “reversed” where all the values are negative but the method breaks if only some of the values are negative.
It gets tricky because what range you hide (or not) depends on where (or if) any of the ranges straddle the zero point.
A more robust solution would be include some boolean logic in the formulas. I’ve been playing around with it and am stymied because the changing relative location of the zero point means:
1) the order of the stack changes
2) the series which should be made invisible changes. It will be either 1stQ, 3rdQ or none.
3) if none, then either the 1st and 2nd, or 3nd and 3rd series need to be rendered in the same color.
The cute trick starts to become an inelegant mess. Macros seem much easier at this point.
Ahh, what going to lunch will do. I figured out a better way to handle negative values.
In the “trick” range and an offset constant equal or greater than the lowest negative value to all of the numbers. Proceed as in the original instructions.
Finally add another range of data that has two points, the offset constant and the highest value in the original range. Change the axis of that range to “secondary” and make the marker and line style “none” to make it invisible.
Finally, change the tick labels of the primary axis to “none.”
Now you have a good looking chart with an appropriate scale. You may have to tweak the scale range on the secondary axis to make sure it lines up properly.
Art, that’s a good solution, I will definately write up an short instruction about it if you don’t object. Thanks.
Dear Sirs,
much thanks for your responses, but my question i still unanswered.
is it possible to plot columns of different thicknesses – signifying durations of programs and heights denoting the program ratings.
eg, if a program has a duration of 2 hours and1 trp, the column is 2 units wide and 1 unit tall, another program has a duration of half hour nd trps of 5, the column is half a unit wideand 5 units all.
would appreciate any solution on the same,
thanks , Praveen
hi, i found a small mistake (if it is one)
the “min” row for the modified row for plotting box plot should be median – min instead of
Q1 – min
Please let me know
Hi
The ‘min’ in this case is the error bar representing the extreme value of the data set, and to caliculate the value of the errorbar in this case I think it should be 1st quartile – min().
So I think it is correct in the article.
Thank you for your comment!’
/Jesper
Good morning (or good evening)
Is it possible to individually format axis labels under Excel 2007? How to do it? Thank you in advance.
“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.”
Can somebody post a screenshot link to this part of the process? im having trouble finding where you do that step, thankyou so much!
Hi,
I see you have made two tables. I have successfully made the First table, but what is the second table, and how can you work what figures they are? Is there a special formula?
Mikey, after you have selected the graph, at the very top, you will see a little green box, in a slightly hazy effect. This is on the top right-ish. Click on the “Layout” tab, and on the very left of this new menu, there will be a “Error bars” option. At the extreme bottom of the menu is a little box called “More error bar options”. To tell the truth, even I need a screenshot to help me with the next bit, butI know you need to click on minus, and then onto custom towards the bottom, and then specify. The rest I don’t know?
i need comments about graph
Hi, I’m having trouble with some data I’m trying to put into a box plot. Ive done the first box plot but I don’t know how to add another one onto the same scale????? I have excel 2007, does anyone know how?
Thanks
Will
To do the second table:
you write down the original 1st Quartile
the Min = Q1(1st Quartile) – the original min
Median = original median – Q1
Q3 = original Q3 – original Median
Max = original max – original Q3
select the bottom part of the column when you do the minus whiskers, and select the top part of the column when you do the plus whiskers!
If you like this kind of charts you will certainly love “Box and Whisker Chart” ad-in … It’s free and works perfect!
Rufik,
so where do I get the “box and whisker chart” add-in from??
Hi
How can i draw boxes if have some negative values in the dataset.
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.
you choose fill and choose no fill…
How do you make multiple boxplots in same scale using ddxl???
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 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?
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!
@Matts: I think this post will help you handle negative values in the data set.
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.
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’!
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 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.
Help. I am a newbie….how did you calculate the median in the 2nd group of numbers; 3.7, 2.2, 0.9, etc.
Very Useful and easy to follow!
Wonderful instructions. The only set I have found that actually works.
Very Very helpful thank you very much
Thank you so much, i needed this for a physics lab, but the instructor figured we already knew how to do this. Very helpful.