Box and Whisker Plot in Excel: Create and Read One

To create a box and whisker plot in Excel for Microsoft 365 on Windows, put each group of numeric observations in its own column, select the data including the group headings, then choose Insert > Insert Statistic Chart > Box and Whisker. The chart summarizes each group’s distribution, showing its median, quartiles, spread, and potential outliers. Before you begin, arrange the data so Excel can distinguish the groups from their measurements.

What You’ll Need

Excel for Microsoft 365 on Windows, with the data-analysis chart feature available. Box and Whisker charts are also available in supported desktop releases beginning with Excel 2016.
A table of numeric observations, with one column per group you want to compare. Use a descriptive heading at the top of each column.
Enough observations to make a distribution useful. A box plot can be drawn from a small sample, but its quartiles and apparent pattern may be unstable when there are very few values.

You do not need to calculate the quartiles or outliers yourself. Excel calculates and displays them from the selected observations. You can optionally change the quartile convention or show additional points after creating the chart.

Step-by-Step

Arrange the observations in columns. Put each category or group in a separate column and place its name in the first row. For example, enter “Morning,” “Afternoon,” and “Evening” in cells A1, B1, and C1, then put the corresponding measurements below each heading. If Morning has eight measurements and Afternoon has ten, the columns can have different numbers of entries. Keep the values as numbers; do not include units such as “cm” in the individual cells. Put units in a heading or chart axis title instead.
Check the data before selecting it. Make sure the headings are clear, each measurement is in the correct group, and cells do not contain labels mixed into the numeric values. Remove accidental spaces or notes from the data area. A zero is a real value, not a blank: keep it if it is a valid observation, and do not replace missing measurements with zero. If you have multiple measurements for each group, include all of them rather than only the averages; a box plot describes the distribution of the observations.
Select the table, including headings. Drag across the complete range, for example A1:C11 if the headings are in row 1 and the values occupy rows 2 through 11. Include the headings so Excel can use them as category labels. If columns have unequal lengths, select the full rectangular range down to the last used row in any group; cells without observations should remain empty. Avoid selecting nearby totals or explanatory text.
Insert the chart. On the ribbon, choose Insert > Insert Statistic Chart > Box and Whisker. Excel adds a chart to the worksheet. If the chart menu is collapsed or your window is narrow, look under the Charts group on the Insert tab for the statistical chart options. A chart type called Histogram is not the same thing; choose Box and Whisker.
Confirm Excel has treated the groups correctly. Each group should have its own box, labelled with the column heading. If you see a box for each row, or the labels do not match your headings, select the chart and use Chart Design > Switch Row/Column. This switches whether Excel interprets rows or columns as the data series. For the usual layout with groups in columns, the headings should appear along the category axis and each heading should have one box.
Add a useful chart title and labels. Click the chart title and replace it with a concise description, such as “Delivery time by shipping method.” If the title is not visible, select the chart and use Chart Design > Add Chart Element > Chart Title. To explain measurement units, choose Chart Design > Add Chart Element > Axis Titles and label the value axis, for example “Delivery time (days).” Avoid putting the unit only in the title if readers could mistake the scale.
Choose what to display in the boxes. Right-click a box and choose Format Data Series to open the formatting pane. Under Series Options, review the options for inner points, outlier points, mean markers, and a mean line. Turn on inner points when you want to show observations that fall within the whiskers; use outlier points to make values beyond the whiskers visible. Mean markers and the mean line add the average, which can help comparison but may distract if the main purpose is to compare medians and spread. You can also set Quartile Calculation to Inclusive median or Exclusive median.
Interpret the parts before drawing a conclusion. The line inside the box is the median, or middle observation by rank. The lower and upper edges of the box are the first and third quartiles; the box therefore covers the middle half of the data. The distance between those edges is the interquartile range (IQR). Whiskers extend to the most extreme values Excel treats as non-outliers. Individual points beyond the whiskers are displayed as outliers. A taller box means the middle half of that group’s measurements is more spread out; it does not mean the group has a higher average.
Compare groups in context. Look at the median lines to compare typical values, then compare box heights and whisker lengths to assess variation. Check whether the boxes overlap, whether one group has unusually distant points, and whether the value axis is appropriate. A group with a higher median may still have considerable overlap with another group. Treat an outlier as a signal to check the original observation, not proof that it is an error or should be removed.
Review the final chart and source data. Make sure every group is named, the axis scale is readable, and the chart communicates the intended comparison. If the chart will be shared, keep the source table in the workbook and add a note describing the measurement period or units. This makes the result easier to verify and update when new observations arrive.

The box plot is a compact summary, not a replacement for checking the data. Two groups can have similar medians and boxes but different sample sizes, or have the same median while their overall distributions differ. If those details matter, display individual points or provide a companion table with the number of observations and relevant summary statistics.

Common Mistakes

