# Create a box plot chart in Excel

For those who rely on Excel to do their data analysis (rather than mini-tab or JMP), occasionally the charts available are a little limiting.  So I thought I would post this technique to allow you to perform a box plot analysis using Excel.

Collate the data

First you need to gather your data together.  In this example, I have 10 sites, with values by month for the year 2011.  For clarification, I have also uploaded the excel file, so please feel free to have a look!

In the example attached, I have created a tab in excel for each site (labelled 1 to 10).  You can of course use just one tab for all if you prefer!

The first thing you need to do is calculate the median, minimum, maximum and upper quartile values for each site.  You can see this on the “Box plot” tab of the excel sheet attached.

The median is calculated by:

=MEDIAN(‘1’!\$C\$2:\$C\$11)

‘1’ refers to tab “1” and it is looking at the values in column C from lines 2 to 13.

Repeat this for each site (and tab) by changing the figure ‘1’ to ‘2’, then ‘3’ etc to form row 2 of the table below.  The \$ symbol ensures that the range used for column C never changes when you copy / paste the calculation across the row.

I will come back to row 3 in a moment, for now let’s skip on to rows 4 and 5.

The minimum is calculated by:

=MIN(‘1’!\$C\$2:\$C\$11)

The maximum is calculated by:

=MAX(‘1’!\$C\$2:\$C\$11)

Again, repeat this for each site (and tab) to form rows 4 and 5 of the table above.  The minimum and maximum values will be used to create the whiskers of the box plot.

Now let’s go back to row 3.  Excel has a built in function “quartile” which we can use for the calculation for this row and for row 6.

Row 3 (Lower quartile, Q1) is calculated by:

=QUARTILE(‘1’!\$C\$2:\$C\$11,1)

The value after the comma (in blue above) show that we want the lower quartile (Q1)

Row 6 (Upper quartile, Q3) is calculated by:

=QUARTILE(‘1’!\$C\$2:\$C\$11,3)

Once again, repeat this for each site (and tab) to form rows 3 and 6 of the table also shown below.  The Q1 and Q3 values will form the top and bottom of the box.

The order they appear in, (median, Q1, Min, Max, Q3) is very important!  Do not change this or the graph will be wrong!

Create the chart

Since excel doesn’t have a box plot chart we are going to have to create one.  To do this, first select the data table shown above, then on the Insert tab, click on the small down arrow next to Other Charts in the charts section.  Click on the 4th chart in the Stock section (Volume-Open-High-Low-Close.  You will need five series of values to create the graph).

Excel will automatically create the graph below.

This is not exactly what we want, so we will have to do some formatting.  The first step is to adjust the two Y axes so that they are identical, i.e. both go from 0 to 4.0.

Double click on the left axis.  In the menu that apears, ensure that Axis options is selected, then change the maximum value from Auto to Fixed and enter a value of 4.0.  Then click on the Close button.

Then right click on the blue filled median data on the graph and select change series chart type.   In the menu that apears, change the graph type to line and choose the 4th option (Line with markers).  Then click on the OK button.

The graph will then look as shown below.

There are a few more formatting points to do.

1. Change the fill of the box plot to none
2. Remove the interconnecting line of the median series
3. Double click on the boxes.  In the menu that appears, ensure that fill is selected and choose No fill.  Then click on Close.
4. With the formatting menu open, left click on the median series.  Left click on line color and choose No line.  Then left click on marker options and choose Built in.  Change the marker type to a line, increase the size to 11, then click on Close.
5. For the finishing touches, you could delete the legend, and the secondary Y axis.  You could also reduce the number of decimal places for the primary Y axis to 2  e.g. 0.00.  Finally, I have also changed the font colour and the chart lines to grey.

Enjoy!

Please note: The statistical analysis in JMP, Minitab and Excel is slightly different in terms of the inclusion of outliers or not!  If you are serious about data analysis, I recommend using the right tools (i.e. JMP or minitab).

boxplot

## 35 thoughts on “Create a box plot chart in Excel”

1. David Warriner says:

Hi Alesandra. This page is so helpful! But, despite following your instructions to the letter, whenever I click create chart, Excel (2013) simply says, to create this chart you must place your data in the following order, which it already is! e.g. volume traded, open price, high price, low price, closing price.
Do you have any suggestions?

