How to Create a Box Plot in Excel: Step-by-Step

Key Insights

• Excel 2016 and later versions include a native Box and Whisker chart type that automates quartile calculations and outlier detection, making box plot creation a straightforward three-click process.
• For older Excel versions, you can build box plots manually using stacked bar charts combined with QUARTILE.INC and IQR formulas—more work, but equally effective.
• Box plots excel at comparing distributions across multiple groups simultaneously, revealing median differences, spread variations, and outliers that summary statistics alone would hide.

Introduction to Box Plots

Box plots (also called box-and-whisker plots) are one of the most efficient ways to visualize data distribution. Invented by statistician John Tukey in 1970, they pack five key statistics into a single compact graphic: minimum, first quartile (Q1), median, third quartile (Q3), and maximum.

The anatomy is straightforward. The box spans from Q1 to Q3, representing the interquartile range (IQR) where 50% of your data lives. A line inside the box marks the median. Whiskers extend from the box to show the range of typical values, and individual points beyond the whiskers indicate outliers.

Why use box plots instead of bar charts or histograms? They’re unmatched for comparing multiple groups side-by-side. You can instantly see which group has higher values, more variability, or more outliers. They’re also resistant to sample size differences—a box plot of 50 observations looks the same as one of 5,000, making fair comparisons possible.

Preparing Your Data

Before creating any visualization, your data needs proper structure. Box plots require numeric data organized in a specific way.

For Excel’s built-in chart, arrange your data with category labels in the first column and values in subsequent columns. Each column becomes a separate box in your plot.

Here’s a sample dataset structure for comparing test scores across three classes:

| Class    | Score |
|----------|-------|
| Class A  | 78    |
| Class A  | 82    |
| Class A  | 91    |
| Class A  | 67    |
| Class A  | 85    |
| Class B  | 72    |
| Class B  | 88    |
| Class B  | 79    |
| Class B  | 94    |
| Class B  | 81    |
| Class C  | 65    |
| Class C  | 71    |
| Class C  | 68    |
| Class C  | 73    |
| Class C  | 69    |

Alternatively, you can structure data in separate columns per category:

| Class A | Class B | Class C |
|---------|---------|---------|
| 78      | 72      | 65      |
| 82      | 88      | 71      |
| 91      | 79      | 68      |
| 67      | 94      | 73      |
| 85      | 81      | 69      |

Both formats work with Excel’s Box and Whisker chart. The second format is often easier to manage when groups have equal sample sizes.

Before proceeding, clean your data. Remove or address blank cells—Excel handles them inconsistently in chart calculations. Check for text values mixed with numbers, which will cause errors. If you have legitimate missing data, decide whether to exclude those rows or impute values based on your analysis requirements.

Creating a Box Plot in Excel (2016+)

Excel 2016 introduced native box plot support. Here’s the process:

Step 1: Select your data range, including headers. For the columnar format above, select cells A1 through C6.

Step 2: Navigate to the Insert tab on the ribbon. In the Charts group, click the Statistical Chart icon (it looks like a histogram).

Step 3: Select “Box and Whisker” from the dropdown menu.

Excel generates a box plot automatically, calculating all quartiles and identifying outliers using the standard 1.5×IQR rule.

Step 4: Click on the chart to access the Chart Design and Format tabs for customization.

The default chart works for quick analysis, but you’ll typically want to adjust titles, colors, and axis labels before sharing. Right-click any element to access formatting options.

One important setting: Excel offers two whisker calculation methods. Right-click a box and select “Format Data Series.” Under Series Options, you’ll find:

• Exclusive: Excludes median when calculating quartiles (Tukey’s original method)
• Inclusive: Includes median in quartile calculations

The difference matters for small datasets. Exclusive is the statistical standard and matches most other software.

Creating a Box Plot Manually (Older Excel Versions)

If you’re using Excel 2013 or earlier, you’ll need to build box plots using a stacked bar chart workaround. This requires calculating the statistics yourself.

First, create a summary table with these formulas. Assuming your data is in column A, rows 2-21:

