Create a Box Plot in Excel: Steps and Interpretation

To create a box plot in Excel for Microsoft 365 on Windows, put each group of numeric observations in its own column, select the data and headers, then choose Insert > Insert Statistic Chart > Box and Whisker. Excel creates the chart automatically; use Format Data Series to adjust quartile calculations, show means or individual points, and control how outliers appear.

What a box plot shows

A box plot, also called a box-and-whisker plot, summarizes the distribution of a numeric dataset. It is useful when you want to compare spread, typical values, and unusually high or low observations across groups without plotting every value as a separate chart mark.

The box runs from the first quartile (Q1, the 25th percentile) to the third quartile (Q3, the 75th percentile). A line inside the box marks the median. The distance from Q1 to Q3 is the interquartile range, or IQR, which covers the middle half of the observations. A longer box indicates greater spread among those central values.

In Excel’s standard box-and-whisker chart, whiskers reach to the lowest and highest data points that are not classified as outliers under the chart’s 1.5-IQR rule. Points beyond those whiskers can be displayed individually as outliers. They are not automatically errors: an unusual value may be valid and important. The chart can also show individual observations and the mean, depending on the series settings.

Prepare data in a chart-friendly layout

Use one column per group, with a descriptive heading in the first row and numeric observations underneath. The columns do not have to contain the same number of values. For example, a store comparison might have headers Store A, Store B, and Store C, with daily sales figures below each heading. Select the full rectangular range, including the headings, before inserting the chart.

For a comparison by month, each month could be a column and each row could represent one day’s measurement. The chart then produces one box for each month. If your groups are arranged in rows instead, select the data and use Chart Design > Switch Row/Column if Excel initially interprets the layout in the wrong direction.

Keep each column to one kind of measurement, using consistent units such as seconds, dollars, or degrees.
Use a clear heading for each group so the chart labels make sense.
Remove explanatory text, subtotals, and stray values from the observation area. Keep notes outside the selected range.
Check for numbers stored as text, blank cells, and data-entry mistakes before interpreting the chart.

Excel needs numeric observations to calculate the distribution. Text labels within a data column are not measurements and may be ignored or affect the chart’s interpretation. If the source includes a total row, exclude it: a total is not another observation and can distort the quartiles and scale.

Create the chart in Excel

Arrange the observations in separate columns and add a header to each column.
Select the cells containing the headers and values. If there are unrelated cells next to the data, select only the required range.
On the ribbon, choose Insert > Insert Statistic Chart > Box and Whisker. The chart appears on the worksheet.
Use Chart Design > Add Chart Element to add or change a chart title and axis titles. Replace a generic title with a description that identifies the measure and groups.
If the groups are reversed, select the chart and choose Chart Design > Switch Row/Column.

To edit the plot’s statistical display, right-click a box in the chart and choose Format Data Series. The Format Data Series pane includes options for displaying outlier points, inner points, mean markers, and a mean line. It also offers a quartile calculation choice. The exact pane wording can vary slightly by Excel build, but the controls are attached to the data series rather than to an individual point.

On Excel for Mac, the chart is also available in current desktop versions through the Insert ribbon’s statistical chart options; select Box and Whisker. The ribbon arrangement can differ from Windows. Excel for the web may expose fewer chart-creation or formatting controls than the desktop app, so use desktop Excel if you cannot find the chart type or a required series setting. Box-and-whisker charts are available in Excel 2016 and later desktop editions, though menu details vary by version.

Choose quartile settings and understand outliers

Excel offers inclusive and exclusive median quartile calculations. These are different statistical conventions for locating quartiles, especially in small datasets, and can produce different box boundaries. Inclusive calculations include the median as part of the lower and upper halves when splitting an odd-sized dataset; exclusive calculations leave it out. For an even number of observations, the methods can still use different percentile interpolation rules.

For example, suppose A2:A9 contains 4, 5, 7, 8, 10, 12, 15, and 30. With this cell layout, the inclusive first quartile is calculated by =QUARTILE.INC(A2:A9,1), which returns 6.5; the inclusive third quartile is calculated by =QUARTILE.INC(A2:A9,3), which returns 12.75. The median is 9, and the IQR is 6.25. The upper 1.5-IQR fence is 22.125, so 30 is beyond that fence and is treated as an outlier under this method. The whisker reaches the largest non-outlier value, 15.

That example explains why a point may appear beyond a whisker, but it does not establish that the point should be removed. Investigate whether it is a typo, a measurement problem, or a genuine extreme observation. Keep the value if it is valid and relevant; if you exclude a value for a specific analysis, document the reason. Also remember that a box plot does not show the full shape of a distribution as clearly as a histogram can, particularly when there are multiple clusters.

Format the plot for clear comparisons

