Box Plot in Excel: Compare Multiple Data Sets

Yes. In Microsoft Excel for Microsoft 365 on Windows, put each data set in its own column, select the columns with their headings, then choose Insert > Insert Statistic Chart > Box and Whisker. Excel draws a box for each column, making it possible to compare medians, spread, and potential outliers across several groups in one chart.

Arrange the Data for Multiple Boxes

Excel uses the selected data to identify the groups to plot. The clearest layout is one group per column, with a descriptive heading in the first row and numeric observations below it. For example, columns called Product A, Product B, and Product C could contain one measured value per row. Each column then supplies the observations for one box.

The columns do not have to contain the same number of observations. If one group has 20 measurements and another has 17, leave the remaining cells blank; do not fill them with zeros, because zero is a real numeric value and would change the distribution. Keep headings distinct and avoid putting notes, totals, or summary statistics among the observations. A total at the bottom of a column can accidentally be treated as another data point.

Use one column per group and put its name in the top cell.
Enter each individual observation on its own row.
Keep values numeric and use the same unit and measurement definition for every group.
Leave missing observations blank rather than entering zero or a guessed replacement.

This arrangement is also easier to update: adding a new group means adding a column, while adding a measurement means adding a row. If your source data instead has one column for group names and another for measurements, reorganize it into columns before charting, or create a separate summarized layout for the chart.

Create the Box and Whisker Chart

On Windows, select the heading row and the numeric data range, then use Insert > Insert Statistic Chart > Box and Whisker. Excel inserts a chart in the worksheet. Each data column should appear as a separate box, with its heading used as the category label. If the result groups the data in the wrong direction, select the chart and use Chart Design > Switch Row/Column. This is most likely to matter when the data has been arranged by rows rather than by columns.

Check that the selected range includes the group headings and all intended observations.
Choose Insert > Insert Statistic Chart > Box and Whisker.
Confirm that each box represents one group and that the labels are correct.
Add a chart title and axis titles where they will help readers understand the measure and units.

The chart type is available in desktop Excel 2016 and later, including Microsoft 365. In Excel for Mac, look under the Insert tab for the statistical chart options; the exact ribbon labels and placement can vary by release. Excel for the web may display or edit charts differently from the Windows desktop app, so use desktop Excel if the Box and Whisker command is not available in your web interface.

What the Box, Line, and Whiskers Show

A box plot condenses a distribution into a few landmarks. The line inside the box marks the median, or middle value. The lower and upper edges of the box mark the first and third quartiles. The box therefore covers the middle half of the observations, and its height represents the interquartile range (IQR), the difference between those quartiles.

The whiskers show the extent of the non-outlier values under the chart’s outlier rule; they do not necessarily reach the smallest and largest observations. Excel identifies values beyond 1.5 times the IQR below the first quartile or above the third quartile as potential outliers and can display them as individual points. Those points are worth checking, but a point outside the whiskers is not automatically an error. It may be a valid rare observation.

When comparing boxes, a higher median indicates a higher typical value, while a taller box indicates more variability in the middle half of the data. Unequal whisker lengths or a median positioned off-center within the box can suggest asymmetry. These are useful visual clues, not a complete statistical analysis: the plot does not show every value unless individual points are enabled.

Set Quartiles, Means, and Individual Points

To adjust what the chart displays, right-click a box and choose Format Data Series. In the Format Data Series pane, use the Series Options to find settings such as quartile calculation, show mean markers, show mean line, show outlier points, and show inner points. The available options depend on the Excel version. Mean markers and lines add information about the arithmetic average, while inner points show individual observations and can make a chart crowded when there are many measurements.

Excel offers inclusive and exclusive median calculations for quartiles. The choice can affect the box edges, especially in small samples, so use the same setting for every group and explain the convention if readers need to reproduce the result. Neither option makes the chart universally correct for every purpose; the appropriate convention depends on the analysis or reporting standard. For a quick comparison, consistent settings across groups matter more than changing options until the boxes look a certain way.

Showing the mean can be helpful when a few large or small values pull the average away from the median. For example, an unusually high measurement may leave the median nearly unchanged while raising the mean. Showing inner points is useful for small samples, where each observation matters, but can obscure the boxes in large data sets. Try the display options and keep those that make the data easier to interpret without hiding important details.

A Concrete Example of Comparing Groups

