Box Plot in Excel: Example, Steps, and Interpretation

In Excel for Microsoft 365 on Windows, put each group of values in its own column, select the data with its headers, then choose Insert > Insert Statistic Chart > Box and Whisker. For example, a chart comparing two groups can show their medians, quartiles, spread, and any unusually high or low observations at a glance.

What a box plot shows

A box plot, also called a box-and-whisker plot, summarizes the distribution of numerical data. The box extends from the first quartile (Q1) to the third quartile (Q3), so it contains the middle half of the observations. A line inside the box marks the median. The interquartile range, or IQR, is Q3 minus Q1 and measures the width of that middle half.

The whiskers show the range of observations that are not classified as outliers under the chart’s rule. In Excel’s standard box-and-whisker chart, the whiskers reach the most extreme data points within 1.5 times the IQR below Q1 or above Q3. Points beyond those limits can appear individually as outliers. This means a whisker does not necessarily reach the smallest or largest value in the dataset.

Box plots are useful when you want to compare distributions across categories, such as delivery times by carrier or test scores by class. They compress many values into a chart, but they do not show every detail: two datasets can have similar boxes while differing in their underlying shapes.

A worked Excel example

Suppose you want to compare the results for two groups. Enter the following values in Excel, with the group names in the first row and one observation per cell:

Group A: 12, 14, 15, 16, 18, 20, 22, 24, 26, 28, 80.
Group B: 10, 13, 15, 17, 18, 19, 20, 21, 23, 25, 27.

Put the Group A values in A2:A12 and the Group B values in B2:B12, with the headers Group A and Group B in A1 and B1. For a check using Excel’s inclusive quartile method, the layout for the next calculation is Group A in A2:A12. The formula =QUARTILE.INC(A2:A12,1) returns 15.5 for Q1. With the same layout, =MEDIAN(A2:A12) returns 20, and =QUARTILE.INC(A2:A12,3) returns 25 for Q3.

For Group A, the IQR is 25 − 15.5 = 9.5. The upper outlier fence is Q3 + 1.5 × IQR, or 25 + 14.25 = 39.25, so 80 is beyond the fence and should appear as an outlier. The upper whisker reaches 28, the largest observation within the fence. Group B has Q1 = 16, a median of 19, and Q3 = 22 when calculated inclusively; its values fall within the corresponding fences. The example makes it easy to see that Group A has one unusually high result and a higher median than Group B.

Prepare the data before inserting the chart

Use one column per group, put a clear category name in the first row, and keep each observation in a separate cell. The groups do not have to contain the same number of values, though it helps to keep the layout tidy. Avoid putting totals, averages, or explanatory text among the observations: Excel may treat those entries as data or fail to interpret the range as intended.

Before charting, check for values stored as text, accidental spaces, duplicate rows, and blank cells that are actually meant to represent missing data. A blank is not the same as a measured zero. Do not fill missing observations with zero unless zero is genuinely the measurement; doing so can distort the median and quartiles. If the groups are arranged in rows instead of columns, consider transposing them so each group is a column, or use the chart’s data selection controls to confirm that Excel has interpreted the series correctly.

Create a box-and-whisker chart

Select the complete range, including the category headers. In this example, select A1:B12.
On the ribbon, choose Insert > Insert Statistic Chart > Box and Whisker.
Check that the horizontal axis shows Group A and Group B and that each group has its own box. If Excel has treated each row as a separate series, use Chart Design > Switch Row/Column.
Add a descriptive chart title and, where useful, axis titles from Chart Design > Add Chart Element. Use a vertical axis title to identify the measurement and its units.

Excel creates the chart from the selected data; you do not need to calculate quartiles in worksheet cells first. You can still calculate them separately when you want to audit the chart, document the method, or use the statistics in another report. In Excel for Mac, the statistical chart is also available from the Insert tab, although the group name or ribbon layout can vary by release. Excel for the web may not offer the same chart-formatting controls as the Windows desktop app, so use desktop Excel if you need the detailed series options below.

Read the boxes, whiskers, and points

Start with the median lines. A higher median means the middle observation is higher; it does not mean that every value in that group is higher. Then compare the boxes: a taller box on a vertical chart indicates a larger IQR and therefore more spread among the middle 50% of values. If one box is much taller, that group’s central observations are less tightly clustered.