Including averages instead of raw observations. A box plot needs the measurements whose distribution you want to describe. If you enter one average per group, Excel can only summarize those averages; it cannot show the variation among the original observations.
Mixing text and numbers in a data column. Values such as “12 days” or “about 12” may be stored as text and not plotted as intended. Keep measurement cells numeric and put units in the heading or axis title.
Putting every group in a row without checking orientation. Excel may interpret the selection differently than you expect. Use Chart Design > Switch Row/Column and confirm that one box corresponds to each intended group.
Assuming whiskers always show the minimum and maximum. Points that Excel identifies as outliers can appear beyond the whiskers. Read the individual outlier markers as well as the whisker endpoints.
Comparing charts with different scales carelessly. A separately scaled vertical axis can make small differences look large or large differences look modest. For side-by-side comparisons, use a consistent value scale where practical.
Changing quartile settings without noting it. Inclusive and Exclusive median calculations can produce different quartiles, especially in small datasets. Use one convention consistently when comparing groups or charts.

Tips

Use short, distinct group headings. If labels are long, edit the source headings or widen the chart so they remain readable.
Keep one observation per cell and one group per column. This layout makes it easier to add data and rebuild or update the chart.
Show inner points when the sample is small or when readers need to see how individual values are distributed. For very large datasets, point markers may overlap and make the chart harder to read.
Use mean markers only when the average is relevant to the question. The median is the line in the box; the mean is a separate statistic and can be pulled by unusually high or low values.
Use consistent units, measurement procedures, and time periods across groups. Otherwise, the visual comparison may reflect differences in how data was collected rather than a genuine difference between groups.
For clearer reporting, state what one observation represents and how many observations are in each group. Excel’s box plot does not necessarily make unequal group sizes obvious at a glance.
If you change the source values, check that the chart reflects the updated range. When adding observations below the original selection, expand the chart’s source data if Excel has not included the new cells automatically.

Troubleshooting

Box and Whisker is missing from the Insert menu: Confirm that you are using a desktop Excel version that supports this chart type, such as Excel 2016 or a later release. In Excel for Microsoft 365 on Windows, look under Insert > Insert Statistic Chart. Excel for the web may not offer the same chart-creation controls as the desktop app; open the workbook in the desktop app if the option is unavailable. On Mac, use the Insert tab and look for the statistical chart menu; the labels or ribbon layout can differ slightly by release.

Excel creates the wrong number of boxes: Check that the selected range includes the correct headings and only the intended data. Then select the chart and choose Chart Design > Switch Row/Column. If this does not fix it, review the source layout for an extra heading row, totals, or an accidentally included column.

A group appears to have no box, or values seem missing: Inspect the source cells for numbers stored as text, blank cells, or values outside the selected range. Re-enter text-formatted numbers as numeric values, and make sure the selected range extends to the last observation. Do not convert genuine missing values to zero just to fill a rectangular selection.

The chart shows many outliers: Check the original measurements and units first. Confirm that one group is not recorded in a different unit or with a misplaced decimal point. If the observations are valid, keep them and explain what they represent; hiding outlier markers changes what readers can see, not the underlying values.

Quartiles differ from another tool or textbook example: Open Format Data Series and check whether Excel is using Inclusive median or Exclusive median. Different quartile conventions can yield different box edges, particularly with small samples. For a fair comparison, document the convention and apply it consistently.

The chart looks cluttered or hard to interpret: Reduce unnecessary markers, enlarge the chart, simplify long labels, and add units to the axis title. If categories have very different magnitudes, review the axis range and whether a single chart is still an appropriate comparison. Do not remove valid observations simply to make the graphic look neater.

Frequently Asked Questions

What does a box and whisker plot show?
A box plot summarizes the distribution of numeric data. The box shows the middle 50 percent, the line inside it marks the median, and whiskers show the range of non-outlier values. Excel can also show individual points identified as outliers, inner points, and the mean.

How many data points do I need?
Excel can draw a box plot from a small number of observations, but a very small sample provides a limited and potentially unstable summary. Use enough observations to represent the question you are studying, and report the sample size when readers need to assess the comparison.

Can groups have different numbers of observations?
Yes. Put each group in its own column and leave unused cells below shorter columns blank. Do not fill those cells with zero, because zero would be treated as an actual measurement and could change the distribution.

What is the difference between inclusive and exclusive quartiles?
They are two conventions for calculating quartile positions. They can produce different box edges, especially for small datasets. Choose the convention that fits your reporting requirements and use the same one for every group being compared.

Can I make a box and whisker plot in Excel for the web?
Availability and editing controls can differ from the Windows desktop app. If you cannot find the statistical chart option or format settings in the browser, open the workbook in desktop Excel, create or format the chart there, and then save it for sharing.

Should I remove outliers before making the chart?
Not automatically. First check whether an unusual value is a recording or measurement error. If it is valid, keep it unless your analysis has a clearly stated reason for excluding it, and explain any exclusion so the chart is not misleading.

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.