How to Make a Box Plot in Excel

In Excel for Microsoft 365 on Windows, select your data with a heading for each group, then choose Insert > Insert Statistic Chart > Box & Whisker. Excel creates a box plot showing the median, quartiles, spread, and any points it identifies as outliers; you can adjust its display through the chart’s Format Data Series pane.

What a box plot shows

A box plot, also called a box-and-whisker plot, summarizes the distribution of numerical values. Each box represents the middle half of a group’s observations: its lower edge is the first quartile (Q1), and its upper edge is the third quartile (Q3). A line inside the box marks the median, or middle value. The distance between Q1 and Q3 is the interquartile range, often abbreviated IQR.

Whiskers extend beyond the box to show the range of values that are not classified as outliers under the chart’s rule. In Excel’s standard box-and-whisker chart, points beyond 1.5 times the IQR from a quartile are shown separately as outliers. This makes it easier to compare typical values and variation across groups than it would be to compare several lists of raw numbers.

A box plot does not show every observation unless you turn on inner points. Nor does it establish why a value is unusually high or low. An apparent outlier may be a valid measurement, a data-entry error, or evidence of a subgroup. Check the underlying data before drawing conclusions.

Arrange and check your data

For the clearest chart, put each group in its own column and put a descriptive heading in the first row. For example, columns headed Morning, Afternoon, and Evening could contain one measurement per row. The columns can have different numbers of observations; leave unused cells blank rather than filling them with zeros, since zero is a real value and would change the distribution.

Include the headings when you select the data, and keep the observations as numbers. Remove explanatory text from the data area, check for numbers stored as text, and decide how to handle missing values before charting. A blank cell is not the same thing as a recorded zero. If your source data puts groups in rows instead of columns, Excel may plot the wrong direction at first; you can correct that with Switch Row/Column.

Use one column per group, with the group name at the top.
Keep one observation in each cell below its heading.
Use consistent units and measurement rules across groups.
Check that unusually large or small values are genuine before interpreting them.

Suppose you are comparing delivery times for three routes. Put Route A, Route B, and Route C in separate columns and enter each delivery time below the relevant heading. The chart then produces one box for each route, making differences in median and spread visible without requiring the same number of deliveries for every route.

Insert the box-and-whisker chart

Select the full data range, including the headings. If the columns are not next to each other, select one range and then use Ctrl-click to add the other ranges.
On the Windows ribbon, choose Insert > Insert Statistic Chart > Box & Whisker. Excel inserts a chart on the worksheet.
Check that each box represents the group you intended. If the group names appear as separate observations, select the chart and choose Chart Design > Switch Row/Column.
Use the chart title and axis labels to explain the measure and units. For example, write Delivery time (minutes), rather than leaving the reader to guess what the values represent.
Resize or move the chart as needed, and save the workbook so the chart remains connected to its source data.

In Excel for Mac, select the range and look under Insert for the Statistical Chart or Box & Whisker option; ribbon wording and pane placement can vary slightly by release. The instructions above describe Excel for Microsoft 365 on Windows. Excel for the web has a different interface, and availability of chart types and formatting controls can vary, so use the desktop app if you cannot find the box-and-whisker command or need the full series settings.

Format the boxes, points, and axes

Click a box in the chart, right-click, and choose Format Data Series to open its settings. In Series Options, Excel provides choices for quartile calculation and display options such as showing outlier points, inner points, mean markers, or a mean line. The exact pane layout may differ slightly, but selecting the data series rather than the chart background is the important step.

Outlier points: Keep these visible when you want unusual observations to stand out. An isolated dot is not automatically a mistake.
Inner points: Turn these on when readers need to see individual observations as well as the summary. With many values, points may overlap and make the chart busy.
Mean markers or line: Show the mean if it answers the question you are investigating. The mean can be pulled by extreme observations, so do not confuse it with the median.
Quartile calculation: Choose the inclusive or exclusive median method consistently when comparing charts. The difference matters most with smaller samples.

You can add or edit the chart title and axis titles from Chart Design > Add Chart Element. Use a vertical scale that makes differences readable, but avoid narrowing it so much that ordinary variation looks dramatic. If you have several groups with very different ranges, explain any axis choices that might affect a reader’s interpretation.

Read a concrete example

