Box Plot in Excel: Create, Format, and Read Box Plots

To create a box plot in Excel for Microsoft 365 on Windows, arrange each group’s numeric observations in its own column, select the data with its headers, then choose Insert > Insert Statistic Chart > Box and Whisker. The chart summarizes each group’s median, quartiles, spread, and potential outliers, making it useful for comparing distributions rather than just totals or averages.

What a box plot shows

A box plot, also called a box-and-whisker plot, compresses a set of numeric observations into a visual summary. The box extends from the first quartile (Q1) to the third quartile (Q3), so it contains the middle half of the data. A line inside the box marks the median, or 50th percentile. The box’s height or length is the interquartile range (IQR), calculated as Q3 minus Q1.

Whiskers extend from the box toward the lower and higher values that are not classified as outliers. In Excel’s standard box-and-whisker chart, potential outliers are shown as individual points beyond the whiskers, using the 1.5 × IQR rule. A whisker does not necessarily reach the smallest or largest observation: it reaches the most extreme observation still within that rule. Excel can also display individual inner points and a mean marker, depending on the chart settings.

Because the plot emphasizes the median and spread, it can reveal differences that an average alone hides. Two groups might have the same median but very different variability, or similar boxes but different outliers. A box plot is a summary, however; it does not show every detail of the distribution, such as gaps or clusters, unless you display the individual observations too.

Arrange the data before inserting the chart

Excel expects the values for each group to be in a separate data series. The easiest layout is one column per group, with a descriptive header at the top and observations below it. For example, columns named Morning, Afternoon, and Evening could contain measurements collected during those periods. Groups can have different numbers of observations; leave unused cells blank rather than filling them with zero, since zero is a real value and would affect the summary.

Put one group’s numeric observations in each column.
Use the first row for clear group names, and include that row when selecting the chart data.
Keep the measurements in a consistent unit and make sure values intended as numbers are stored as numbers.
Keep notes, totals, and other non-observation rows outside the selected range.

If your source data is arranged with one group per row instead, Excel may interpret the series in the wrong direction. After inserting the chart, choose Chart Design > Switch Row/Column to change how Excel groups the observations. Check the horizontal category labels and the number of plotted boxes before relying on the result. If categories are not obvious, a small, clean table is usually easier to diagnose than a large selection containing extra columns.

Create a box plot in Excel

Select the complete data range, including the group headers and numeric observations. Avoid selecting unrelated cells.
On the ribbon, choose Insert > Insert Statistic Chart > Box and Whisker. Excel inserts a chart based on the selected series.
Review the labels and boxes. If the groups appear as separate observations or the category names look wrong, use Chart Design > Switch Row/Column.
Give the chart a meaningful title and add axis titles if the measurement or units might be unclear. Select the chart to reveal the chart design options and use the available chart elements controls to add or edit titles.
Right-click a box and choose Format Data Series to adjust the statistical display and visual options.

On Excel for Mac, the chart type is also available as a statistical chart in supported Microsoft 365 builds, although ribbon labels and the formatting pane can differ by release. Excel for the web may offer a more limited set of chart creation or formatting options. If Box and Whisker is missing, open the workbook in the desktop version of Excel and check that it is updated; do not substitute an ordinary column chart and assume it calculates quartiles.

Read the boxes, whiskers, and outliers

Read a box plot by comparing the median lines first, then the box sizes and whiskers. A higher median means the middle observation is higher, but it does not mean every value in that group is higher. A taller box on a vertical chart—or a longer box on a horizontal chart—means the middle 50% of observations covers a wider range. Unequal whisker lengths can suggest that one tail extends farther than the other.

An isolated point beyond a whisker is a potential outlier under the chart’s rule, not proof that the measurement is wrong. It could reflect a data-entry error, an unusual but valid event, or a population with a long tail. Check the original record and the context before excluding it. A point can be statistically unusual and still be important to report.

When distributions overlap, avoid treating the boxes as a formal significance test. Box plots are descriptive: they help show center, spread, and unusual values, but they do not by themselves establish that groups differ statistically or explain why. Sample size also matters. A box plot based on a few measurements can look tidy while providing limited evidence about the broader population.

Choose quartile and point options

Excel’s Format Data Series pane includes choices that affect how the plot is displayed. The Quartile Calculation setting offers Inclusive median and Exclusive median. These methods can produce different quartiles, particularly with small datasets, so charts built with different settings may not match exactly even when they use the same observations. For a comparison, use the same setting across the groups and document it if the method matters to the audience.

