Box and Whisker Plot in Excel: Set It Up Step by Step

To create a box and whisker plot in Excel for Microsoft 365 on Windows, arrange each group of numeric observations in its own column, select the data, then choose Insert > Insert Statistic Chart > Box and Whisker. The chart summarizes each group’s median, quartiles, spread, and potential outliers, making it easier to compare distributions than a list of values alone. Before you start, decide which groups you want to compare and check that your data is numeric and clearly labeled.

What You’ll Need

Excel for Microsoft 365 on Windows. The steps below use the desktop app; chart availability and editing options can differ in Excel for the web.
A set of numerical observations for each group. Examples include delivery times by location, test scores by class, or monthly measurements by product.
A worksheet with one group per column and a short heading at the top of each column. Groups can have different numbers of observations; leave unused cells blank rather than filling them with zero.
Enough observations to make a distribution summary meaningful. A box plot can be created from small samples, but a very small group may not reveal much about the shape or spread of its data.

Step-by-Step

Arrange the source data by group. Put each group in a separate column, with a descriptive heading in the first row and one observation per row beneath it. For example, column A might be headed North and contain its measurements in A2:A16, while column B is headed South and contains its measurements in B2:B18. Keep the rows within a column together; do not put group labels in a separate column beside a single list of measurements for this chart setup.
Check and clean the values. Confirm that observations are stored as numbers, not text that merely looks like numbers. Remove explanatory notes from the measurement area, and decide how to handle missing entries before charting. Leave genuinely missing observations blank. A zero is a real value, not a placeholder: including it can substantially change the group’s median and quartiles. If a value is an error or was entered incorrectly, correct the source rather than hiding it with chart formatting.
Select the entire data range, including headings. Drag across all group columns and their observations, including the header row. If the columns have different lengths, select the rectangular range covering the longest one; blank cells below shorter groups are fine. Including headings helps Excel identify the series names. If you leave out the headings, the chart may use generic names such as Series 1 and Series 2.
Insert the chart. On the Windows ribbon, choose Insert > Insert Statistic Chart > Box and Whisker. Excel adds a chart to the worksheet. If the command is unavailable, confirm that the active selection contains suitable data and that you are using a desktop Excel version with this chart type. You can also use the Search box at the top of Excel to look for Box and Whisker, though the ribbon path is the direct route.
Check that Excel has interpreted the groups correctly. You should normally see one box for each column heading. If you see a box for each row instead, or the category labels are not the group names, select the chart and choose Chart Design > Switch Row/Column. Review the result after switching; the correct orientation is the one that gives one distribution per intended group.
Give the chart a clear title. Select the title box and replace the default text with a description that identifies the measurement and, if useful, the period or population. For example, use Delivery time by distribution center rather than Chart Title. If the title box is missing, select the chart and use Chart Design > Add Chart Element > Chart Title.
Review the chart’s statistical display settings. Right-click a box and choose Format Data Series to open the formatting pane. Under Series Options, check the available settings, including quartile calculation, show outlier points, show inner points, show mean markers, and show mean line. The wording or layout can vary slightly with Excel updates. Decide which points and summaries support your purpose, and avoid turning on every marker if that makes a crowded chart difficult to read.
Choose a quartile method deliberately. The Quartile Calculation setting offers Inclusive median or Exclusive median. These methods can produce different box boundaries, particularly with small samples. Use the method required by your course, organization, or analysis standard; if none is specified, choose one, note it in a chart note or caption, and use the same method for every group being compared. Do not assume the two methods must produce identical quartiles.
Format for comparison and readability. Resize the chart so group names and points are legible. Use a restrained color scheme, and keep the same scale and quartile method when comparing groups. Add axis titles when the measurement units are not obvious: select the chart and choose Chart Design > Add Chart Element > Axis Titles. A vertical axis title such as Time (minutes) tells readers what the values mean. Avoid decorative effects that obscure the boxes or points.
Check the result against the data. Confirm that each group appears once, the axis covers the expected values, and obvious extremes are represented as expected. An Excel box plot typically shows the median as a line inside the box, the first and third quartiles as the box edges, and whiskers extending to the most extreme non-outlier values. Points beyond the whiskers may be shown as outliers. The precise display depends on the series settings and the data.
Save the workbook and preserve the source data. Keep the observations in the workbook so the chart can update when values change. If you need to share a static view, save a copy or export the worksheet after checking the labels and scale. On Mac, use the desktop Excel app and look for the Box and Whisker chart under the Insert tab’s statistical chart options; ribbon wording and pane placement may differ slightly from Windows. Excel for the web may display an existing chart, but if the chart type or formatting controls are unavailable, open the workbook in desktop Excel.

