How to Make a Box and Whisker Plot in Excel [2019 Tutorial]

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

If you’re working with statistical data in Excel, a box and whisker plot is one of the best charts you can use.

In the past, creating one came with a price — it was tedious

Fortunately, the box and whisker plot is one of the 6 new charts introduced in Excel 2016.

First, we’ll talk about what this chart is and when to use one. Then, we’ll discuss how to create one easily!

Kasper Langmann, Co-founder of Spreadsheeto

Let’s start!

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

The Box and Whisker Plot

A box and whisker plot — also known as box plot or box and whisker diagram/chart — shows the distribution of numerical values in a data set.

It’s a tool that displays the statistical five-number summary:

  • Minimum: the smallest value
  • 1st Quartile: the lower 25%
  • Median (2nd Quartile): the middle number
  • 3rd Quartile: the upper 25%
  • Maximum: the largest value

The ‘whiskers’ are the lines extending vertically that indicate the outliers — the minimum and maximum.

Kasper Langmann, Co-founder of Spreadsheeto

When to use a box and whisker plot?

When it comes to displaying data distribution, there are some charts in Excel who do a good job, like histogram.

However, a box and whisker chart provides additional details while showing different data sets in the graph.

Kasper Langmann, Co-founder of Spreadsheeto

In general, you can use this chart when you have different data sets from different sources that are related.

A few examples include:

  • Medical trial results
  • Test scores comparison
  • ‘Before and after’ numerical values
  • Survey responses
  • Route traffic patterns
  • Anything with a numerical summary

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Creating a box and whisker plot

Making this type of chart in Excel is easy.

Let’s assume you have the data below where you want to have a summary of the students’ grades on 3 subjects:

overview for the student grades summary exercise

Here’s a summary of making a box and whisker plot in Excel:

a summary of how to make a box and whisker chart in excel

No sweat, right?

Now, let’s break what just happened.

Kasper Langmann, Co-founder of Spreadsheeto

The first thing you have to do is select the cells with your data.

In our example, it’s B2:D12.

english, math, and physics grades selected

Then, click ‘Insert’ on the tab list.

insert option on the tab list

Find the ‘Insert Statistic Chart’ from the ‘Charts’ group and click the ‘Box and Whisker’ icon.

the box and whisker chart icon under the statistic chart group

Congratulations! You now have a box and whisker plot!

the default box and whisker plot

Editing box and whisker charts

Your first chart will always have Excel’s default colors and designs.

Fortunately, it’s easy to customize and edit your chart to your own liking.

If you want to edit your chart’s looks, click ‘Chart Design’ on the tab list. 

(If you can’t see the ‘Chart Design’ tab, click your chart first and you’ll see the tab.)

chart design tab on tab list

To see how the style looks on your chart, just hover your mouse over the styles.

previewing the different chart styles for box and whisker charts

In addition, you can also add or remove chart elements:

  • Axes
  • Axis Titles
  • Chart Title
  • Data Labels
  • Gridlines
  • Legend

Just click your chart and then click the plus symbol (+) at the upper right side.

add or remove chart elements icon on the upper right side of the chart

To view how each element affects the chart, just hover your mouse over the elements and you’ll see a preview.

(Furthermore, click the right-pointing pointer “►” to view additional options.)

previewing the different chart elements in a box and whisker plot

Have you noticed how the boxes seemed to group on the upper side?

Well, you can change the bounds on the vertical axis so the boxes would look more centered.

Kasper Langmann, Co-founder of Spreadsheeto

To do so, right-click on any part of the vertical axis and select ‘Format Axis’. A window will then open on the right-hand side.

changing the vertical border on the chart by right clicking

Here, you’ll see different options to format the axis.

To center the boxes, change the ‘Bounds’ of the variable.

minimum and maximum bounds option on format axis

Let’s change the minimum bound to 70 and the maximum to 95.

(If you like to revert the bounds, just click the ‘Reset’ button.)

changing the minimum bounds to 70 and the maximum to 95

Editing charts in Excel is more or less similar. When in doubt, preview the changes you like.

Also, you can always undo your action by pressing ‘Ctrl’ + ‘Z’. 😊

Kasper Langmann, Co-founder of Spreadsheeto

Conclusion

A box and whisker plot is a great tool to display the numerical summary of your data. In Excel, you can make one in seconds.

If you haven’t tried using box and whiskers plot yet, feel free to start today! When you’re stuck, just refer to this tutorial. Easy peasy! 😊

Kasper Langmann, Co-founder of Spreadsheeto
2019-09-04T13:01:42+00:00