| Statistic     | Formula                        | Class A |
|---------------|--------------------------------|---------|
| Minimum       | =MIN(A2:A21)                   | 67      |
| Q1            | =QUARTILE.INC(A2:A21, 1)       | 74.25   |
| Median        | =QUARTILE.INC(A2:A21, 2)       | 82      |
| Q3            | =QUARTILE.INC(A2:A21, 3)       | 87.25   |
| Maximum       | =MAX(A2:A21)                   | 91      |
| IQR           | =Q3-Q1                         | 13      |
| Lower Whisker | =MAX(MIN, Q1-1.5*IQR)          | 67      |
| Upper Whisker | =MIN(MAX, Q3+1.5*IQR)          | 91      |

Next, calculate the segments for a stacked bar chart:

| Segment          | Formula              | Value  |
|------------------|----------------------|--------|
| Bottom Spacer    | =Lower Whisker       | 67     |
| Lower Whisker    | =Q1 - Lower Whisker  | 7.25   |
| Lower Box (Q1-M) | =Median - Q1         | 7.75   |
| Upper Box (M-Q3) | =Q3 - Median         | 5.25   |
| Upper Whisker    | =Upper Whisker - Q3  | 3.75   |

Create a stacked bar chart from these segments. Then format it:

1. Make the Bottom Spacer segment invisible (no fill, no border)
2. Color the Lower Box and Upper Box segments identically
3. Format whisker segments as thin lines or make them invisible and add error bars
4. Add a line for the median by formatting that segment border

This method is tedious but gives you complete control over the visualization.

Customizing Your Box Plot

Default Excel charts rarely meet publication standards. Here are the essential customizations:

Colors and Fills: Right-click any box and select “Format Data Series.” Under Fill, choose solid colors that distinguish categories clearly. For print, consider patterns instead of colors. Remove gradients—they look dated and reduce clarity.

Axis Scaling: Double-click the Y-axis to open formatting options. Set meaningful minimum and maximum values. Don’t let Excel auto-scale if it creates misleading comparisons. For percentage data, always start at 0 and end at 100.

Outlier Markers: By default, Excel shows outliers as small circles. You can change the marker style, size, and color through Format Data Series. Make outliers visually distinct but not overwhelming—they should be noticeable without dominating the chart.

Labels and Titles: Add a descriptive chart title that explains what’s being compared. Include axis labels with units. Remove the legend if category names are clear from the X-axis.

Gridlines: Remove or lighten horizontal gridlines. They add visual noise without improving readability for box plots.

Whisker Style: Excel draws whiskers as simple lines by default. You can’t easily add the traditional perpendicular caps (T-bars) at whisker ends without significant workarounds involving error bars.

Interpreting Box Plot Results

Reading a box plot correctly requires understanding what each element reveals:

Median Position: The line inside the box shows central tendency. If it’s closer to Q1, the data is right-skewed (tail extends toward higher values). Closer to Q3 indicates left-skew.

Box Height (IQR): Taller boxes mean more variability in the middle 50% of data. When comparing groups, this shows which has more consistent values.

Whisker Length: Asymmetric whiskers indicate skewness. Long whiskers relative to box height suggest high variability in the tails.

Outliers: Points beyond whiskers represent unusual observations. One or two outliers in a large dataset is normal. Many outliers suggest the data might not follow a normal distribution, or there are data quality issues.

Comparing Groups: Look for non-overlapping boxes—this suggests statistically significant differences between groups. When one box’s median falls outside another box entirely, the difference is likely meaningful.

Common Issues and Troubleshooting

Whiskers extending to min/max instead of 1.5×IQR: Check your Excel version. Some older builds calculate whiskers incorrectly. Verify the calculation method in Format Data Series options.

Missing outliers: Ensure your data range is selected correctly. Outliers only appear when values exceed 1.5×IQR beyond the quartiles. If your data has no such values, no outliers will display—this is correct behavior.

Boxes appearing at wrong positions: This usually indicates mixed data types. Check that all values are formatted as numbers, not text. Use VALUE() function to convert text-formatted numbers.

Unequal box widths: Excel sometimes varies box width based on sample size. To force equal widths, right-click the chart and select “Change Chart Type,” then reselect Box and Whisker.

Comparison across many categories: With more than 6-8 categories, box plots become cramped. Consider splitting into multiple charts or using a horizontal orientation (switch row/column in Select Data).

Box plots remain one of the most information-dense visualizations available. Once you’ve mastered creating them in Excel, you’ll find yourself reaching for them whenever you need to compare distributions or identify outliers in your data.