Common Mistakes

Using the wrong data orientation: A single column of values with a category column beside it is not the same arrangement as one group per column. For a quick box plot, put each group’s observations in its own column. If your source is in a different layout, reshape or copy it into that structure before inserting the chart.
Mixing labels and observations: Text notes, units typed into individual cells, and stray headings inside a group can prevent Excel from treating the range consistently. Put units in the axis title, not in every measurement cell.
Filling blanks with zero: This creates real zero observations and changes the distribution. Leave missing data blank unless zero is genuinely the measured value.
Comparing charts with different settings: Inclusive and exclusive quartile calculations can yield different boxes. Use one method consistently when comparing groups, and disclose it where readers need to interpret the chart.
Calling every distant point an error: A plotted outlier is a statistical signal under the chart’s rule, not proof that the observation is wrong. Check the original record and the context before excluding or correcting a point.
Reading the box as the full range: The box covers the middle half of the data, not all observations. Whiskers and separately plotted points carry information about the rest of the distribution.

Tips

Use a direct title and clear group names. If labels are long, shorten them sensibly in the chart while keeping the full names in the worksheet or a note.
Keep measurement units consistent across groups. Comparing minutes with hours, for example, can make the chart misleading unless values are converted to the same unit first.
When groups have unequal sample sizes, show the number of observations for each group in a caption or nearby table. The standard box plot does not necessarily make sample-size differences obvious.
Use inner points when you need to see individual observations, especially for smaller samples. For large samples, the points may overlap; consider whether the chart remains readable before enabling them.
Do not rely on color alone to identify groups. Keep the group names visible and use a legend only when it helps rather than repeats information.
For a presentation or report, add a brief note explaining the quartile method and what the points beyond the whiskers represent. This prevents readers from mistaking the whiskers for minimum and maximum in every case.

Troubleshooting

The Box and Whisker command is missing: Make sure you are in the desktop version of Excel and have selected a range with numerical observations. Excel for the web may not expose all chart insertion options. Open the workbook in Microsoft 365 for Windows or Mac and check Insert > Insert Statistic Chart.
The chart shows one box per row rather than one per column: Select the chart and choose Chart Design > Switch Row/Column. If the result is still wrong, check that the heading row and data range were selected together and that no extra row or column was included.
Group names appear as Series 1 or Series 2: Recreate or edit the chart using a selection that includes the heading cells. You can also check Chart Design > Select Data and confirm that the series names refer to the intended heading cells.
Some observations seem to disappear: Check whether the cells are blank, stored as text, or outside the selected range. Verify the source values and extend the selection if needed. Also inspect the series options: turning off inner points hides individual observations that are not displayed as outlier points, but it does not remove those values from the summary.
The boxes look different from a textbook example: Check the Quartile Calculation setting, the number of observations, and whether the example uses a different whisker convention. Excel’s chart settings affect how quartiles are calculated, and whiskers do not necessarily extend to the absolute minimum and maximum when outliers are plotted separately.
The axis scale makes groups hard to compare: Right-click the value axis and choose Format Axis. Review the bounds and units, and use a consistent scale across charts that readers will compare. Avoid narrowing the scale in a way that exaggerates small differences without making that choice clear.

Frequently Asked Questions

What does a box and whisker plot show?
The box shows the middle 50% of observations, with its central line marking the median. Whiskers show the range of non-outlier values under the chart’s rule, and separately plotted points can identify potential outliers. The plot helps compare centers and spread across groups.

Can I make a box plot with one column of data?
Yes. Select a single column of numeric observations, including its heading, and insert the chart. It will summarize one group. To compare multiple groups, place each group in a separate column and select them together.

Does Excel show the mean as well as the median?
It can. Right-click a box, choose Format Data Series, and look under Series Options for Show mean markers or Show mean line. The median is the line inside the box; the mean is a separate summary and can be affected more strongly by extreme values.

Why are the whiskers not at the minimum and maximum?
In Excel’s box plot, whiskers generally reach the most extreme values that are not classified as outliers under the chart’s rule. Values beyond them can appear as individual points. That is why the whiskers may stop short of the smallest or largest observation.

Can I change the quartile method after making the chart?
Yes. Right-click a box, choose Format Data Series, and change Quartile Calculation under Series Options. Because the box boundaries can move, make sure the selected method is appropriate and consistent with any charts you are comparing.

Can Excel for the web create this chart?
Chart creation and formatting support can vary in Excel for the web. If you cannot find the Box and Whisker option or its series settings, open the workbook in desktop Excel for Microsoft 365 on Windows or Mac, create or edit the chart there, then save the workbook.

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.