Box Plot Maker in Excel: Create a Box-and-Whisker Chart

Excel has a built-in box plot maker: select your grouped numeric data, then choose Insert > Insert Statistic Chart > Box and Whisker in Excel for Microsoft 365 on Windows. The chart summarizes each group’s distribution, including its median, quartiles, spread, and any points marked as outliers.

What an Excel box plot shows

A box plot, also called a box-and-whisker plot, compresses a set of numeric observations into a visual summary. The line inside the box marks the median. The bottom and top edges of the box mark the first and third quartiles, often called Q1 and Q3. The box therefore spans the middle half of the values, and its height is the interquartile range, or IQR.

Whiskers extend from the box toward the low and high ends of the data. In Excel’s standard box plot, they reach the most extreme observations that are not classified as outliers by the chart’s rule. Points beyond the whiskers can appear individually. A displayed outlier is a statistical flag, not proof that a measurement is wrong: it may be a valid, unusual observation that deserves investigation.

Box plots are useful when comparing distributions, not just averages. Two groups can have the same median but different variability, or similar ranges but very different medians. A box plot makes those patterns easier to notice than a table of dozens of values.

Prepare data in a chart-friendly layout

Put each group in its own column, with a descriptive heading in the first row and one numeric observation per row beneath it. For example, columns named Morning, Afternoon, and Evening could contain response times for those three periods. Select the headings and data together when inserting the chart. Excel will normally treat each column as one distribution.

Groups do not have to contain the same number of observations. Keep the data in separate columns and leave unused cells blank; do not enter zero as a substitute for a missing observation, because zero is a real value and can change the distribution. Check that the measurement columns contain numbers rather than numbers stored as text. Remove explanatory notes from the middle of the values, and keep units consistent across groups.

Use one column per group when comparing categories such as departments, treatments, or months.
Include a clear header for every group so the legend and axis labels can identify the boxes.
Use raw observations rather than only a pre-calculated minimum, quartile, median, and maximum. The built-in chart calculates its summary from the observations.

If your data are arranged with groups in rows instead, insert the chart and use Chart Design > Switch Row/Column if Excel interprets the layout the wrong way. Confirm that each box represents a group you intend to compare before presenting the chart.

Create a box-and-whisker chart in Excel

Arrange the raw measurements in columns with a heading at the top of each column.
Select the complete data range, including the headings.
On the Windows ribbon, choose Insert > Insert Statistic Chart > Box and Whisker. Depending on the ribbon width, Insert Statistic Chart may appear as a chart button or within the Charts group.
Review the resulting chart. If the groups or category labels appear in the wrong direction, choose Chart Design > Switch Row/Column.
Add a useful chart title and axis title through Chart Design > Add Chart Element, where appropriate. Label the measurement units so the scale is understandable.

The chart is linked to its source cells, so changing observations can update the plot. If you add new rows after creating it, check whether the chart’s source range expanded to include them; if it did not, use Chart Design > Select Data to adjust the range. Excel for Mac also offers box-and-whisker charts in supported desktop releases, though the Insert tab may group the option under Statistical Charts and labels can vary by release. Excel for the web may not show every creation or formatting control; if Box and Whisker is unavailable, use the desktop app to create or edit the chart.

Format the boxes, whiskers, and points

To control the statistical display, select a box in the chart, right-click, and choose Format Data Series. The Format Data Series pane contains options for how Excel presents the series. Depending on the chart and Excel release, you can show or hide inner points, outlier points, mean markers, and a mean line. Inner points show individual observations within the whisker range; displaying them can reveal clusters, but a chart with many observations may become visually crowded.

The same pane provides a quartile calculation choice, commonly labeled Inclusive median or Exclusive median. This affects how Excel calculates the quartiles, particularly for small samples and odd-sized samples. It can change the box boundaries and sometimes the outlier classification, so use one method consistently when comparing charts. If the chart has several groups, check whether the change applies to the selected series or all the series in your version, and verify the result rather than assuming every box uses the same setting.

Formatting should help readers interpret the data, not disguise its spread. Use a readable vertical scale, avoid unnecessary 3-D effects, and use distinct but restrained colors if color helps separate groups. A mean marker can add context, but it does not replace the median: the mean is sensitive to unusually high or low values, while the median is the central value in the ordered data.

Understand quartiles and outlier markers

