How to Make a Box Plot in Excel (and Read It)

A box plot, which Excel calls a box and whisker chart, shows the spread of your data at a glance: where the middle half of the values sit, where the median falls, and which values are unusually high or low. Current versions of Excel draw one from your data in a couple of clicks. Older versions need a workaround, which is covered at the end.

Set up your data

• One group: put all the values in one column with a heading.
• Several groups side by side: put each group in its own column, each with a heading, such as one column per team or for each month.
• Numbers only: leave out totals and text in the value cells, or the chart can come out wrong.

How to make a box plot in Excel

1. Select your data, including the headings.
2. Go to the Insert tab.
3. Click Insert Statistic Chart in the Charts group. Its icon looks like a small histogram.
4. Choose Box and Whisker.
5. Add a chart title and axis titles from the Chart Design tab so readers know what they're looking at.

How to read a box plot

Part	What it shows
The box	The middle half of your values, from the lower to the upper quartile
The line inside the box	The median, the middle value
The X in the box	The mean, or average
The whiskers	How far the rest of the values spread beyond the box
Dots beyond the whiskers	Outliers, values far from the rest

A tall box means the values vary a lot. A short box means they're bunched together. If the mean marker sits well away from the median line, a few extreme values are pulling the average.

Settings worth changing

Right-click a box and choose Format Data Series. You can:

• Show inner points to plot every value as a dot inside the whiskers.
• Show outlier points, or hide them if they're cluttering the chart.
• Show mean markers and Show mean line to compare averages across groups.
• Choose the quartile calculation: inclusive or exclusive median.

The catch: inclusive or exclusive median

This setting changes where the box edges sit, and most people never notice it. With an odd number of values, the exclusive method leaves the median out when working out the quartiles, while the inclusive method counts it in. On small sets of data the boxes can look noticeably different. Neither is wrong, but if you're comparing your chart with someone else's, or with a statistics package, check you're both using the same method.

Can't find Box and Whisker?

The built-in chart is a newer chart type. Microsoft's compatibility notes list it as not supported in Excel 2016 to 2019, and it isn't available in Excel 2013 at all. It also can't be saved in the OpenDocument (.ods) format, so keep the file as .xlsx. On an older version, there's a manual method.

How to make a box plot in older Excel

1. Work out five values for each group with formulas: the minimum, first quartile, median, third quartile and maximum.
2. Work out the differences between them, so each piece of the box stacks on the last.
3. Make a stacked column chart from those differences.
4. Format the bottom series with no fill, so the box appears to float.
5. Add error bars for the whiskers, set to the Minus direction at a percentage of 100%.
6. Colour the two middle sections to form the box.

It's fiddly, and Microsoft's own walkthrough is long. If you can, open the file in a current version of Excel, or in Excel on the web, and use the built-in chart.

When a box plot is the right chart

• Comparing response times, scores or order sizes between groups.
• Spotting outliers before you work out averages.
• Showing that two groups have the same average but very different spreads.

For a plain average comparison, a bar chart is easier for most audiences to read. Use a box plot when the spread is the story.

Explaining a box plot to people who've never seen one

Box plots are clear to anyone who knows them and puzzling to everyone else. When you share one with clients or colleagues, add a one-line caption under the chart, such as: the box shows where the middle half of results fall, and the line is the middle value. Point out any outliers in words too. It takes seconds and stops people reading the whiskers as the range of every value, which they aren't when outliers are shown separately.

If you're comparing marketing results by group, my guide to measuring social media without an analytics tool suggests which numbers to compare.

My verdict

Select your data and choose Insert, Insert Statistic Chart, Box and Whisker: that's a finished box plot in a current version of Excel. Then right-click a box to decide whether to show outliers and the mean, and check the quartile method if you'll compare it with another tool. On older versions, the stacked column workaround works but takes a while. For turning charts into dashboards people check, see building dashboards with AI.

Frequently asked questions

Where is the box plot in Excel?

Insert, then Insert Statistic Chart, then Box and Whisker.

What does the X mean on an Excel box plot?

It marks the mean, or average, of that group.

How do I hide outliers on an Excel box plot?

Right-click a box, choose Format Data Series and untick Show outlier points.

What's the difference between inclusive and exclusive median?

They count the median differently when working out quartiles, so the box edges can move.

Can I make a box plot in Excel 2013?

Not with a built-in chart. Use the stacked column and error bar workaround.

Can I show several groups on one box plot?

Yes. Put each group in its own column and select them all before inserting the chart.