WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Box and Whisker Plot in Excel

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

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.

Popular Course in this category
Sale
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

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

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 –

  • Box Plot in Excel
  • Make Scatter Plots in Excel
  • Create Dot Plots in Excel
  • Create 3D Scatter Plot in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Box and Whisker Plot Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More