Box and Whisker Plot in Excel

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 the dataset; below are the detailed explanation of these statistics.

  • Minimum Value: The minimum or smallest value from the dataset.
  • First Quartile Value: Its 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 the 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.

Box-and-Whisker-Plot-in-Excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Box and Whisker Plot in Excel (wallstreetmojo.com)

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.

Box and Whisker Plot Example 1

For each year, the first thing we need to do is to calculate the five numbers 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 below.

Box and Whisker Plot Example 1-1

First, Calculate the Minimum Value for each year.

Box and Whisker Plot Example 1-2.png

Then calculate First Quartile Value.

Box and Whisker Plot Example 1-3

Then Calculate the Median Value.

Box and Whisker Plot Example 1-4.png

Next, calculate the third quartile value.

Box and Whisker Plot Example 1-5.png

Then the final statistics are Maximum value from the lost.

Box and Whisker Plot Example 1-6.png

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.

Box and Whisker Plot Example 1-7

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

Box and Whisker Plot Example 1-8

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

Box and Whisker Plot Example 1-9

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

Box and Whisker Plot Example 1-10

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

Box and Whisker Plot Example 1-11

Our final table is ready to insert a chart for the data. Now select the data to Insert Stacked Column Chart in ExcelInsert Stacked Column Chart In ExcelA stacked column chart in Excel is a column chart in which multiple series of data representations of various categories are stacked over each other. The stacked series are vertical, and comparing multiple data series is easy, but as the number of data series increases, so does the complexity of representation.read more.

Box and Whisker Plot Example 1-12

Now we will have a chart like below.

Box and Whisker Plot Example 1-13

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

Box and Whisker Plot Example 1-14

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

Box and Whisker Plot Example 1-15

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

Box and Whisker Plot Example 1-16

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

Box and Whisker Plot Example 1-17

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

Box and Whisker Plot Example 1-19

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.

Box and Whisker Plot Example 1-20

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

B and W Plot Example 1-21

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

B and W Plot Example 1-22

Now we got Whisker lines on top of the bars.

B and W Plot Example 1-23

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

B and W Plot Example 1-24

Now the Whisker lines will look as shown below:

B and W Plot Example 1-25

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

B and W Plot Example 1-26

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

Box and WhiskerPlot Example 1-27

Recommended Articles

This has been a guide to Box and Whisker Plot in Excel. Here we discuss how to create a box and whisker plot chart in excel along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>