How to Box Plots (Box & Whisker) in Excel to Show Data Distribution and Disparity

AUTHOR:  JASON MORRELL 

Jason Morrell

Reading Time:
  minutes remaining

A Box Plot (also known as Box and Whisker) shows the distribution and disparity of data and highlights statistical abnormalities.

Here is an example.

What a finished box plot (box and whisker) looks like

Notice that the 2003 data appears to show an extremely high rainfall measurement compared to every other year.

Box and Whisker charts, as they are know in Excel, were not always available. In older versions of Excel, the only way to create this kind of chart was to create a stacked column chart then modify its various components using tools like errors bars.

But now, it's much easier.

In this post, you'll see how to set up your data for a box plot, then how to create the chart and finally the modifications that will enhance the final product.

1. How to set up data for a box and whisker plot

Consider the following data showing the monthly rainfall in Timbuctoo between 2000 and 2017. 

Example starting data for a box plot

An outlier/anomaly has been highlighted yellow (cell D6).

For the example you are about to see, only a subset of the data is used (the data between 2000 and 2006).

Box & Whisker charts do not directly use the source data. Instead, 5 new values are calculated and used.

A) Breakdown of a box plot

The breakdown of a box plot
  • The X in the middle of the bar shows the median data value. 
  • The horizontal top line at the end of the whisker and the horizontal bottom line at the end of the whisker show the maximum and minimum data value respectively.
  • The top and bottom of the box represent the third quartile and first quartile data values.

B) Formulas used to create the data

Here are the formulas required to calculate the 5 new values used in the box plot.

MIN

  • =QUARTILE.INC(B$4:B$15, 0)
    could also use =MIN(B$4:B$15)

QUARTILE 1

  • =QUARTILE.INC(B$4:B$15, 1)

MEDIAN

  • =QUARTILE.INC(B$4:B$15, 2)
    could also use =MEDIAN(B$4:B$15)

QUARTILE 3

  • =QUARTILE.INC(B$4:B$15, 3)

MAX

  • =QUARTILE.INC(B$4:B$15, 4)
    could also use =MAX(B$4:B$15)

c) What the final data looks like

After creating these formulas, here is the data that is used to create the Box & Whisker chart. This example is only using the data for years 2000 to 2006.

The source data together with the formula-generated data that is used for the box plot

2. The workaround to create the Box Plot (Box and Whisker) the way it 'should' look

The Box and Whisker chart type in Excel is still quite buggy. It’s still a reasonably new chart type and there are a few wrinkles to iron out.

Hence the weird workaround you’re about to see.

1.  Select the MIN, Q1, MEDIAN, Q3 and Max cell range (B17:B21) for one column only.

DO NOT SELECT ANY HEADINGS. If you do you will end up with a chart full of ‘x’s rather than the boxes and whiskers.

Also, where the heading data is normally displayed on the horizontal chart axis, it is not displayed anywhere on a box and whisker chart. You simply see ‘1’ and it’s easiest just to switch it off or delete it, once the chart is created.

Select the MIN, Q1, MEDIAN, Q3 and Max cell range (B17:B21) for one column only.

2.  Click the Insert tab | Recommended Charts | All Charts | Box & Whisker.

This is what the initial chart looks like:

What a box plot first looks like when you use the workaround to overcome inherent bugs with the chart type

3.  The 2 steps so far were just to create the chart in the right orientation. Now, we select the real data we want to use on the chart.

With the chart selected, click the Select Data icon on the Chart Design ribbon.

4.  Select the full range of data.

Once again, DO NOT INCLUDE HEADINGS.

5.  Click OK. The chart will adapt to reflect the new data range.

What a finished box plot (box and whisker) looks like

We’ll revisit this chart to make some changes in a moment, but before we do, let’s look at what happens if you don’t follow this workaround.

3. WHAT HAPPENS IF YOU DON’T USE THE WORKAROUND?

Sticking with a 7-year range, if you select the full range of data straight off the bat, this is what happens:

Don’t select the whole data range when first creating the box plot because data will be plotted transversely (columns vs rows).

Where there should be 7 boxes there are only 5 because the data has been plotted transversely (columns vs rows). Even if you use the Switch Row/Column icon on the Chart Design ribbon, the problem does not resolve.

Despite much research I have not been able to find a solid reason why this happens. If you discover (or know) the reason, let me know in the comments below! I will be very grateful and you can have your moment in the spotlight!

Until then, use the workaround.

4. How to spread out the box plot elements

When the box plot is first created, all the elements are bunched up.

To spread out the chart elements:

1.  Double-click any element to display the Series Options in the sidebar.

2.  In the sidebar, click the Series Options drop-down arrow and select any series. When you make a change to one series it makes the same change to every series.

3.  Click the 3rd icon (also called Series Options)!

4.  Adjust the Gap Width percentage. The lower the number (e.g. 5%) the more spread out the chart elements become.

Under Series Options, adjust the Gap Width percentage. The lower the number (e.g. 5%) the more spread out the chart elements become.

5. How to change the scale on the axis

1.  In the sidebar, click the options drop-down arrow and choose Vertical Value Axis.

2.  Click the 3rd icon.

3.  Under the Axis Options heading, under the Bounds subheading, set the Minimum and Maximum value you want to use on the axis.

Under the Axis Options heading, under the Bounds subheading, set the Minimum and Maximum value you want to use on the axis.

6. How to switch on the legend (to give some meaning to the chart colours)

1.  On the Chart Design ribbon, click Add Chart Element then hover over the Legend option and choose where to position it.

It is better to use the legend rather than data labels on a Box & Whisker chart because there are 5 values per element.

7. What next?

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

POPULAR EXCEL POSTS

Radar charts in Excel


Sunburst charts in Excel


Box plots (Box & Whisker charts) in Excel


Waterfall charts in Excel


Funnel charts in Excel


VLOOKUP de-mystified


How to fix broken formulas fast


3 Ways To Insert a Degree Symbol In Excel


11 little-known sorting secrets (wait until you see 5, 6 and 9)

«« SEE ALL EXCEL POSTS »»

Office Legends Academy | Making Office Legends

Master Microsoft Office
in 30 days or less

more from

Office Mastery logo
Read More
How to fix formulas: 21 Fatal Formula Fails (and how to avoid them)
Read More
How to master the VLOOKUP formula in Excel
Read More
How many rows and columns does Excel have?
Read More
Creating New Custom Excel Templates

JOIN THE DISCUSSION

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>