Box and Whisker Plot in Excel | How to Create Box and Whisker Plot?

Excel Box and Whisker Plot

Box & Whisker Plot in Excel is an exploratory chart used to show statistical highlights and distribution of the data set. This chart is used to show a five-number summary of the data. These five-number summary are “Minimum Value, First Quartile Value, Median Value, Third Quartile Value, and Maximum Value”. Using these statistics we display the distribution of dataset, below are the detailed explanation of these statistics.

Minimum Value: The minimum or smallest value from the dataset.
First Quartile Value: This the value between the minimum value and Median Value.
Median Value: Median is the middle value of the dataset.
Third Quartile Value: This the value between the median value and maximum value.
Maximum Value: The highest value of the dataset.

One of the problems with Box & Whisker Plot chart is it looks like not familiar to use outside the statistical world may be due to lack of awareness among its users in the Excel community. It could well also be the reason for lack of knowledge on interpretation from the chart.

How to Create Box and Whisker Plot in Excel?

You can download this Box and Whisker Plot Excel Template here – Box and Whisker Plot Excel Template

For a better explanation of the Box & Whisker Plot chart with excel, I am taking the sample data of the examination of the previous 3 years, below is some data.

For each year the first thing we need to do is to calculate the five number of statistics from the above data. Five number of statistics are “Minimum Value, First Quartile Value, Median Value, Third Quartile Value, and Maximum Value”.

For this create a table like the below.

First Calculate Minimum Value for each year.

Then calculate First Quartile Value.

Then Calculate Median Value.

Next, calculate the third quartile value.

Then the final statistics are Maximum value from the lost.

Ok, now we are done with five number statistics. However, we need to create one more similar table to find the differences. Retain only minimum value as it is.

To find the difference for First Quartile is First Quartile – Minimum Value.

To find the difference for Median Value is Median Value – First Quartile.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

To find the difference for Third Quartile is Third Quartile – Median Value.

To find the difference for Maximum Value is Maximum Value – Third Quartile.

Our final table is ready to insert chart for the data. Now select the data to insert Stacked Column Chart.

Now we will have a chart like below.

By selecting the data under Design ribbon select “Switch Row / Column”.

Now our rows & column data in the chart is switched, so our modified chart looks as follows.

Now select the bottom-placed bar i.e. blue-colored bar and make the fill as No Fill.

So now the bottom bar is not visible in the chart.

Now change the Horizontal Axis Labels to 2017, 2018 and 2019.

Now Box chart is ready now we need to create Whisker for these boxes. Now select the top bar of the chart makes no fill.

Now by selecting the same bar go to the Design tab and Add Chart Elements.

Under Add Chart Elements click on “Error Bars > Standard Deviation”.

Now we got Whisker lines on top of the bars.

Now select newly inserted Whisker lines and click Ctrl + 1 to open the format data series option to the right of the chart.

Under “Format Error Bars” do the following changes.

>>> Direction “Minus”

>>> End Style “No Cap”.

>>> Error Amount > Percentage > 100%.

Now the Whisker lines will look as shown below:

Now select the bottom-placed bar and make the Fill as No fill.

Then follow the same steps as above to add whisker line at the bottom of the box. Now your Box and Whisker Excel Chart will look as follows.

Excel Box and Whisker Plot

How to Create Box and Whisker Plot in Excel?

Recommended Articles