Imagine one group has the sorted values 4, 5, 6, 7, 8, 9, 10, and 30. With the inclusive quartile method, Q1 is 5.75, the median is 7.5, and Q3 is 9.25. The IQR is therefore 3.5. Under the 1.5-IQR rule, the upper outlier fence is 14.5, so 30 is plotted as an outlier; the upper whisker reaches 10, the largest value within the fence.

You do not need to calculate these statistics separately to make a chart. However, calculating them can help you verify a result or explain why a point appears outside a whisker. With the eight values in A2:A9, the inclusive first quartile is returned by =QUARTILE.INC(A2:A9,1); for the example, the result is 5.75. The matching third-quartile formula is =QUARTILE.INC(A2:A9,3), which returns 9.25.

With the same example values in A2:A9, the median formula =MEDIAN(A2:A9) returns 7.5. With those values still in A2:A9, the IQR formula =QUARTILE.INC(A2:A9,3)-QUARTILE.INC(A2:A9,1) returns 3.5. These worksheet formulas illustrate one quartile convention; if the chart is set to exclusive quartiles, its box edges may not match calculations made with QUARTILE.INC.

Choose a quartile method consistently

Inclusive and exclusive quartile calculations are different ways of locating quartiles in a sample. They can produce different box boundaries, especially when a group has few observations. Neither setting changes the original values; it changes how the quartiles are calculated and therefore how the box is drawn. If you are comparing groups, use the same setting for all of them and state the method when the distinction is important.

A box plot is a descriptive summary, not a test of statistical significance. Two boxes that look different do not by themselves prove that the groups differ in a meaningful or reliable way. Consider the sample sizes, how the data were collected, and whether the measurement conditions were comparable. With only a few observations, showing the inner points can make the small sample more apparent than a box alone would.

Fix common chart problems

The chart has one box per observation: Excel may be reading rows and columns in the opposite direction from what you intended. Use Chart Design > Switch Row/Column, or rearrange the source data so groups occupy columns.
A group is missing or looks empty: Check that its heading and numerical observations were included in the selected range. Confirm that values are numbers, not text that only looks numeric.
The whiskers or boxes look unexpected: Verify the quartile calculation setting, the selected range, and whether extreme values are valid. A single large value can be an outlier while the upper whisker remains much shorter.
The chart is crowded: Reduce the number of groups on one chart, enlarge the chart, or turn off inner points when individual observations are not needed. Keep the group labels readable.
Zeros or blanks distort the display: Remove placeholder zeros if they mean no measurement, and use blank cells for missing observations. Do not replace missing values with zero unless zero was actually observed.

If you edit the source values, Excel generally updates the chart because it is linked to the selected range. If new rows are outside that range, they may not appear automatically. Select the chart and use Chart Design > Select Data to review or adjust its source range.

When to use another chart

Use a box plot when your main question is how the center and spread compare between groups, especially when groups contain many observations. A histogram is more useful when you want to inspect the shape of one distribution, such as whether it has one peak or a long tail. A dot plot or scatter chart can be preferable when every observation matters and the sample is small.

For a report, a box plot often works best alongside a short note stating the units, sample size, and quartile method if relevant. If readers need the exact values behind a striking outlier, include a data table or show inner points. The chart summarizes; the source data and context make the summary interpretable.

Frequently Asked Questions

Does Excel have a built-in box plot?
Yes. In current desktop versions that include the chart type, select the data and choose Insert > Insert Statistic Chart > Box & Whisker. Excel builds the chart without requiring you to calculate quartiles by hand.

How do I make a box plot with multiple groups?
Put each group’s numeric observations in a separate column and include a heading above each column. Select the range and insert a Box & Whisker chart. If Excel plots the groups in the wrong direction, use Chart Design > Switch Row/Column.

What do the dots outside the whiskers mean?
They are points Excel classifies as outliers under the chart’s 1.5-IQR rule. They may be valid observations, so check the original data and measurement context rather than deleting them automatically.

Why does my box plot look different from another program’s?
Programs can use different quartile conventions or outlier display rules. In Excel, check whether the series uses inclusive or exclusive quartiles, then compare that choice and the underlying data with the other program.

Can I make a box plot in Excel for the web?
Some chart options and formatting controls differ from desktop Excel and can depend on the web version available to your account. If you do not see Box & Whisker or its series settings, open the workbook in desktop Excel for Microsoft 365.

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.