Next, compare the whiskers and any separate points. Long whiskers indicate values spread farther from the box, while isolated points indicate observations beyond the chart’s outlier threshold. In the example, Group A’s point at 80 is separated from the upper whisker, which ends at 28. That point is flagged by the rule, not automatically proven to be an error. Check the original record and the context before deciding whether it should be corrected or excluded.

Excel can also show mean markers or a mean line. The mean is sensitive to extreme values, so in Group A the value 80 pulls the average upward more than it shifts the median. A box plot is descriptive: it does not by itself establish why groups differ, whether a difference is statistically significant, or whether one group caused an outcome.

Choose the quartile and display options

To change a series, right-click one of its boxes and choose Format Data Series. In the Format Data Series pane, look under Series Options. Excel provides inclusive and exclusive median choices for quartile calculation. These methods can produce different quartiles, especially with small datasets. For the worked example above, select Inclusive median if you want the chart’s quartiles to match the QUARTILE.INC checks.

The same pane lets you control what the chart displays. Depending on your needs, you can show or hide outlier points and inner points, show a mean marker or mean line, and adjust the quartile calculation. Inner points are individual observations that are not classified as outliers; displaying them can reveal clusters or gaps, but a chart with many observations may become crowded. Mean markers can help when the average matters, but retain the median as a separate statistic rather than treating the two as interchangeable.

For a report, use a chart title that names the measurement and a vertical axis that includes units. Keep the scale consistent when comparing charts side by side. If the values are measured in seconds, for instance, label the axis as time in seconds rather than relying on readers to infer the units.

Box plots compared with other charts

A column chart usually emphasizes a summary such as a total or average. It can be easier to read for a small number of categories, but a chart of averages alone hides how values vary within each category. A box plot makes the median, middle spread, and potential outliers visible, making it a stronger choice when distribution matters.

A histogram shows how many observations fall into value ranges and can reveal multiple peaks or an uneven shape. A box plot is more compact for comparing several groups, but it does not display that shape directly. A scatter plot preserves individual paired observations and is better suited to exploring the relationship between two numerical variables. Choose the chart based on the question: distribution by group, frequency pattern, or relationship between measurements.

Common problems and how to fix them

The chart has the wrong categories: Confirm that headers are included and each group is in its own column. Try Chart Design > Switch Row/Column if Excel interpreted rows and columns in the opposite direction.
There is no visible outlier: The data may contain no values beyond the 1.5-IQR fences, or outlier points may be hidden in Format Data Series. Check the selected quartile method and the display settings.
The chart and worksheet quartiles do not match: Confirm whether the chart uses inclusive or exclusive quartiles. Compare like with like; different methods can give different boundaries.
A value looks like an outlier but is not shown: Being far from the median is not, by itself, the chart’s test. The point must exceed the relevant 1.5-IQR fence under the selected method.
The plot looks compressed: A very large value can stretch the vertical axis and make the boxes look small. Check that the value is valid before changing the axis scale; hiding a valid extreme value can make a comparison misleading.

Frequently Asked Questions

How do I make a box plot in Excel?

Place each category’s numerical observations in a separate column, select the range including headers, and choose Insert > Insert Statistic Chart > Box and Whisker. Then verify the category labels and format the chart as needed.

Does Excel show the mean or the median?

The median is the line inside the box. The mean is a separate optional marker or line that you can enable in Format Data Series. The median and mean can differ, particularly when the data include extreme values.

Do Excel box plot whiskers show the minimum and maximum?

Not necessarily. Excel’s standard whiskers extend to the most extreme observations within 1.5 times the IQR from the quartiles. Observations beyond those fences can be shown as separate outlier points.

Why do my box plot quartiles differ from another calculation?

Quartile results depend on the method used, and Excel lets you choose inclusive or exclusive median calculations for the chart. A comparison with worksheet functions or another program is meaningful only when the quartile methods match.

Can I make a box plot in an older version of Excel?

The built-in Box and Whisker chart is available in Excel 2016 and later desktop versions, including Microsoft 365. If your version does not include it, you can calculate the statistics and build a custom chart, but that requires more setup and careful formatting.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.