Show outlier points: displays observations beyond the whiskers as separate marks.
Show inner points: displays individual observations that fall within the whisker range. This can make a chart crowded when there are many values.
Show mean markers: adds a marker for the arithmetic mean, which can differ from the median when a distribution is skewed.
Show mean line: adds a line representing the mean, where available in the series options.

These display choices do not make the data more or less valid, but they change what readers can see. Showing inner points helps reveal clustering in small datasets; hiding them can make a chart with many observations easier to read. A mean marker is useful when readers expect an average, but label it clearly so it is not mistaken for the median.

Worked example: compare three groups

Suppose a researcher records a measurement for nine items in each of three conditions. Put the condition names in B1, C1, and D1, then enter each condition’s nine readings in rows 2 through 10. Select B1:D10 and insert a Box and Whisker chart. Each column should produce one box, letting a reader compare the three medians and the spread of the middle half of the readings.

Imagine the first group’s sorted values are 1 through 9. Its median is 5. With the inclusive-median quartile method, Q1 is 3 and Q3 is 7, giving an IQR of 4. The usual outlier limits are three units below Q1 and three units above Q3? No: the limits are Q1 − 1.5 × IQR and Q3 + 1.5 × IQR, which here are −3 and 13. All nine observations fall inside those limits, so the whiskers can reach 1 and 9 and there are no outlier points.

Now imagine a second group with values 2, 3, 3, 4, 5, 6, 7, 8, and 30. The value 30 lies beyond the upper outlier limit for the inclusive quartiles, so it appears as a point beyond the upper whisker; the whisker ends at the largest non-outlier value. The median remains 5. This illustrates why an outlier need not move the median as much as it moves the mean, and why reporting only an average can give an incomplete picture. The exact quartile boundaries can depend on the selected calculation method.

Common problems and practical fixes

The chart has one box instead of one per group: check whether the values are in columns or rows and try Chart Design > Switch Row/Column. Confirm that headers are included.
Zeros appear unexpectedly: look for formulas returning zero, placeholders, or cells that should have been blank. A zero is plotted as data; it is not the same as a missing observation.
Some values seem absent: inspect the source range for numbers stored as text, blanks, or values outside the selected range. Correct the source data and refresh or recreate the chart if necessary.
There are many separate dots: the chart may be showing inner points as well as outliers. Turn off Show inner points in Format Data Series if the detail obscures the summary.
Two charts disagree on quartiles: compare their Inclusive median or Exclusive median settings, and check whether both charts use the same observations.
The chart is hard to interpret: add units to the axis title, use a descriptive chart title, and avoid colors that imply a ranking unless the colors have a defined meaning.

Before removing an apparent outlier, verify the raw entry, units, and measurement process. If you need to exclude a value for a documented reason, preserve the original data and state the rule used. Silently deleting observations can make a comparison misleading.

Box plot versus other Excel charts

A box plot is a good choice when the question is how distributions differ across groups: where the middle lies, how wide the central half is, and whether unusually distant values occur. A bar or column chart is usually better for comparing totals or a single summary value per category. If you use a bar chart to show averages, it will not automatically communicate the median, quartiles, or outliers.

A histogram shows the shape of one numeric distribution by dividing values into bins. It can reveal clusters, gaps, and skew that a box plot conceals, but comparing many histograms can be cumbersome. A box plot makes side-by-side group comparisons compactly, while a histogram gives more detail about frequency patterns. For a detailed analysis, these chart types can complement one another rather than compete.

Frequently Asked Questions

Does Excel have a built-in box plot?
Yes. In supported desktop versions, including Microsoft 365, select the data and choose Insert > Insert Statistic Chart > Box and Whisker. The chart calculates and displays the summary from the selected observations.

How do I add outliers to a box plot in Excel?
Right-click a box, choose Format Data Series, and enable Show outlier points. Excel identifies and plots points beyond the whiskers using its box-and-whisker outlier rule; inspect the source values before deciding what they mean.

Why does my box plot show a different median or quartile?
Check the selected data range, whether the chart is grouping rows or columns, and the Quartile Calculation setting. Inclusive median and Exclusive median methods can produce different quartiles, especially with small samples.

Can a box plot show the average?
Yes. In Format Data Series, enable Show mean markers or Show mean line if you want the mean displayed. Keep the median line distinct in your explanation: the mean and median are different statistics and can diverge when data is skewed.

Can I make a box plot in Excel for the web?
Web capabilities can vary by release, and some chart options may be unavailable there. If you cannot create or format the statistical chart in the browser, open the workbook in desktop Excel for Microsoft 365 and use the Insert Statistic Chart command.

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.