Excel Box and Whisker Plot: How to Create One

Yes. In Microsoft Excel for Microsoft 365 on Windows, select your grouped data, then choose Insert > Insert Statistic Chart > Box and Whisker. Excel creates a chart that summarizes each group’s spread, median, quartiles, and potential outliers; you can then adjust its statistical options and labels.

What an Excel box and whisker plot shows

A box and whisker plot, also called a box plot, condenses a group of numerical observations into a visual summary. It is useful when you want to compare distributions across categories, such as delivery times by carrier, test scores by class, or monthly sales by region. Unlike a bar chart, it shows how values are spread out rather than focusing only on a total or average.

The box marks the middle half of the data. Its lower and upper edges represent the first and third quartiles, usually called Q1 and Q3. The line inside the box marks the median, or middle value. The distance from Q1 to Q3 is the interquartile range (IQR). Excel also draws whiskers and, when enabled, individual points beyond them.

A box plot is a summary, not a display of every observation. Two groups can have the same median but very different variability, and a box plot can reveal that difference quickly. It can also help you spot skew, clusters, or unusually distant observations that an average alone might conceal.

Arrange the data before inserting the chart

Put each group in a separate column, with a descriptive header in the first row and numeric observations beneath it. For example, use columns headed North, South, and West, with each region’s measurements below its heading. The groups do not have to contain the same number of observations; leave unused cells blank rather than entering zero, because a zero is a real value and affects the distribution.

Keep the observations as numbers. Text such as “not recorded” in the middle of a data column can complicate chart interpretation, so keep missing values blank or handle them separately. Avoid including totals or averages as if they were individual observations: a summary value is not part of the underlying sample and can distort the plot.

Use one column per category or group, with a clear header.
Place one observation in each cell below the relevant header.
Remove accidental spaces, text entries, and subtotal rows from the selected range.
Use consistent measurement units across the groups being compared.

If your data is arranged with groups in rows instead, select the range and insert the chart. If Excel interprets the layout in the wrong direction, select the chart and use Chart Design > Switch Row/Column. Check that the horizontal axis shows the intended groups and that each box represents a group, not an individual observation.

Create the chart in Excel

Select the full data range, including the column headers and numeric observations.
On the ribbon, choose Insert > Insert Statistic Chart > Box and Whisker. Excel inserts a chart on the worksheet.
Check the category labels and series orientation. If necessary, use Chart Design > Switch Row/Column.
Use the chart’s title and labels to explain what is measured and, where needed, the units or time period.
To change the statistical display, right-click a box and choose Format Data Series. The pane contains the box-and-whisker options.

Excel’s box-and-whisker chart type is available in modern desktop versions, including Excel 2016 and Microsoft 365. The ribbon wording or layout can differ slightly by platform and update. On Excel for Mac, look under the Insert tab for the statistical chart choices and Box and Whisker; if the command is not available in your build, open the workbook in an up-to-date desktop version of Excel. Excel for the web may display a chart but does not always provide the same chart-creation and formatting controls, so use desktop Excel for the steps above if an option is missing.

How to read the box, median, and whiskers

The median divides the ordered observations into lower and upper halves. A median line near the center of the box suggests a fairly balanced middle spread; a line close to one edge can indicate that values are more spread out on one side of the median. The box itself represents the middle 50 percent of values, so a taller box on a vertical chart means a larger IQR and more variation in that central portion.

In Excel’s standard box plot, whiskers extend to the most extreme data values that remain within 1.5 times the IQR below Q1 or above Q3. Points beyond those limits are treated as potential outliers and can appear as separate markers. This convention is a screening rule, not proof that an observation is wrong. A distant value may be a valid rare event, a measurement error, or a value from a different process; investigate its source before deciding what it means.

Whisker endpoints are therefore not necessarily the sample minimum and maximum. If the data contains no observations beyond the 1.5-IQR limits, the whiskers may reach the minimum and maximum. If potential outliers exist, the whiskers stop at the most distant values still within those limits, while the farther values are drawn separately when outlier points are shown.

Quartiles are calculated from ranked data, and there are different conventions for locating them, particularly in small samples. Excel provides a quartile calculation choice in the Format Data Series pane. Inclusive and exclusive methods can place the box edges differently, so document the selected method when exact quartile values matter or when comparing results with another application.

Customize the statistical display

Right-click a box and select Format Data Series to show or hide chart details. Depending on the Excel build, the pane includes settings for inner points, outlier points, mean markers, mean lines, and quartile calculation. Inner points show individual observations inside the whiskers; they can be useful for small samples but may overlap when there are many values. Outlier points make the 1.5-IQR rule visible, while mean markers or lines add an average that is distinct from the median.