2. David Warriner says:

Really? That’s great many thanks.

1. There are two reasons for this error:
1. The numbers are not real numbers, excel is seeing them as text
2. You need to have more than 4 data sets.

I’ve fixed your file and sent it back. Hope it helps!

1. Mehul says:

2. In David’s case he had only 1 data set if I remember correctly. I added in 3 pretend data sets to create the chart and then once it was created I deleted them.

3. laura says:

Hi,
You state at the beginning to enter the median, 1st quartile, etc. Where do I enter that?

4. Noel says:

Hi! Thanks for this post. Is it also possible to show the mean instead of the median?

1. Noel says:

Thanks! Why not? My data has some values that’s why I want to use a box plot. But at the same time I’d like to indicate the mean AFTER the outliers have been trimmed off…

2. It’s just not the normal procedures. You will need to be careful that people don’t get confused with your data… That’s all! Good luck!

5. Malik Powell says:

Hi, Ms. Blakeston. My name is Malik Powell. I’m a senior in High school and I have a math project that requires me to create a boxplot on excel. Unfortunately, I have no idea how to do so. I’ve followed your guide here but am still having trouble. Just like Mr. Warriner, whenever I try to create my graph I get an error message. It’s actually the exact same one he received. I was wondering if I could email you my spreadsheet in order to get some more assistance?

1. Sure, but did you see my comment to him? If I remember rightly, it’s a very simple solution

6. Malik Powell says:

I just sent out the email. Thanks so much for you help. I think my error may be with the number of data set. I’m not entirely sure though.

7. Malik Powell says:

Well, I have two sets. One set details state population and the other details state SAT averages.

1. You need 4 sets Malik. You will have to copy and paste the first set to create the graph and then delete once its done.

8. Malik Powell says:

Thank you so much! You have no idea how long I’ve been working on this. I can finally move forward with my project.

9. Teun says:

Great work, thanks a lot. It helped my daughter a lot with her schoolwork and I got something out of it in the process too.

Some restrictions: the mac version does not have the “built in” option (so it seems you can’t get the median across the whole box), plus the max min bar runs through the box (which is not “normal” for box plots .. I guess there is no option to bring the left axis to the front?)

again thanks for putting this online!

Teun

1. So just a couple of thoughts based on your comments. I’ve never actually used Excel on a Mac, so I’m just guessing here…

Can you change the order? This might help

Also you can use formatting to remove the max min bar through the box? Make the fill of the box the same colour as the line for example?

Also can you change the size of the bullets? This might make the median go across the whole box

10. Chris says:

Hi Alexandra, thanks for your help and easy to follow instructions. I have my box & whisker plots now, but wanted to ask you about something I noticed that bothers me. I experimented by adding a new series to plot the arithmetic mean. When I plot this though, the upper bounds of my boxes change (decrease below the median marker). The axis scale remains the same; not sure what is happening – any ideas?

My plots show the 95% and 5% percentiles instead of max and min. Is it ok to do this – I won’t have caused problems with scaling the size of the boxes or whiskers will I?

Best regards,

Chris

11. Gian says:

Hi Alexandra,
Do you know whether it’s somehow possible to create a bloxplot in excel with just 3 instead of more than 4 Collumns? Many thanks!

1. The only workaround I have found is to create a boxplot with 4 columns and then after you have created the chart to delete the data yiu don’t need. Make sense?

12. Tshepo says:

Greetings. i have been given 20years of monthly rainfall data for my location(Gaborone,Botswana). For my analysis, i am to produce a box plot. the problem is i am not familiar with it at all. i have read the steps, but the problem is, the data is too much. i have started calculating the monthly totals,as well as the annual total for each year in attempt to make it more sensible. i’d appreciate general advice on how to tackle this.Thanx

1. If you have a huge amount of data, I’d consider using a pivot table first to get the data into a manageable size and then try creating a box plot.

13. Tshepo says:

OH OKAY,,LET ME TRY THAT!

14. Jordan says:

This is very helpfull but i am having trouble with creating the table, i have put it in the order and it is still not making the chart!!!!

15. This is really great. I’d also like to add that I got the same error as the other posters, and found that I had to change my data set labels from numbers to text to get it to work. Once I created the chart though I could change them back.