Suppose Product A has the measurements 2, 3, 4, 5, 6, 7, 8, 9, 10, and 11. Product B has 3, 4, 4, 5, 6, 7, 8, 9, 10, and 30. Both groups have a median of 6.5, so their typical middle values are the same. Most of the measurements are also similar, but Product B includes a much larger final value.

With Excel’s inclusive quartile setting, the two groups have the same first and third quartiles in this example: 4.25 and 8.75. Their IQR is 4.5, so the upper outlier fence is 15.5 (8.75 plus 1.5 times 4.5). Product B’s value of 30 is beyond that fence and appears as a potential outlier; Product A’s largest value, 11, is not. The box plots make this difference visible even though the medians match. The example also shows why a box plot should not be read as a chart of just the averages or maximums.

Make Several Data Sets Easy to Compare

A chart with many boxes can quickly become difficult to read. Give each group a short, recognizable heading, use a descriptive chart title, and label the vertical axis with the measured quantity and unit. Keep the measurement scale consistent; changing scales between charts can make differences look larger or smaller than they are. If categories have a natural order, arrange the columns in that order rather than relying on alphabetical sorting.

For a fair comparison, confirm that each group was measured in the same way and represents a comparable population or time period. A box plot cannot correct for inconsistent sampling, different units, or a change in how measurements were recorded. Also distinguish missing values from genuine zeros before charting. If groups have very different sample sizes, the plot can still compare their distributions, but the boxes alone do not communicate how many observations each group contains. Add sample sizes in labels or a nearby table when that context matters.

Common Problems and How to Fix Them

Only one box appears: Check that multiple group columns are included in the selected range. If the data is laid out by rows, try Chart Design > Switch Row/Column.
A zero or extreme value changes the plot: Inspect the source cells for placeholders, totals, or accidental entries. Replace a missing value with a blank only when it truly is missing; do not remove valid unusual measurements just to improve the appearance.
Group names are missing or incorrect: Include the heading cells in the selection and check whether Excel has interpreted the first row as labels.
The boxes differ from another statistical tool: Check the quartile convention, outlier display, and source observations. Different quartile definitions can produce different box edges, particularly with small samples.
The chart is crowded: Shorten category labels, widen the chart, or split a large set of groups into related charts. Showing every inner point is not always helpful.

When a chart looks surprising, verify the underlying values before changing formatting. Sorting the source observations is not required for a box plot, but reviewing them can reveal text stored as text, accidental duplicates, or an unexpected value. A clear chart is useful only if its data range and labels are correct.

Box Plots Compared with Other Excel Charts

A box plot is designed to compare distributions, not to show how a measure changes continuously over time. Use a line chart when the sequence of dates or time periods is central. Use a column chart when the main point is to compare totals or averages, while remembering that a single average can conceal variation. A histogram is often better when readers need to see the shape and frequency of values within one distribution.

Box plots are especially useful when several groups have many observations and the reader needs a compact comparison of medians and spread. They are less informative about clusters or gaps within a distribution because those details are compressed into quartiles and whiskers. If the sample is small, showing individual points alongside the box can help; if the sample is large, a histogram or another distribution display may reveal more detail. Choose the chart based on the question, not just on the fact that the data is numeric.

Frequently Asked Questions

Can Excel make one box plot with multiple data sets?
Yes. Put each data set in a separate column, select the columns and headings, then insert a Box and Whisker chart. Excel creates one box for each group.

Do all data sets need the same number of values?
No. Columns can have different numbers of observations. Leave unused cells blank; do not enter zeros to make the columns the same length.

Why does Excel show a value as an outlier?
Excel marks observations beyond the whisker rule, which is based on 1.5 times the interquartile range from a quartile. Check that the value is valid, but remember that an outlier can be a real observation rather than a mistake.

Can I show the mean on an Excel box plot?
Yes. Right-click a box, choose Format Data Series, and turn on the mean marker or mean line if that option is available in your version. The mean and median measure different aspects of a distribution.

Why are my box plot quartiles different from another program?
Programs can use different methods to calculate quartiles, and Excel lets you choose inclusive or exclusive median quartiles. Compare the calculation settings as well as the source data before concluding that a chart is wrong.

Can I make a box plot in Excel for the web?
Availability and editing options can depend on the web version and account. If you cannot find the chart type or its formatting settings, open the workbook in the desktop Excel app and use Insert > Insert Statistic Chart > Box and Whisker.

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.