How to Make a Box Plot in Excel (Step by Step)

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

An excel box plot shows the spread of a numeric column as a box, a median line, and two whiskers. Excel builds it from your raw data in a few clicks, and you can reproduce every part of it with quartile formulas. This guide covers the chart, the formulas behind it, and how to read both.

Quick Answer

Put your numbers in one column with a header, select that range, then go to Insert > Insert Statistic Chart > Box and Whisker [1].
The box spans the first quartile (Q1) to the third quartile (Q3), and the line inside the box is the median [1].
The whiskers reach the smallest and largest values that fall within 1.5 times the interquartile range from the box edges.
Points beyond that distance are plotted as outliers, so a long tail shows up as separate dots.
To verify the chart, add =QUARTILE.INC(range,1), =QUARTILE.INC(range,2), and =QUARTILE.INC(range,3) in empty cells and compare, after setting the chart's Quartile Calculation option to Inclusive median (the default, Exclusive median, matches QUARTILE.EXC).

Before You Start

You need one column of numbers per group you want to compare. A single column gives one box. Two or more columns side by side give one box per column, which is how you compare groups such as test scores from different classes [1].

Keep the layout simple. Put a header in the first row and the values directly below it, with no blank rows and no text mixed into the number column. Excel reads the selected range as your data series, so stray labels or totals inside the range will distort the quartiles.

The box and whisker chart is a built-in chart type, so you do not need the Analysis ToolPak or any add-in. If you want to see the underlying numbers before you chart them, the formula route in the Worked Example below is the fastest check.

Step by Step

Enter your data in a single column, with a header in the first cell. For example, put the header in B1 and the values in B2 down to the last row.
Select the data range, including the header row.
On the ribbon, select the Insert tab, then select the Statistical chart icon, then select Box and Whisker [1].
With the chart selected, go to Chart Design > Add Chart Element > Data Labels > Outside End to print the values next to the boxes.
Still on Chart Design, go to Add Chart Element > Chart Title > Above Chart and type a title that names the variable and the unit.
Select a box on the chart, then use the Format tab to change the fill, border, or gap width if you want the boxes wider or narrower [1].

If you do not see the Chart Design and Format tabs, select anywhere inside the chart to bring them back to the ribbon [1].

Worked Example

The dataset is a lab timing study: 20 reaction times in seconds, one measurement per trial. The table below holds the raw values in column B and the summary statistics in columns C through H.

Row	A: Reaction #	B: Time (s)	C: Q1	D: Median	E: Q3	F: IQR	G: Lower Whisker	H: Upper Whisker
1	Reaction #	Time (s)	Q1	Median	Q3	IQR	Lower Whisker	Upper Whisker
2	1	12.4	`=QUARTILE.INC(B2:B21,1)` -> 12.58	`=QUARTILE.INC(B2:B21,2)` -> 13.30	`=QUARTILE.INC(B2:B21,3)` -> 14.28	`=E2-C2` -> 1.70	`=MIN(IF(B2:B21>=C2-1.5*F2,B2:B21))` -> 11.80	`=MAX(IF(B2:B21<=E2+1.5*F2,B2:B21))` -> 15.60
3	2	13.1
4	3	11.8
5	4	14.2
6	5	12.9
7	6	15.6
8	7	13.4
9	8	12.1
10	9	14.8
11	10	13.7
12	11	12.6
13	12	15.1
14	13	13.9
15	14	12.3
16	15	14.5
17	16	13.2
18	17	12.8
19	18	15.3
20	19	13.6
21	20	12.5

Each formula returns the value shown after the arrow.

C2 gives the first quartile, the 25th percentile of the reaction times: 12.575 seconds (12.58 at two decimals).
D2 gives the median, the 50th percentile: 13.30 seconds.
E2 gives the third quartile, the 75th percentile: 14.275 seconds (14.28 at two decimals).
F2 gives the interquartile range, Q3 minus Q1: 1.70 seconds.
G2 gives the smallest reaction time within 1.5 times the IQR below Q1: 11.80 seconds.
H2 gives the largest reaction time within 1.5 times the IQR above Q3: 15.60 seconds.

The whisker rule is the standard one:

$$ \text{lower fence} = Q1 - 1.5 \times IQR, \qquad \text{upper fence} = Q3 + 1.5 \times IQR $$

With Q1 at 12.575, Q3 at 14.275 and the IQR at 1.70, the lower fence sits at 10.025 and the upper fence at 16.825. Every value in this column falls inside those fences, so the whiskers run from the minimum of 11.80 to the maximum of 15.60 and the chart shows no outlier dots.

To build the chart from this sheet, select B1:B21, then go to Insert > Charts > Insert Statistic Chart > Box and Whisker. With the chart selected, go to Chart Design > Add Chart Element > Data Labels > Outside End. Then go to Chart Design > Add Chart Element > Chart Title > Above Chart and type "Lab Reaction Times".