Good formatting makes the statistical comparison easier to read without changing the underlying data. Give the chart a concise title, use an axis title when the unit is not obvious, and keep group labels readable. If the chart compares groups measured on the same scale, retain a shared value axis. A truncated or inconsistent axis can exaggerate apparent differences.

Use Format Data Series to decide whether to include mean markers or a mean line. The median is the line within the box; the mean is a separate statistic and may be pulled toward extreme values. Showing both can help explain why they differ, but adding every optional point can make a chart crowded. Inner points show observations inside the whiskers, while outlier points identify values beyond them. They are most useful when the sample is small enough that individual values remain legible.

For a chart with many categories, widen the chart, shorten lengthy group names, or split the comparison into a few charts. Avoid relying on color alone to identify groups, especially if the chart may be printed in grayscale. If a legend repeats information already stated on the horizontal axis, remove it to reduce clutter. After formatting, check that no category has been omitted and that the axis scale still makes the differences understandable.

How to read and compare the boxes

Start with the medians to compare the central value of each group. Then compare box heights to assess the spread of the middle half of the observations. A group with a higher median is generally centered at a higher value, while a taller box has a larger IQR. The whiskers add information about the non-outlying range, and separate points reveal observations beyond that range.

For instance, if delivery times for two services have similar medians but one box is much taller, their typical delivery times may be similar while the second service is less consistent across the central half of its deliveries. If one service has several high outliers, those unusually long deliveries may matter even if its median looks favorable. A box plot alone does not establish why a difference exists or whether it is statistically significant; consider sample sizes, collection methods, and other relevant evidence.

Do not interpret the mean marker as a replacement for the median, or assume that every point outside the box is an outlier. The box itself contains the central 50 percent of values; points between the box and whisker ends are not classified as outliers by the displayed rule. The definition of quartiles and the 1.5-IQR convention are useful summaries, not universal tests for bad data.

Common problems and practical fixes

The chart option is missing: Check that you are using a desktop edition of Excel 2016 or later and that you opened Insert’s statistical chart menu. If the option is unavailable in a browser, open the workbook in the desktop app.
Excel makes one box instead of one per group: Check whether each group is in its own column. Use Chart Design > Switch Row/Column to change Excel’s interpretation, or rearrange the source data.
Headers appear as data: Include the headings in the selection and verify that Excel has treated the first row as series names. Remove any extra title or note row from the selected range.
A box or whisker looks unexpected: Verify the data values, the chosen inclusive or exclusive quartile method, and whether outlier and inner points are displayed. One extreme observation can also stretch the value axis.
Different tools give slightly different quartiles: Check the quartile convention and interpolation method. Small samples are particularly sensitive to these choices, so state the method when reporting results.

When a manual summary or another chart helps

If a recipient needs only a compact report, a table of group medians, quartiles, and IQRs may be easier to audit than a chart. Excel’s QUARTILE.INC and QUARTILE.EXC functions can calculate quartiles for a selected range; the chart’s setting should match the method used in any accompanying table. With the example values in A2:A9, =QUARTILE.EXC(A2:A9,1) returns 5.5, while =QUARTILE.EXC(A2:A9,3) returns 14.25. These differ from the inclusive results because the conventions place quartiles differently.

A histogram is a better choice when the shape of one distribution matters, such as whether it is skewed or has multiple peaks. A dot plot can be clearer for a very small sample because it shows each observation directly. A box plot is especially useful for comparing several groups on the same measurement, but it compresses detail. In older Excel versions without a built-in box-and-whisker chart, a manual chart can be constructed from calculated quartiles and a stacked column chart, but it requires careful setup and is easier to misread or break when data changes. A summary table or a newer Excel version is often the safer alternative.

Frequently Asked Questions

Can a box plot compare groups with different sample sizes?
Yes. Each group can contain a different number of numeric observations. The chart compares their summaries, but a box plot does not communicate sample size clearly by itself, so include counts in labels or an accompanying table when group sizes matter.

Does Excel show the mean or the median by default?
The line inside the box represents the median. Mean markers and a mean line are optional series settings in Format Data Series; they are separate from the median and can be enabled when useful.

Why does Excel show a value as an outlier?
Under the standard box-plot rule, a value beyond 1.5 times the IQR below Q1 or above Q3 is treated as an outlier and can be shown beyond the whisker. Check the quartile setting and the source value, but do not delete a valid observation solely because it is marked as an outlier.

Can I make a box plot in Excel for the web?
Web availability and editing controls can differ from the desktop app and may change over time. If you cannot create or format the chart with the controls available in your browser, open the workbook in Excel for Windows or Mac and use the Insert statistical chart menu.

What should I do if the groups run across rows?
Select the chart and choose Chart Design > Switch Row/Column. If that does not produce clear category labels, rearrange the source so each group has its own column with a heading.

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.