Box and Whisker Plot in Excel (Create & Customize)

The box and whisker plot in Excel shows the distribution of quartiles, medians, and outliers in the assigned dataset.

This article will demonstrate how to create box and whisker plots in Excel with easy approaches. You will learn how to use a Stacked Column chart and apply the Box and Whisker chart option to create a box and whisker plot in Excel.

Download Practice Workbook

You can download our practice workbook here for free!

Box and Whisker Plot.xlsx

How to Create Box and Whisker Plot in Excel?

Let’s use a simple dataset to explain two ways of creating a box and whisker plot.

Method 1 – Create Box and Whisker Plot Using Box and Whisker Chart

Select the range of cells from B4 to E13.

Go to the Insert tab in the ribbon.
Select the Insert Statistic Chart drop-down option from the Charts group.
Choose the Box and Whisker chart.

You will see the Box and Whisker chart in the image below.

Method 2 – Create Box and Whisker Plot Using Stacked Column Chart

In this approach, we’ll make a box and whisker plot in Excel using the stacked column chart, then by plotting it using the stacked column diagram.

Step 1 – Prepare Dataset

Prepare the data for a single record that contains numerous entries. We will generate additional information for the box and whisker charts using this dataset.

Select cell I5 and input the following formula:

=MIN(C5:C13)

Press Enter.
Drag the Fill Handle icon to cell K5.

You will see the output in the image below.

Select cell I6 and copy the formula below:

=QUARTILE.INC(C5:C13,1)

Press Enter.
Drag the Fill Handle icon to cell K6.

You will see the output in the following image.

Select cell I7 and insert the formula below:

=MEDIAN(C5:C13)

Press Enter and drag the Fill Handle icon to cell K7.

You will get the result like in the image.

Click on the image for a detailed view

Insert the following formula in cell I8:

=QUARTILE.INC(C5:C13,3)

Press Enter.
Drag the Fill Handle symbol to cell K8.

Here’s the result.

Insert the following formula in cell I9:

=MAX(C5:C13)

Press Enter.
Drag the Fill Handle symbol to cell K9.

The results are displayed in the image below.

To identify the differences, we must also generate another comparable table.

For the minimum value, we will use the following function:

=MIN(C5:C13)

To determine the difference for Quartile 1, use the following formula:

=I6-I12

To determine the difference for the Median, insert the following formula:

=I7-I6

To find the difference for Quartile 3, apply the following formula:

=I8-I7

To get the difference for the Maximum value, use the following formula:

=I9-I8

Step 2 – Insert Stacked Column Chart

Select the range of cells from I11 to K16.
Go to the Insert tab in the ribbon.
From the Charts group, select Insert Column or Bar Chart.
Choose the Stacked Column chart.

We will get the following chart.

Right-click on the chart and choose Select Data.

Click on Switch Row/Column.

Click OK.

You will see that the chart has been switched.

Step 3 – Customize Chart

Right-click on the lower part of the chart.
Choose Format Data Series.

We will select No Fill. As a result, the bottom bar is no longer visible on the graph.

The box diagram is done. The whiskers for these boxes must then be made.
We will select the top bar of the chart.
Then, choose No Fill.

Select the Error Bars from the Chart Elements by selecting the same bar.

Choose Standard Deviation.

Right-click on the error bars.
Select Format Error Bars.

Select Minus in Direction option, No Cap in End Style option and keep the percentage at 100% in Error Amount.

The whisker lines will now appear in the following image. Choose No Fill by selecting the bottom bar.

Select the Error Bars from the Chart Elements by selecting the same bar.

Choose Standard Deviation.

Right-click on the error bars and select Format Error Bars.

Select Minus, No Cap, and keep the percentage at 100%.

Our Excel box and whisker chart will appear in the following image.

How to Add Average Marker to Box and Whisker Plot in Excel

To determine the average for the data ranges, enter the AVERAGE function in cell I17:

=AVERAGE(C5:C13)

Copy all of the cell values as well as the cells with the Average label.

Click on the chart, then select the Paste button on the ribbon’s Home tab.
Click Paste Special.

Select “New Series“, “Values in Rows,” and “Series Names in First Column” in the Paste Special dialog box, then click OK.

The average series shows as a Stacked Column.

Choose Change Series Chart Type from the context menu by right-clicking one of the columns.

In the Change Chart Type dialog box, choose the Combo.
Find the Average in the list of series, change its chart type to Line With Markers, and then click OK.

This is the following output of the line with average markers.

Right-click on the average line.
Choose Format Data Series.

Select No Line.

Our final box and whisker plot chart with an average marker will look like this.

How to Create Box and Whisker Plot with Outliers in Excel

Select the range of cells from C5 to C15.

Go to the Insert tab in the ribbon.
Select the Insert Statistic Chart drop-down option from the Charts group.
Choose the Box and Whisker chart.

You will see the box and whisker plot chart with outliers.

If you want to know the value of the Lower outlier and Upper outlier, you need to calculate the Minimum value, Median, Quartile 1, Quartile 3, Maximum value, Mean value, and Inter Quartile Range (IQR).

Copy the following formula in cell F5:

=MIN(C5:C15)

Click Enter to see the result.

Insert the following formula in cell F6:

=QUARTILE.INC(C5:C15,1)

Press Enter to see the result.

Insert the following formula in cell F7:

=MEDIAN(C5:C15)

Press Enter to see the result.

Copy the following formula in cell F8:

=QUARTILE.INC(C5:C15,3))

Hit Enter to see the output.

Input the following formula in cell F9:

=MAX(C5:C15)

Hit Enter to see the output.

Copy the following formula in cell F10:

=AVERAGE(C5:C15)

Press Enter to see the result.

Use the following formula in cell F11 to find the interquartile range:

=F8-F6

Press Enter to see the result.

Insert the following formula in cell F12 to find the Upper outlier:

=F8+(F11*1.5)

Press Enter to see the output.

Insert the following formula in cell F13 to find the Lower outlier:

=F6-(F11*1.5)

Press Enter to see the output.

Things to Remember

Box and Whisker charts are not available in all Excel versions. They should be available starting in Excel 2016, Excel 2019, and Excel 365. You might need to use a different method to create box plots if you’re using an old version.
Make sure that your data is properly arranged in rows or columns.

Frequently Asked Questions

1. Is it possible to make a box and whisker plot in previous versions of Excel?

Box and whisker charts are accessible in newer versions of Excel, such as Excel 2016. If you have an older version of Excel, such as Excel 2013 or earlier, the built-in feature to make box and whisker plots may be missing. You can either upgrade to a newer version of Excel or use alternative software or web tools created expressly for making box and whisker plots.

2. Can I make a box and whisker plot with many data sets in Excel?

Yes, you can make a box and whisker plot in Excel using various data sets. When constructing the chart, simply include data from all of the sets in your selection. Each data set will be depicted on the chart as a separate box and whisker plot, allowing for easy comparison.

3. What if my data has negative values?
If your data includes negative values, the Box and Whisker Plot will handle them just like positive values. The box will still represent the interquartile range, and the whiskers will extend accordingly.