Excel Box and Whisker Diagrams (Box Plots)
by Jon Peltier
Tuesday, June 7th, 2011
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
Box and Whisker Charts (Box Plots) are commonly used in the display of statistical analyses. Microsoft Excel does not have a built in Box and Whisker chart type, but you can create your own custom Box and Whisker charts, using stacked bar or column charts and error bars. This tutorial shows how to make box plots, in vertical or horizontal orientations, in all modern versions of Excel.
In its simplest form, the box and whisker diagram has a box showing the range from first to third quartiles, and the median divides this large box, the “interquartile range”, into two boxes, for the second and third quartiles. The whiskers span the first quartile, from the second quartile box down to the minimum, and the fourth quartile, from the third quartile box up to the maximum.
Sample Data and Calculations
To play along at home in Excel 2007 or 2010, download the workbook Excel_2007_Box_Plot_Workbook.xlsx.
Let’s use the following simple data set for our tutorial. The values were taken from a normally distributed population with a mean of 10 and standard deviation of 5. There are four sets of 20 values.
All of these values are positive. If your data set has mixed positive and negative values, this technique requires major modifications.
First, insert a bunch of blank rows, and set up a range for calculations. Only the horizontal version of the box plot uses the last calculated row, “Offset”. It will not hurt to include it in the vertical box plot’s calculations.
First, compute some simple statistics, such as the count, mean, and standard deviation. The formulas used in column B are shown in column G of the screen shot.
Now let’s compute the minimum and maximum, median, and first and third quartiles.
Finally, let’s determine which values we need to plot. Our chart has a box for the second quartile, which shows the difference between median and first quartile calculated above. It has a box for third quartile, which show the difference between the third quartile calculation and the median. The bottom of the lower box rests on the first calculated quartile. The down whisker is as long as the first quartile minus the minimum, and the up whisker is as long as the maximum minus the third quartile.
The offset values are calculated as follows: In my example, I have four categories, Alpha through Delta. I can divide my horizontal chart into four horizontal strips, numbered from 0 to 4, each containing one box-and-whisker unit. I need to position my average points in the middle of each 1-unit horizontal strip. These will ultimately go onto a secondary vertical axis which I will have conveniently scaled from 0 to 4. Hence the Y values I will need are 0.5, 1.5, 2.5, and 3.5.
Chart Construction
Select the header row of the calculated data, then hold Ctrl while selecting the three rows that include Bottom, 2Q Box, and 3Q Box. This multiple-area range is highlighted in orange below.
With this range selected, insert a stacked column chart or a stacked bar chart. Be sure to use the stacked version, and not the 100% stacked version, of the column or bar chart.
The labels in the bar chart go bottom-to-top. To reverse the labels, select the vertical axis, press Ctrl-1 (numeral one) to open the Format Axis dialog, then check the “Categories in Reverse Order” box, then under “Horizontal Axis Crosses”, select “At maximum category”.
To add the down whisker, select the Bottom series, then in the Chart Tools > Layout tab, click Error Bars, and select More Error Bar Options from the bottom of the menu. Choose the Minus direction, select Custom for Error Amount, and click on Specify Value. Delete the contents of the Positive Error Value box in the mini dialog that appears, then clear the Negative Error Value box and select the Whisker- row from the table (B14:E14). Click OK and Close to get back to Excel. These “down” error bars (whiskers) extend from the bottom (left) edge of the 2Q Box downward (leftward) into the Bottom series.
To add the up whisker, select the 3Q Box series,then in the Chart Tools > Layout tab, click Error Bars, and select More Error Bar Options from the bottom of the menu.
For a vertical box plot, choose the Plus direction, select Custom for Error Amount, and click on Specify Value. Delete the contents of the Negative Error Value box in the mini dialog that appears, then clear the Positive Error Value box and select the Whisker+ row from the table (B15:E15). Click OK and Close to get back to Excel.
For a horizontal box plot, the process is a little trickier, because Excel 2007 is likely to forget the range you’ve specified. Choose Both for direction, select Custom for Error Amount, and click on Specify Value. Enter zero in the Negative Error Value box in the mini dialog that appears, then clear the Positive Error Value box and select the Whisker+ row from the table (B15:E15). Click OK, then change to the Plus direction. Click Close to get back to Excel.
These “up” error bars (whiskers) extend upward (rightward) from the top (right) of the 3Q Box.
Now we can format the boxes. Select the Bottom series, and apply no fill and no border, so it is hidden. Then select each of the 2Q Box and 3Q Box series, and apply a dark border and a light fill.
– - 
Adding the Mean
To add the mean as a series of markers, select the Mean row in the calculated range (highlighted in blue). If you are making a horizontal box plot, hold Ctrl and also select the Offset row (highlighted in green), so both areas are selected. Copy the selected range.
Select the chart, and use Paste Special to add the data as a new series. If you are making a horizontal box and whisker diagram, check the “Category (X Labels) in First Row” box. The “Series Names in First Column” box should already be checked.
The new series is added as another column or bar stacked on top of the existing ones.
– - 
Select this new series, then on the Chart Tools > Design tab, click on Change Chart Type. If you are making a vertical box plot, choose a Line Chart style. If you are making a horizontal box plot, choose an XY Scatter style.
– - 
The points in the horizontal box plot are in reverse order. To change the order of points, select the secondary vertical axis (right edge of the chart), press Ctrl-1 (numeral one) to open the Format Axis dialog, then check the “Values in Reverse Order” box.
If you’re making a horizontal box plot in Excel 2003, this last process is a little more involved. Excel draws both secondary axes, but the vertical one is hidden behind the primary axis with the text labels (below left). Double click on the secondary horizontal axis (top of chart), and on the scale tab of the Format Axis dialog, check “Value (Y) Axis Crosses at Maximum Value” (below right).
– - 
Excel 2003, continued: Double click the secondary vertical axis (right of chart), and on the scale tab, check “Values in Reverse Order” and uncheck “Value (X) Axis Crosses at Maximum Value” (below left). Finally, select the secondary horizontal axis (top) and click Delete; Excel will now plot the XY series on the primary horizontal axis.
– - 
All Versions: Now format the mean series: remove the line, and use an appropriate marker of a contrasting color. If you’ve made a horizontal box plot, hide the secondary Y axis (right edge of the chart) by choosing no tick marks, no tick labels, and no line in the Format Axis dialog.
– - 
That was easy and didn’t take too long.
Peltier Tech Box and Whisker Chart Utility
This tutorial shows how to create Box and Whisker Charts in Excel, including the calculations and specialized data layout needed, and the detailed combination of chart series and chart types required. This manual process takes a little time, can be prone to error, and becomes tedious.
I have created the Peltier Tech Box and Whisker Chart Utility for Excel to create such charts automatically from raw data. This utility, a standard Excel add-in, lays out data in the required layout, then constructs a chart with the right combination of chart types. This is a commercial product, tested on hundreds of machines in a wide variety of configurations, which saves time and aggravation.
The Peltier Tech Box Plot Utility creates charts in either horizontal or vertical orientation. It provides four different methods for calculating quartiles. This add-in also provides three styles for the box plots, including one that shows outliers.
Please visit the Peltier Tech Box and Whisker Chart Utility page or the Peltier Tech Box and Whisker Chart Utility Documentation page for more information.
Related Posts:
- Simple Box Plots
- Text Labels on a Vertical Column Chart in Excel
- Filled Histograms Using Excel XY-Area Charts
- Text Labels on a Horizontal Bar Chart in Excel
- Gantt Chart with Repeated Tasks via Excel XY Chart
- Box Plot of Values Against Limits
Posted: Tuesday, June 7th, 2011 under Chart Types.
Comments: 46
Comments
Comment from Tamoghna Acharyya
Time: Tuesday, June 7, 2011, 1:20 pm
Hi Jon!!
Thanks for the post. I am a big fan of you.
Comment from M Amjad
Time: Monday, June 13, 2011, 4:13 pm
how we can move secondary Y axis in bottom direction, like for common x-axis, one Y-axis in upward direction and other Y-axis in downward direction.
Comment from Jon Peltier
Time: Monday, June 13, 2011, 8:20 pm
I’m not sure what you’re doing with the axes. Why do you want two axes, and what are they showing?
Comment from robert a
Time: Tuesday, June 21, 2011, 1:14 pm
I have been enjoying the PTS Box and Whisker in Excel 2003 for the past two years. I now have Excel 2010 … how do I add this in? I have tried to reinstall from the zip file.
Comment from robert a
Time: Tuesday, June 21, 2011, 1:23 pm
Actually, after reinstalling it now shows up under Add-Ins.
Comment from Wangari Gichiru
Time: Sunday, July 31, 2011, 8:53 am
Thank you so much for this information. This is VERY helpful.
Comment from David
Time: Monday, August 15, 2011, 7:14 am
i want to draw a graph of one dependent variable having two categories and one continuous independent variable, what is the appropriate graph?
Comment from Jon Peltier
Time: Monday, August 15, 2011, 10:22 am
David -
Seems to me you want an XY (Scatter) Chart.
Comment from Dorina
Time: Monday, August 15, 2011, 2:01 pm
Perfect! Exactly what i was looking for! Thanks a lot!
Comment from Eva Van De Water
Time: Monday, September 5, 2011, 10:07 pm
Thanks a million!
Comment from Franziska Lindner
Time: Saturday, September 10, 2011, 4:50 am
I can draw a horizontal boxplot and get the outliers marked as points (using xy scatter plot). However, the outliers are not in line with the actual boxplot (but above the boxplot if I use y>0 and below, if I use y=0). How can I make the outliers “lie on the same axis” as the boxplot.
Thank you so much.
Regards
Franziska
Comment from Jon Peltier
Time: Saturday, September 10, 2011, 11:06 am
Franziska -
Did you line them up using the same approach I used to align the average markers in this example? The X values need to be the outlier value, and the Y values use the offset values 0.5, 1.5, 2.5, etc. as shown in the last table above.
Comment from Franziska Lindner
Time: Saturday, September 10, 2011, 12:05 pm
This is so freaky, suddenly it’s working. Thank you so much – I don’t know how you did it, but it seems you persuaded my Excel to be obedient. Thank you :-)
Comment from Jon Peltier
Time: Sunday, September 11, 2011, 9:06 am
Franziska -
Ha, I wish I had that kind of control over MY Excel!
Comment from Brad
Time: Tuesday, September 13, 2011, 11:20 am
Can these box plots identify outliers i.e., the asteris beyound the wiskers?
Comment from Jon Peltier
Time: Tuesday, September 13, 2011, 11:46 am
Brad -
Outliers can be displayed on charts such as these, but you need to add more data series, and adjust the lengths of the whiskers to indicate not min and max data, but data closest to the definition of outlier without being outliers.
The ability to display outliers is built into the Peltier Tech Box and Whisker Chart Utility.
Comment from Jhiel
Time: Sunday, September 18, 2011, 7:56 am
Dear Jon,
Thanks for this post, I was able to build a box and whisker plot from scratch because of you. I need some tweaking however, hope you can still be of help. I need to show the position of outliers (any values outside the USL / LSL) in the graph also. How it can be done?
thanks,
Jhiel
Comment from Jon Peltier
Time: Monday, September 19, 2011, 4:00 pm
Jhiel -
First, you have to redefine the length of the whiskers. If you are dealing with outliers, the whisker goes as far as the farthest point which is not an outlier. Near outliers are more than 1.5 interquartile ranges (third quartile minus first quartile) outside the first and third quartiles. Far outliers are more than 3 interquartile ranges outside the quartiles.
Then you need to set up your outlier data. Put the category for each outlier in column 1 of a convenient range (the first box is category 1, etc.), and put the value into column 2. Copy this range, select the chart, and use paste special to add this data as a new series. Convert this new series to an XY type, and then assign it to the primary axis (if Excel moved it to the secondary axis).
Comment from andy
Time: Saturday, September 24, 2011, 12:19 am
Very helpful, thanks.
A note: I made a vertical box and whisker chart but Excel kept “forgetting” the plus whisker value. I had to follow the instructions for the horizontal plot to trick Excel into remembering the plus whisker. BTW, how the heck did you ever figure out this workaround!? (and the eternal question: why is MS office so buggy?)
Comment from Appalanaidu
Time: Tuesday, September 27, 2011, 9:34 pm
Dear sir,
This is Naidu Ph.D student, before i do not no about Excel Box and Whisker Diagrams (Box Plots) how to draw this diagrams in Excel? why we are using?. But i typed this words in Google then i got your Pelteir tech blog. Then i learned about various charts like Box plot, cluster stock, Dot plot etc.
Thanking you very much giving this opportunity to all
Comment from mariam
Time: Monday, October 3, 2011, 10:12 am
thank you so much, finally I did it!
best wishes
M
Comment from brad
Time: Thursday, October 13, 2011, 1:38 pm
huge help. thanks a ton.
I have some borders on the box that are above and below 0. any work around for this? thanks
Comment from GP
Time: Friday, October 14, 2011, 4:13 pm
Want to make the plot horizontal, but Excel 2010 doesn’t have a “Categories in Reverse Order” box in the vertical axis “Format Axis” box, but only “Values in Reverse Order”. Any thoughts would be most appreciated.
Comment from Jon Peltier
Time: Friday, October 14, 2011, 9:17 pm
The setting is certainly there:
If you already have secondary axes in the chart, you may be trying to format the other vertical axis.
Comment from Kelly
Time: Wednesday, October 19, 2011, 8:47 am
How do you calculate the offset?
Comment from Jon Peltier
Time: Wednesday, October 19, 2011, 11:20 am
Kelly -
In my example, I have four categories, Alpha through Delta. If I scale my secondary vertical axis from 0 to 4, I need points in the middle of each 1-unit division. Hence 0.5, 1.5, 2.5, and 3.5.
Comment from brad
Time: Monday, October 31, 2011, 7:11 am
can you please help find a solution to box values that are both above and below 0? here’s an example that is causing difficulty.
whisker- 7
1q box -11
mid box 6
3q box 5
whisker+ 9
Thanks a ton!
Comment from Melissa
Time: Wednesday, November 9, 2011, 11:34 am
Hi,
Thanks for the great blog! I was wondering if there is any way of changing the width of the boxes to reflect different sample sizes per cateorgory?
Comment from Sheikh Ali Ahmed
Time: Friday, November 18, 2011, 4:15 am
Hi,
Thank you very much for providing those utmost valuable information regarding different charts in Excel. I have drawn a box plot using your instruction. It was fun and interesting. But for my data set, I need to convert the box into standard deviation box. In which the mean value will be middle. And offcourse the whiskers range for upper and lower values would be as it is.
Please help.
Sheikh Ali Ahmed
Comment from Jon Peltier
Time: Friday, November 18, 2011, 8:29 am
It is not advisable to use quantities other than quartiles for box plots. You risk distorting the interpretation of those who are familiar with the standard box plot construction, causing them to overestimate the variability in the plotted data (mean ± 1 sd includes 68% of a population, while the interquartile range includes 50% of the population). You also risk distorting the future use of box plots by those who are not yet familiar with them.
Comment from Youssef RAYADI
Time: Thursday, December 8, 2011, 9:28 am
Thinks a lot
I have one more question please: It’s about adding the mean in the Blox Plot
I dont know how to do it.
Comment from Gretchen
Time: Wednesday, December 14, 2011, 5:57 pm
Hello–Thanks so much for this tutorial, ecstatic that I’ve made three box plots from scratch! I was wondering, is there a way to make just two? (i.e. just the ‘alpha’ and ‘beta’ boxes in your example?) The tutorial doesn’t seem to work for this.
Comment from Jon Peltier
Time: Friday, December 16, 2011, 9:36 am
Gretchen -
You don’t define what isn’t working, but I suspect it’s something like this.
When you first create a chart, Excel counts rows and columns in the source data range, and tries to minimize series and maximize points per series. You get the following chart, with two series and three categories:
Right click on the chart, select Select Data, and click the Switch Row/Column button, to get the proper starting chart:
From here, follow the steps to get your two-category box plot:
Comment from Anonymous
Time: Monday, December 19, 2011, 4:32 pm
Hi Thanks a lot for posting it. It is a lot helpful. However I am using excel 2007 and when I try to add whisker +, every error bar will have similar length of bar…I couldn’t know what went wrong there
Comment from Jon Peltier
Time: Monday, December 19, 2011, 5:31 pm
Did you do exactly this, which is the instruction for Excel 2007:
Choose the Plus direction, select Custom for Error Amount, and click on Specify Value. Delete the contents of the Negative Error Value box in the mini dialog that appears, then clear the Positive Error Value box and select the Whisker+ row from the table (B15:E15). Click OK and Close to get back to Excel.
Comment from dave
Time: Wednesday, December 21, 2011, 9:30 pm
This is fantastic right until I get to insertion of means. It works perfect in a vertical box plot. But in the horizontal plot, even when I click on xy scatter style, something is wrong. The line is on the wrong axis, somehow. Anyone else getting this bug (excel 2007)
Comment from Gretchen
Time: Thursday, December 22, 2011, 7:31 am
OH Thank you! It worked! Happy Holidays to you and your family…Gretchen
Comment from Cassy
Time: Wednesday, January 4, 2012, 6:02 pm
Hello!
Thanks so much for your tutorial, it really came in handy when constructing my charts! I do have a question though: Are the up and down whiskers equivalent to the min and max for the data sets? Are there box and whisker charts that use the min and max as the up and down whisker? Basically I’m just not sure how to interpret the up and down whiskers based on the quartile calculations you use to get them.
Thanks!
Cassy
Comment from Richees
Time: Monday, January 9, 2012, 4:59 pm
Thanks, very helpful
Comment from Kelly
Time: Friday, January 13, 2012, 8:41 am
I have used this site several times, so I appreciate you posting it! I now have to do an analysis with positive and negative data, and this says that the method would require major modifications. Is there another page that discusses how to do that? I need to look at chemical sampling data (influent v/s effluent) through a system. Sometimes the system removes the chemical (positive percent removal) and sometimes it loads the chemical (negative percent removal). I need to find out if overall it achieves 85% removal. Any thoughts on that? Thanks!
Comment from Jon Peltier
Time: Friday, January 13, 2012, 9:19 am
Kelly -
Scroll down to “Stacked Columns for Positive and Negative Data” in Excel Waterfall Charts (Bridge Charts), or visit the older tutorial at Stacked Column Charts that Cross the X Axis.
Comment from Meena
Time: Friday, January 13, 2012, 1:05 pm
Thank you for your easy to follow instructions. I want to put a line across each box and whisker plot for the mean series (instead of a dot). How do I do that? Thanks.
Comment from Jon Peltier
Time: Friday, January 13, 2012, 3:18 pm
Meena -
The simplest is to use the wide dash marker for the data points. Or if you don’t need the median, use mean for the boundary between the two boxes (but this might confuse people who are familiar with box plots, because box plots were designed to show median, not mean).
Comment from Meena
Time: Friday, January 13, 2012, 3:29 pm
Jon,
What is a wide dash marker? My boss wants me to put black dots for all of the data points on the box and whisker plot. He also wants me to put a single red line (in addition to the black median line) for the mean. I followed your instructions and plotted a dot for the mean and then drew a red line using “shapes” in excel before deleting the orginal dot. The problem is that the line drawn keeps moving. How can I make a line that doesn’t move (like the median line)? Thanks. -M
Comment from Jon Peltier
Time: Friday, January 13, 2012, 6:50 pm
Meena -
The long dash is the 7th marker in the dropdown list, right above the circle. You can enlarge it by changing the size, but it gets thicker as well as wider.
Another way to get a line that doesn’t move around is to draw a line as you’re doing now and format it (size, color, line thickness, etc.), copy the line, select the series, and paste (Ctrl+V). Then you can delete the line you drew. This pastes the line as a new custom marker for the series. This technique works for other shapes as well, but don’t get carried away.
Comment from Megan
Time: Friday, February 3, 2012, 8:22 pm
Hi John, thanks so much for your tutorial! I’m creating a horizontal plot and am having trouble adding the mean…I have eight categories (months) on my y axis and the range of my data on the x axis, and each time I try to add my mean series by selecting my months and then my offset data points (0.5 – 4), Excel plots all of my mean values for the month of May only (my first month). Do you happen to know what I might be doing wrong?
Thank you so much,
Megan
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.