How to Create a Box and Whisker Plot in Excel

Box and whisker plots — also called box plots — are one of the most underused chart types in Excel, yet they pack a serious amount of statistical information into a compact visual. If you're working with data that has natural variation — test scores, sales figures, response times, survey results — a box plot tells a story that a simple bar chart completely misses.

Here's how to build one, what the parts actually mean, and what to think about based on your version of Excel and your data.

What a Box and Whisker Plot Actually Shows

Before building one, it helps to understand what you're looking at. A box plot summarizes the distribution of a dataset using five key values:

The box itself represents the interquartile range (IQR) — the middle 50% of your data. The whiskers extend outward from there. This makes it immediately obvious whether your data is skewed, tightly clustered, or spread wide.

Method 1: Using Excel's Built-In Box and Whisker Chart (Excel 2016 and Later) 📊

If you're on Excel 2016, 2019, Microsoft 365, or Excel for Mac (2016+), the chart type is built in and takes about 30 seconds to insert.

Steps:

1. Select your data. Highlight the range of values you want to plot. This can be a single column or multiple columns for side-by-side comparison.

2. Open the Insert tab. In the ribbon, go to Insert → Charts.

3. Click the statistical chart icon. It looks like a histogram. From the dropdown, select Box and Whisker.

4. Your chart appears. Excel automatically calculates the quartiles, median, and whiskers.

5. Format as needed. Right-click the chart area to access formatting options. You can adjust fill colors, show or hide individual points, and toggle the mean marker (the X inside the box).

Important Default to Know

Excel's box plot includes a mean marker (shown as an X) by default, in addition to the median line. These are different values and can be in different positions depending on your data's skew. You can right-click the data series and go to Format Data Series to control whether the mean marker is displayed.

Method 2: Building a Box Plot Manually (Excel 2013 and Earlier)

Older versions of Excel don't include a native box and whisker chart type. The workaround involves building a stacked bar chart and then manipulating it to look like a box plot — a process that's functional but more tedious.

The Core Approach:

1. Calculate your five statistics using Excel formulas:

   • =MIN(range) — minimum
   • =QUARTILE(range, 1) — Q1
   • =MEDIAN(range) — median
   • =QUARTILE(range, 3) — Q3
   • =MAX(range) — maximum

2. Calculate interval values (not the raw values themselves — you need the differences between them for stacked segments):

   • Segment 1: Q1 − Min
   • Segment 2: Median − Q1
   • Segment 3: Q3 − Median
   • Segment 4: Max − Q3

3. Create a stacked bar chart using these interval values.

4. Make the bottom segment invisible by setting its fill to "No Fill" — this creates the illusion that the box starts at Q1.

5. Add error bars to simulate the whiskers.

This method works, but it requires more manual maintenance if your underlying data changes.

Comparing Multiple Groups Side by Side

One of the most powerful uses of a box plot is comparing distributions across categories — for example, comparing delivery times across three warehouses, or test scores across four classrooms. 🎯

To do this in Excel 2016+:

• Arrange each group in its own column, with a header row.
• Select all columns, then insert the Box and Whisker chart.
• Excel will create a separate box for each column, plotted on the same axis.

Each box can have a different number of data points — Excel handles uneven datasets fine.

Variables That Affect Your Results

A few factors meaningfully change how this process works:

Excel version is the biggest one. Native box plots only exist in Excel 2016 and later. If you're on an older version, you're building a workaround, not using a dedicated chart type.

Data structure matters. Excel's built-in box plot expects data in columns, one group per column. If your data is in rows, or mixed with categorical labels in non-standard positions, you may need to reformat before the chart populates correctly.

Dataset size affects interpretability. Box plots are most meaningful with at least 10–15 data points per group. With very small samples, the quartile values can be misleading, and the visual loses statistical credibility.

Outlier handling varies. Excel uses a standard method (1.5× IQR beyond Q1 and Q3) to determine what qualifies as an outlier. If your analysis requires a different threshold, you'd need to calculate and plot manually.

Mac vs. Windows Excel — Box and Whisker is available on both platforms for supported versions, but the formatting options in the sidebar may differ slightly from the Windows ribbon layout.

What the Chart Won't Tell You Automatically

A box plot shows shape and spread, but it doesn't show sample size or raw distribution within the box. Two datasets can have identical box plots but very different underlying patterns. For deeper analysis, some users layer a box plot with a swarm plot or dot plot — something Excel doesn't support natively, though it can be approximated.

The decision of which statistical view actually serves your audience — and whether a box plot is even the right chart type for what you're presenting — depends on what your data represents, who's reading it, and what decision that visual is meant to support.