Keep the purpose of the chart in mind when changing these options. A mean marker can help readers compare averages, but it should not be mistaken for the median line. Hiding outlier markers changes what the audience sees, not the underlying values in the worksheet. If you suppress points to reduce clutter, explain that choice when the chart is used in a report where readers might otherwise assume all unusual observations were assessed.

Use the chart title and axis labels to add context. A title such as “Order processing time by warehouse (hours)” is more informative than “Box Plot.” If the distributions differ greatly in scale, check that the vertical axis still allows the boxes to be compared. One very large observation can expand the axis and make the central boxes look compressed; that is a visual consequence of the scale, not evidence that the smaller groups have no variation.

Example: comparing two groups

Suppose one column contains processing times for Warehouse A and another contains times for Warehouse B. A box plot can show whether the typical time differs, whether one warehouse has more variation, and whether either group has unusually long cases. Imagine both median lines sit near the same value, but Warehouse B has a taller box and longer whiskers. The typical middle value is similar, yet the middle half and broader spread of B’s times are less consistent.

Now imagine one very long processing time appears as a separate point above Warehouse A’s upper whisker. That point is flagged by the chart’s outlier rule, but the chart does not explain why it occurred. It could be a data-entry error, a holiday backlog, or a legitimate exceptional order. Check the source record and operational context rather than deleting it automatically. If the value is valid, retaining it may be essential to an honest comparison.

For a small sample, show inner points if the chart remains readable, because individual observations can help readers see how much evidence supports the summary. For large samples, points may overlap heavily; the box, whiskers, and a carefully chosen axis can communicate the distribution more clearly. In either case, include sample size when it is important to judge how stable the comparison may be.

Common problems and how to fix them

The chart shows one box instead of several: Check that each group is in its own column and that the headers and observations were all selected. Try Chart Design > Switch Row/Column if Excel has interpreted the orientation differently.
Zeros appear unexpectedly: Look for cells containing 0 where data was actually missing. Blank cells and zeros have different meanings in a distribution.
The chart labels are unclear: Add descriptive headers before creating the chart, then edit the chart title and axis title to include the measure and units.
The box looks flat or tiny: Check for an extreme value that has stretched the value axis. Confirm the observation is valid, and consider whether separate charts or a clearly explained axis adjustment would make the comparison more legible.
Quartiles differ from another tool: Compare the quartile convention and sample handling. Small samples are particularly sensitive to inclusive versus exclusive quartile calculations.
There are no visible outlier dots: The data may contain no values beyond the whisker limits, or the display option may be turned off. In Format Data Series, check Show outlier points.

When a box plot is useful—and when to choose another chart

Choose a box plot when comparing the center and spread of several numeric groups, especially when distributions may be skewed or contain unusual values. It is more informative than a simple mean-only chart when variation matters. It can also summarize many observations compactly, although readers unfamiliar with quartiles may need a short explanation.

A histogram is a better choice when you need to show the shape of one distribution in detail, including clusters or gaps. A scatter plot is better for examining the relationship between two numeric variables. A column chart is appropriate for comparing totals or averages, but it does not show the same distribution detail unless you add additional summaries. These charts answer different questions, so choose based on what the reader needs to compare.

Do not use a box plot to imply that categories are directly comparable when their measurement units, collection methods, or time windows differ. Even well-made charts can mislead if groups are not comparable. Note the sample period and units, and state whether the plotted observations are individual measurements, a sample, or a complete population.

Frequently Asked Questions

Does Excel have a box and whisker chart?
Yes. In supported desktop editions, including Microsoft 365, select the data and choose Insert > Insert Statistic Chart > Box and Whisker. Excel 2016 and later include this chart type.

What does a dot beyond the whisker mean?
It is an observation beyond the chart’s usual 1.5-IQR whisker limit, shown as a potential outlier when outlier points are enabled. It is a prompt to investigate, not an automatic instruction to remove the value.

Do the whiskers show the minimum and maximum?
Not necessarily. They reach the most extreme observations still within the 1.5-IQR limits. Values beyond those limits can be shown individually as outlier points.

Can I make a box plot with groups of different sizes?
Yes. Put each group in a separate column and leave unused cells blank. Do not fill missing observations with zero unless zero is an actual measurement.

Why does my box plot differ from another program’s?
Programs can use different quartile conventions or handle small samples differently. Check the quartile method selected in Excel’s Format Data Series pane and compare the input values and treatment of blanks.

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.