Make a Box and Whisker Plot in Excel
By ExcelReady TeamUpdated 5 min read
The short answer
To make a box plot in Excel 2016 or later, select your numbers and go to Insert > Insert Statistic Chart > Box and Whisker. The box shows the middle half of the data and the line inside is the median. Whiskers reach the lowest and highest typical values, and dots mark outliers. MIN, MEDIAN and MAX check the figures.
Key takeaways
- Box and Whisker is a built-in chart in Excel 2016 and later, including Microsoft 365, on Windows and Mac.
- The box runs from the first quartile (Q1) to the third quartile (Q3); the line inside is the median.
- Points more than 1.5 × the box height beyond the box are drawn as separate outlier dots.
- The five-number summary is MIN, QUARTILE.INC(...,1), MEDIAN, QUARTILE.INC(...,3) and MAX.
- The chart's Quartile Calculation setting (inclusive or exclusive) can change where the box edges fall.
On this page+
A box plot in Excel, also called a box and whisker plot, shows how a set of numbers is spread out: where the middle half sits, where the median is, and which values are unusual. In Excel 2016 and later you make one in a few clicks with Insert > Insert Statistic Chart > Box and Whisker. This guide shows how to build it, how to read it, and how to check its numbers with formulas.
What a box and whisker plot shows#
A box plot is built from five numbers, called the five-number summary:
| Part of the chart | What it is |
|---|---|
| Bottom whisker | The lowest value that isn't an outlier |
| Bottom of the box | The first quartile (Q1): 25% of values are below it |
| Line inside the box | The median: half the values are below it, half above |
| Top of the box | The third quartile (Q3): 75% of values are below it |
| Top whisker | The highest value that isn't an outlier |
Excel also adds an X for the mean (the average) and draws outliers as separate dots. An outlier is a value far from the rest. Excel uses the common rule: a value is an outlier if it is more than 1.5 times the box height below Q1 or above Q3. The box height, Q3 minus Q1, is called the interquartile range (IQR).
Box plots are most useful for comparing groups side by side, such as delivery times by warehouse or test scores by class. A wide box means the values vary a lot; a short box means they are consistent.
How to make a box plot in Excel#
Here are seven order processing times in minutes:
| A | B | |
|---|---|---|
| 1 | Order | Minutes |
| 2 | A-101 | 34 |
| 3 | A-102 | 41 |
| 4 | A-103 | 28 |
| 5 | A-104 | 52 |
| 6 | A-105 | 39 |
| 7 | A-106 | 95 |
| 8 | A-107 | 45 |
To chart them:
- Select B1:B8, the header and the numbers. Leave out the Order column.
- Go to Insert, click the Insert Statistic Chart icon in the Charts group, and choose Box and Whisker. On a Mac, the same icon is on the Insert tab, labelled as statistical charts.
- Add a chart title and axis title with the + button next to the chart (Windows) or the Chart Design > Add Chart Element menu.
With the exclusive quartile setting (explained below), the chart shows a box from 34 to 52, a median line at 41, a mean marker near 47.7, and a dot at 95: the outlier.
To compare several groups, put each group in its own column with a header, such as North in B, South in C, and select all of them. Excel draws one box per column. The chart basics guide covers titles, axes and moving a chart.
Check the five numbers with formulas#
A chart is easier to trust when you can see the numbers behind it. These formulas give the five-number summary for B2:B8:
| Statistic | Formula | Result |
|---|---|---|
| Minimum | =MIN(B2:B8) | 28 |
| Q1 | =QUARTILE.INC(B2:B8,1) | 36.5 |
| Median | =MEDIAN(B2:B8) | 41 |
| Q3 | =QUARTILE.INC(B2:B8,3) | 48.5 |
| Maximum | =MAX(B2:B8) | 95 |
MIN and MAX return the smallest and largest values; see the MIN function guide and the MAX function guide. MEDIAN sorts the values and takes the middle one, here the 4th of 7: 28, 34, 39, 41, 45, 52, 95. QUARTILE.INC finds the quarter points; the guide to percentiles and quartiles explains how Excel calculates them, and MEDIAN and MODE covers the middle value in more depth.
Why the chart's box may not match QUARTILE.INC#
You may notice that the box runs from 34 to 52, not 36.5 to 48.5. That happens because the chart has its own Quartile Calculation setting. Right-click the box, choose Format Data Series, and look under Series Options:
| Setting | How it works | Q1 and Q3 for this data |
|---|---|---|
| Exclusive median | Leaves the median out of each half when the count is odd | 34 and 52 |
| Inclusive median | Keeps the median in both halves when the count is odd | 36.5 and 48.5 |
In this example, the inclusive setting matches QUARTILE.INC and the exclusive setting matches QUARTILE.EXC. Neither is wrong; they are two accepted methods, and the difference shrinks as you add more data. Check which one is selected before you compare the chart with a table of formulas, and use the same method in both.
The same panel has check boxes to show or hide the inner points, outlier points, mean markers and a mean line.
How Excel finds outliers#
Excel draws a value as an outlier when it falls more than 1.5 × IQR outside the box. With the exclusive setting:
- IQR = 52 − 34 = 18, so 1.5 × IQR = 27.
- Upper limit = 52 + 27 = 79. The value 95 is above it, so it's an outlier.
- Lower limit = 34 − 27 = 7. Nothing is below it.
The whiskers stop at the furthest values inside the limits: 28 at the bottom and 52 at the top. That's why the top whisker looks very short here.
An outlier is a signal to look closer, not a mistake to delete. Order A-106 may have been a genuine delay, or a typing error such as 95 instead of 59. Check the source before you remove anything. Outliers also explain why the mean (47.7) sits above the median (41): one large value pulls the average up. The article on average vs median explains which to report, and the standard deviation guide shows another way to measure spread.
Box plots in older versions of Excel#
The Box and Whisker chart arrived in Excel 2016. In Excel 2013 or earlier, the chart type isn't there. You can still build one by hand: calculate the five-number summary, chart the differences between them as a stacked column chart, hide the bottom segment and add error bars as whiskers. It works, but it takes a dozen formatting steps. If you can use a current version, the built-in chart is far quicker and handles outliers for you.
Practise it#
A box plot is only as good as your understanding of the numbers behind it. Practise MEDIAN and MODE in a live spreadsheet, then find averages, minimums and maximums in the AVERAGE, MIN and MAX lesson. Each formula is checked instantly. Start by finding the median order time in the exercise on this page.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
The box plot's middle line is the median. In E2, find the median order time in B2:B8 with MEDIAN.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Minutes | |||||
| 2 | Median | |||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 | ||||||
| 10 | ||||||
| 11 |
Tip: MEDIAN only needs the range: =MEDIAN(B2:B8). It sorts the values for you and takes the middle one.
Practise this in a lesson
Frequently asked questions
How do I make a box plot in Excel?
Select the column of numbers, including its header, then go to Insert > Insert Statistic Chart > Box and Whisker. It needs Excel 2016 or later, including Microsoft 365.
How do I read a box and whisker plot?
The box covers the middle 50% of values, from Q1 to Q3. The line inside is the median and the X is the mean. Whiskers stretch to the lowest and highest values that aren't outliers, and dots beyond them are outliers.
How does Excel decide what is an outlier in a box plot?
A value is drawn as an outlier when it lies more than 1.5 times the interquartile range (Q3 minus Q1) below Q1 or above Q3. The whiskers stop at the furthest values inside those limits.
Why don't the box plot quartiles match QUARTILE.INC?
The chart has a Quartile Calculation setting with inclusive and exclusive options. They can give different Q1 and Q3 values. Inclusive median tends to match QUARTILE.INC, and exclusive median tends to match QUARTILE.EXC.
Can I make a box plot in Excel 2013 or earlier?
Not as a built-in chart. You can build one from a stacked column chart using the five-number summary, but it takes several formatting steps. Upgrading to a current version is much simpler.