The formulas match the chart once you set Format Data Series > Series Options > Quartile Calculation to Inclusive median. The default, Exclusive median, matches QUARTILE.EXC and can give slightly different box edges. If you want to see the same shape drawn from summary statistics instead of raw values, the Box Plot Maker takes quartiles and whisker values directly.

Other Ways to Do It

The built-in chart is the fastest route, but it is not the only one.

If you want full control over the drawing, compute Q1, the median, Q3, and the whisker ends with formulas, then build a stacked column chart with an invisible bottom segment. That approach lets you place the box exactly where you want it and label each part. The general chart-building steps are the same ones covered in how to make a chart in Excel.

If you only need the numbers and not the picture, the six formulas above are enough. They also work as a check on the built-in chart when a box looks wrong. For a wider treatment of the statistic itself, including how quartile methods differ, see how to make and read a box plot.

When you are comparing several groups, keep each group in its own column and select all of them at once. Excel draws one box per column on a shared axis, which is the clearest way to compare distributions side by side.

Troubleshooting

The chart shows one flat box. Your selected range probably includes a header row that Excel read as a value, or the numbers are stored as text. Check that the cells are right-aligned by default, which indicates numeric values.

The whiskers look shorter than your data range. That is expected when outliers exist. Values beyond 1.5 times the IQR are drawn as separate points, so the whiskers stop at the fence.

The quartiles do not match another tool. Different software uses different quartile definitions. Even inside Excel, the chart offers two methods under Quartile Calculation: Inclusive median, which matches QUARTILE.INC, and Exclusive median (the default), which matches QUARTILE.EXC. Another package may report slightly different Q1 and Q3 on the same data.

The box is very narrow or very wide. That reflects the spread of your data, not an error. A narrow box means the middle half of the values are tightly clustered.

You cannot find the chart type. The box and whisker chart lives under the statistical chart icon on the Insert tab, not under the standard column or bar chart buttons [1].

Common Mistakes

Selecting the wrong range. If you include a total row or a label column, Excel treats those as data. Select only the header and the numeric values.
Reading the whiskers as the full range. The whiskers stop at the most extreme values inside the fences, so they are not always the minimum and maximum. Outliers sit beyond them as dots.
Confusing the median with the mean. The line inside the box is the median. A mean line would sit elsewhere, especially in skewed data.
Comparing boxes built from different sample sizes without saying so. A box from 10 values and a box from 1,000 values look similar but carry very different precision.
Checking a default chart with QUARTILE.INC. The chart's default Exclusive median setting matches QUARTILE.EXC. Either switch the chart to Inclusive median or check it with QUARTILE.EXC. The older QUARTILE function gives the same results as QUARTILE.INC.
Leaving the axis starting away from zero without a note. Truncated axes exaggerate differences between groups.

Limitations

The built-in box and whisker chart hides the sample size. Two boxes can look identical while one rests on 8 observations and the other on 800, and the chart gives no signal about which is which. Always report the count alongside the plot.

The chart also compresses the shape of the distribution into five numbers plus any outliers. Two very different datasets, one with a single peak and one with two, can produce nearly identical boxes. If the shape matters for your question, pair the box plot with a histogram or a dot plot. The steps for the histogram are in how to make a histogram in Excel.

Finally, the whisker rule is a convention, not a law. The 1.5 times IQR fence is standard, but it is a choice, and a different multiplier would move the whisker ends and change which points count as outliers.

Frequently Asked Questions

How do I make a box plot in Excel with multiple series?

Put each group in its own column with a header, select all the columns together, then go to Insert > Insert Statistic Chart > Box and Whisker [1]. Excel draws one box per column on a shared value axis, which makes the groups directly comparable. Keep the columns the same length where possible so the comparison is fair.

What do the whiskers represent in an Excel box plot?

The whiskers run from the smallest value inside the lower fence to the largest value inside the upper fence. The fences sit at Q1 minus 1.5 times the IQR and Q3 plus 1.5 times the IQR. Any value outside those fences is plotted as a separate point instead of extending the whisker.

Why does my Excel box plot show dots above the whisker?

Those dots are outliers by the 1.5 times IQR rule. A point above the upper fence is far enough from the middle half of the data that Excel flags it. This does not mean the value is an error. It means the value is unusual relative to the rest of the column, and you should decide whether to keep, investigate, or exclude it.

Does Excel use the same quartiles as other software?

Not always. Excel itself offers two methods: QUARTILE.INC (the chart's Inclusive median option) and QUARTILE.EXC (the chart's default Exclusive median option). Other tools may use a different definition, which shifts Q1 and Q3 slightly. If you are reproducing someone else's numbers, confirm which quartile method they used before comparing.

Can I add a mean marker to a box plot in Excel?

The built-in chart does not include a mean marker by default. You can compute the mean with =AVERAGE(range) and add it as a separate series or a text annotation. If you need a mean line drawn inside the box, build the plot manually from a stacked column chart so you control every element.

References

Create a box and whisker chart | Microsoft Support