Box Plot in Excel: Step-by-Step Instructions

To make a box plot in Microsoft Excel for Microsoft 365 on Windows, arrange each group of numeric observations in its own column, select the data and headings, then choose Insert > Insert Statistic Chart > Box and Whisker. Excel creates the chart; you can then adjust its quartile method, labels, and display of outliers and mean values using the chart’s formatting options.

What a box plot shows

A box plot, also called a box-and-whisker plot, summarizes how a set of numbers is distributed. The line inside the box marks the median, or middle value. The bottom and top of the box mark the first and third quartiles, commonly called Q1 and Q3. The distance between them is the interquartile range, or IQR, which covers the middle half of the observations.

Lines extending from the box are the whiskers. In Excel’s box-and-whisker chart, whiskers generally reach the most extreme observations that are not classified as outliers under the 1.5-IQR rule. Points beyond those whiskers can appear separately as outliers. They are still data values; the chart does not automatically remove them. Depending on the options selected, Excel can also show individual inner points, a mean marker, or a line for the mean.

This summary is useful when comparing the spread and center of several groups, such as test scores across classes or delivery times across regions. It is not a display of every value by default, so a box plot is best read alongside knowledge of the sample size and how the data were collected.

Prepare the data in columns

Put each group in a separate column, with a descriptive heading in the first row. Keep the observations below the heading. For example, one column might be labeled North and another South, with each column containing the measurements for that region. Groups can have different numbers of observations; leave unused cells blank rather than entering zero, because zero is a real measurement and can change the summary.

Before selecting the range, check that the cells contain numbers rather than numeric-looking text. Remove stray spaces, explanatory notes, and subtotal rows from the data range. A blank cell is usually preferable to a text value such as “not recorded” inside a numeric series. If the source data are in a single column with a group label beside every observation, reorganize them into one column per group for the simplest chart setup.

Use one heading per series, such as North, South, and West.
Place observations directly under the corresponding heading.
Use consistent units and make sure all groups measure the same kind of thing.
Keep the original data intact so the chart can be updated when values change.

Create the box-and-whisker chart

With the data arranged in columns, follow these steps in Excel for Microsoft 365 on Windows:

Select the complete range, including the headings and all observations. If the columns are adjacent, drag across the rectangular range. For nonadjacent columns, you can select one range and then hold Ctrl while selecting another.
Open the Insert tab on the ribbon.
Choose Insert Statistic Chart in the Charts area, then choose Box and Whisker.
Inspect the chart to confirm that each intended column appears as a separate group and that the headings are used as category labels.
Click the chart title and type a clear description, such as Delivery time by region. Add axis titles if readers may not know the measurement or its units.

If Excel treats rows as groups when you intended columns, select the chart and use Chart Design > Switch Row/Column. The exact ribbon layout can vary with window size, but the command is on the Chart Design tab when a chart is selected. In Excel for Mac, select the data and use the Insert tab’s statistical chart options to choose Box and Whisker; the ribbon layout and labels can differ slightly from Windows. Excel for the web may have a different set of chart-creation and formatting controls, so use the desktop app if the statistical chart option or a needed setting is unavailable.

Format quartiles, whiskers, and points

To adjust how the plot looks, select a box in the chart and open Format Data Series. You can usually do this by right-clicking the series and choosing the formatting command, or by pressing Ctrl+1 on Windows after selecting the series. On a Mac, use Command+1. In the formatting pane, look for Series Options. Available controls include the quartile calculation and checkboxes for showing outlier points, inner points, mean markers, or a mean line.

Excel offers an inclusive- or exclusive-median quartile calculation. The choice affects where the box edges fall, particularly in small samples, and can affect comparisons with another tool or a published analysis. Use the same method across groups and state it when the chart is part of a report. Showing inner points can help readers see how many observations there are and where values cluster, though it may make a large dataset crowded. Outlier points are often worth showing because they make unusual observations visible rather than hiding them.

Improve readability by using a descriptive chart title, readable category labels, and a vertical axis scale that does not exaggerate small differences. Use a restrained color scheme; color should distinguish groups without implying a ranking that the data do not support. If you add a mean marker, explain that the mean and median are different measures and can diverge when a distribution is skewed.

Read the boxes and whiskers correctly

The median is a measure of the center, while the box describes the spread of the middle 50 percent. A taller box on a vertical chart indicates a larger IQR and therefore more variation in that central portion of the data. A median close to one edge of the box, or a longer whisker on one side, can indicate asymmetry. These visual clues are descriptive, not proof of a particular cause or a formal statistical test.