Quartiles divide ordered data into parts. Q1 is a lower-quarter measure, the median is the midpoint, and Q3 is an upper-quarter measure. There is more than one accepted calculation convention, especially for finite samples. That is why two programs, or two settings in a program, can produce slightly different box edges from the same observations without either calculation being a data-entry error.

For the usual 1.5-IQR outlier rule, calculate IQR as Q3 minus Q1. The lower fence is Q1 minus 1.5 times the IQR; the upper fence is Q3 plus 1.5 times the IQR. Observations outside those fences are commonly plotted as outliers, while whiskers reach the most extreme observations inside the fences. The fences themselves are thresholds, not whisker endpoints. Excel’s quartile setting influences the quartiles used in this process.

You can check quartiles separately with worksheet functions. For example, suppose A2:A11 contains the sorted values 4, 5, 6, 7, 8, 9, 10, 12, 15, and 30. With those values in A2:A11, the inclusive first quartile formula is =QUARTILE.INC(A2:A11,1), which returns 6.25. With the same layout, the inclusive third quartile formula is =QUARTILE.INC(A2:A11,3), which returns 11.5. With the values still in A2:A11, =MEDIAN(A2:A11) returns 8.5.

For this example, the IQR is 11.5 minus 6.25, or 5.25. The upper fence is 11.5 plus 1.5 times 5.25, which equals 19.375; the value 30 is above that fence. The lower fence is negative 1.625, so none of the values at the low end falls below it. The whiskers therefore reach 4 and 15, and 30 is a potential outlier. These calculations use the inclusive quartiles; choose the matching chart setting before expecting its box to line up with the worksheet calculation.

Common problems and how to fix them

The chart shows one box instead of several. Check the orientation of your data and use Chart Design > Switch Row/Column. Make sure each group has its own column or row of raw observations.
A group has a surprising median or scale. Look for blanks that were entered as zero, values stored as text, mixed units, or an unintended cell included in the chart range. Sort or inspect the source data to find unusual entries.
There are no outlier dots. A group may simply have no observations outside the chart’s fences. You can also select the series and check that Show outlier points is enabled in Format Data Series.
The boxes differ from another report. Compare the quartile convention, outlier rule, and source range. Different conventions can give different quartiles, particularly in small samples.
New observations do not appear. Check the chart’s source range with Chart Design > Select Data and expand it if required. A chart does not necessarily include every row added later to a fixed range.

When to use a box plot instead of another chart

A box plot is a strong choice for comparing the center and spread of multiple groups, especially when each group contains enough observations to make a distribution summary meaningful. It is less effective when readers need to see every individual value, when a group has very few observations, or when the shape of the distribution is the main question. With tiny samples, a box can hide the actual data points and imply more stability than the sample supports.

A histogram shows the frequency pattern within one distribution more directly, while a scatter plot can reveal relationships between two numeric variables. A dot plot or strip plot can make individual observations visible for small groups. Excel’s box-and-whisker chart is convenient because it computes the summary from raw data, but you may choose another chart when the audience needs detail that the quartiles and whiskers do not show.

Frequently Asked Questions

Does Excel have a built-in box plot maker?
Yes. In supported desktop versions, including Excel for Microsoft 365, select the data and choose Insert > Insert Statistic Chart > Box and Whisker. It creates the chart from raw numeric observations rather than requiring you to draw the boxes manually.

Can I make a box plot in Excel with different sample sizes?
Yes. Put each group in its own column and leave the unused cells blank for shorter groups. Do not fill those cells with zero, because zero would be included as an observation and could distort that group’s summary.

Why does my Excel box plot mark a value as an outlier?
The point may fall beyond the chart’s outlier fences, commonly set at 1.5 times the IQR below Q1 or above Q3. Check the raw value and the quartile calculation before deciding whether it is a mistake; an outlier marker is a prompt to review, not an automatic reason to delete the data.

Can I make a box plot from summary statistics alone?
The built-in Box and Whisker chart is designed to calculate its summary from individual observations. If you have only a minimum, quartiles, median, and maximum, you will need to build a custom chart or obtain the underlying observations; the built-in chart cannot reconstruct data it was not given.

Why do two box plots of the same data have different quartiles?
They may use inclusive versus exclusive quartile calculations, or may use different outlier conventions or data ranges. Compare those settings first, and make sure both charts use the same observations before interpreting the difference.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

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

Spotted an error in this guide? Let us know.