Box and Whisker Plot in Excel (Create & Customize)

Box and Whisker Plot in Excel

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 range of cells in the dataset

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.

Choose the Box and Whisker Chart from the Insert Statistic Chart Option

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)

Use MIN Function

You will see the output in the image below.

Show MIN Value

Select cell I6 and copy the formula below:

=QUARTILE.INC(C5:C13,1)

Use QUARTILE.INC Function

You will see the output in the following image.

Show First Quartile Value

Select cell I7 and insert the formula below:

=MEDIAN(C5:C13)

Use MEDIAN Function

You will get the result like in the image.

Show Median Value

Click on the image for a detailed view

Insert the following formula in cell I8:

=QUARTILE.INC(C5:C13,3)

Use QUARTILE.INC Function

Here’s the result.

Show Third Quartile Value

Insert the following formula in cell I9:

=MAX(C5:C13)

Use MAX Function

The results are displayed in the image below.

Show Max Value

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

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

=MIN(C5:C13)

Use MIN Function to Get Min Values

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

=I6-I12

Use Subtraction Formula to Get First Quartile Values

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

=I7-I6

Use Subtraction Formula to Get Median Values

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

=I8-I7

Use Subtraction Formula to Get Third Quartile Values

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

=I9-I8

Use Subtraction Formula to Get Max Values

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.

Select range of cells and choose Stacked Column chart

We will get the following chart.

Right-click on the chart and choose Select Data.

Select Switch Row/Column option

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 DataSeries.

Choose Format Data Series option

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

Select No Fill

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 No Fill

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

Choose Error Bars

Choose Standard Deviation

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

Select Format Error Bars

Select Necessary Parameters from the Format Data Series Window

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

Select No Fill

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

Choose Error Bars

Choose Standard Deviation

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

Select Format Error Bars

Select Necessary Parameters from the Format Data Series Window

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

Show Final Output of Box and Whisker Plot as Stacked Column Chart in Excel

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)

Use AVERAGE Function to Get all the Mean Values

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

Copy all of the cell values with Average label

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

Choose Paste Special

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

Choose New Series

The average series shows as a Stacked Column.

Show Average Series

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

Choose Change Series Chart Type

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.

Choose Line With Markers Type Chart

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

Show Output of the line with average markers

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

Choose Format Data Series

Select No Line

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

Show Final Output of Box and Whisker Plot with average markers

How to Create Box and Whisker Plot with Outliers in Excel

Select the range of cells

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.

Select Insert Statistic 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.

Use MIN Function to Find Min Value

Insert the following formula in cell F6:

=QUARTILE.INC(C5:C15,1)

Press Enter to see the result.

Use QUARTILE.INC Function to Find First Quartile Value

Insert the following formula in cell F7:

=MEDIAN(C5:C15)

Press Enter to see the result.

Use MEDIAN Function to Find Median Value

Copy the following formula in cell F8:

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

Hit Enter to see the output.

Use QUARTILE.INC Function to Find Third Quartile Value

Input the following formula in cell F9:

=MAX(C5:C15)

Hit Enter to see the output.

Use MAX Function to Find Max Value

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.

Use Arithmetic Formula to Find Lower Outlier Value

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.

Box and Whisker Plot in Excel: Knowledge Hub

How to Create Box and Whisker Plot in Excel with Multiple Series?
How to Add Horizontal Box and Whisker Plot in Excel?
How to Rotate Box and Whisker Plot in Excel?