For example, suppose a delivery-time group contains 12, 14, 15, 16, 18, 19, 20, 22, 24, and 40 minutes. With the inclusive quartile method, Q1 is 15.25, the median is 18.5, and Q3 is 21.5. The IQR is 6.25. The usual upper outlier fence is Q3 plus 1.5 times the IQR, or 30.875; the value 40 is above that fence and can appear as an outlier point. The largest observation within the fence is 24, so the upper whisker reaches 24 rather than 40. This example illustrates why a point beyond a whisker is not necessarily an error: it may be a valid, unusually high measurement.

A box plot can make group distributions easier to compare, but overlapping boxes do not establish that groups are statistically identical, and separated medians alone do not establish statistical significance. Consider sample sizes, study design, and the scale of the measurement before drawing conclusions.

Check quartiles with worksheet formulas

If you want to verify the chart’s summary values, calculate quartiles in worksheet cells and match the formula method to the chart setting. For the example above, assume the ten observations are in B2:B11 and the chart uses the inclusive method. With those values in B2:B11, the first-quartile formula is =QUARTILE.INC(B2:B11,1), which returns 15.25. With the same layout, the median formula is =MEDIAN(B2:B11), which returns 18.5. With the same layout, the third-quartile formula is =QUARTILE.INC(B2:B11,3), which returns 21.5.

With the example observations still in B2:B11, the IQR can be calculated as =QUARTILE.INC(B2:B11,3)-QUARTILE.INC(B2:B11,1), returning 6.25. With those same values and the inclusive method, the upper fence formula is =QUARTILE.INC(B2:B11,3)+1.5*(QUARTILE.INC(B2:B11,3)-QUARTILE.INC(B2:B11,1)), returning 30.875. These worksheet calculations help explain the example, but a chart’s visual output depends on its selected quartile setting. If the chart uses the exclusive method, use QUARTILE.EXC for a like-for-like comparison where that function is defined for the sample.

Fix common chart problems

If the chart shows one box instead of one box per group, check whether the selected range includes the group headings and whether Excel interpreted the data orientation correctly. Use Chart Design > Switch Row/Column to change the grouping. Also confirm that the series contain numeric observations and that labels have not been included as data.

Unexpected zero or low values: Check for zeros entered to fill missing observations. Replace them with blank cells only when the value is genuinely missing.
Wrong group names: Include the heading row in the selected range, or review the chart’s data source and category labels.
No visible outlier: The data may not contain a value beyond the outlier fence, or the outlier-points option may be turned off. Check the series options.
Different quartiles than another report: Compare inclusive versus exclusive quartile methods and confirm both analyses use the same observations.
Crowded chart: Hide inner points for a large dataset, widen the chart, or reduce the number of groups shown at once.

When the chart does not update as expected after you add observations, check whether the new cells fall inside the chart’s source range. A chart based on a fixed range will not necessarily include data typed below that range. You can adjust the source selection or format the source as an Excel table before building a chart so the range can expand as rows are added.

When to use a box plot instead of another chart

A box plot is a compact way to compare medians, central spread, and potential outliers across groups. A histogram is often more informative when you need to inspect the shape of one distribution, including peaks or gaps. A dot plot or strip plot shows individual observations more directly and may be preferable for small samples. A bar chart is useful for totals or averages, but a bar alone can conceal variation within each group.

These chart types answer different questions, so choose based on what readers need to see. A box plot is especially helpful for side-by-side comparisons with many observations per group. For a very small sample, the box may summarize too few points to be revealing; showing individual data points or using a table can provide more context. If precise quartile definitions matter, document the method rather than relying on a reader to infer it from appearance.

Frequently Asked Questions

Can I make a box plot in Excel?
Yes. In Excel for Microsoft 365 on Windows, select the grouped numeric data and choose Insert > Insert Statistic Chart > Box and Whisker. If the command is unavailable in your Excel version, you may need a newer desktop version or a different way to calculate and chart the summaries.

How should I arrange data for a box plot in Excel?
Put each group in a separate column, use the first row for group names, and place numeric observations below the heading. Leave cells blank for missing observations instead of entering zero unless zero is a genuine value.

What does a dot beyond the whisker mean?
It is an observation Excel classifies as an outlier under the chart’s rule, commonly a value more than 1.5 IQR beyond a quartile. It is not automatically a mistake or a value that should be deleted.

Why do my quartiles differ from another calculation?
Different quartile conventions can place Q1 and Q3 at different values, especially with smaller datasets. Check whether the Excel chart and the comparison use inclusive or exclusive quartiles and whether they use the same data.

Can a box plot show the average?
Yes. In Format Data Series, turn on the mean marker or mean line if available. The median remains the line inside the box; the mean is a separate statistic and may be pulled toward unusually high or low values.

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.