EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Charts Box and Whisker Plot in Excel
Secondary Sidebar
Excel Functions
  • Excel Charts
    • Excel Data Visualization
    • Dashboard in Excel
    • Bullet Chart in Excel
    • Excel Animation Chart
    • Plots in Excel  
    • Interactive Chart in Excel
    • Surface Charts in Excel
    • Excel Calendar Template
    • Thermometer Chart in Excel
    • Box Plot in Excel
    • S CURVE in Excel
    • Venn Diagram in Excel
    • Project Management with Gantt Chart
    • Change Chart Style in Excel
    • 3D Scatter Plot in Excel
    • Box and Whisker Plot in Excel
    • Radar Chart in Excel
    • Control Charts in Excel
    • Dot Plots in Excel
    • Marimekko Chart Excel
    • Combo Chart in Excel
    • Contour Plots in Excel
    • Candlestick Chart in Excel
    • Map Chart in Excel
    • Pie Chart Examples
    • Excel Stacked Bar Chart
    • Excel Flowchart
    • Excel Types of Graphs
    • Excel Normal Distribution Graph
    • Excel Bell Curve
    • Legends in Excel Chart
    • Excel Chart Templates
    • Excel Organization Chart
    • Excel Doughnut chart
    • Excel 3D Plot
    • Excel Scatter Chart
    • Excel Line Chart
    • Excel Combination Charts
    • Excel Gauge Chart
    • Excel Pivot Chart
    • Excel Clustered Column Chart
    • Excel Dynamic Chart
    • Excel Stacked Column Chart
    • Excel Bubble Chart
    • Histogram Chart Excel
    • Excel Gantt Chart
    • Excel Stacked Area Chart
    • Excel Waterfall Chart
    • Excel Area Chart
    • Excel Clustered Bar Chart
    • Excel Column Chart
    • Excel Pie Chart
    • Comparison Chart in Excel
    • Pareto Analysis in Excel
    • Add a Secondary Axis in Excel
    • Grouped Bar Chart
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED COURSE
  • Excel Data Analysis Course
  • Excel for Marketing Course

Box and Whisker Plot in Excel

By Jeevan A YJeevan A Y

B&WP in Excel

Box and Whisker Plot in Excel (Table of Contents)

  • Introduction to Box and Whisker Plot in Excel
  • How to Create Box and Whisker Plot in Excel?

Introduction to Box and Whisker Plot in Excel

By just looking at the numbers will not tell the story better, so we rely on visualizations. To create visualizations, we have sophisticated software’s in this modern world to create beautiful visualizations. However, amidst all the sophisticated software’s majority of the people still use Excel for their visualization.

When we talk about visualization, we have one of the important charts, i.e. “Box and Whisker Plot in Excel”. This is not the most popular chart in nature but the very effective chart in general. So in today’s article, we will show you about Box and Whisker Plot in Excel.

What is Meant by Box and Whisker Plot in Excel?

Box and Whisker Plot is used to show the numbers trend of the data set. Box and Whisker plot is an exploratory chart used to show the distribution of the data. This chart is used to show a statistical five-set number summary of the data.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

These five statistical numbers summary are “Minimum Value, First Quartile Value, Median Value, Third Quartile Value, and Maximum Value”. These five numbers are essential to creating “Box and Whisker Plot in Excel”. Below is the explanation of each five numbers

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,547 ratings)
  1. Minimum Value: What is the minimum or smallest value from the dataset?
  2. First Quartile Value: What is the number between the minimum value and Median Value?
  3. Median Value: What is the middle value or median of the dataset?
  4. Third Quartile Value: What is the value between the median value and maximum value?
  5. Maximum Value: What is the highest or largest value from the dataset?

How to Create Box and Whisker Plot in Excel?

Let us see the examples of creating Box and Whisker Plot in Excel.

You can download this Box And Whisker Plot in Excel here – Box And Whisker Plot in Excel

Example on Box and Whisker Plot

Below is the data I have prepared to show “Box and Whisker Plot ” in Excel.

Box & Whisker Plot in Excel 1.1

This is the data of educational examination of the past three years which says the pass percentage of students who appeared for the examination.

To create “Box and Whisker Plot in Excel”, first we need to calculate the five statistical numbers from the available data set. Five numbers of statistics are “Minimum Value, First Quartile Value, Median Value, Third Quartile Value, and Maximum Value”. For this, create a table like shown below.

Box and Excel 1.2

First, we need to calculate what the smallest or minimum value is for each year. So apply excel’s built-in function “MIN” function for all the years, as shown below.

Box & Excel 1.3

The second calculation is to calculate what the First Quartile Value is. For this, we need another built-in function, QUARTILE.INC. To find the first Quartile value, below is the formula.

Box and Excel 1.5

The third Statistical Calculation is Median Value; for this, below is the formula.

Box and Excel 1.4

The 4th statistical calculation is the third quartile value; for this change, the last parameter of the QUARTILE.INC function to 3.

Box and Excel 1.6

The last statistic is the calculation of the Maximum or Largest value from the available data set.

Box and Excel 1.7

Once all the five number statistics calculation is done, create a replica of the calculation table but delete numbers.

Box and Whisker Plot in Excel 1.8

For Minimum Value cells, give a link from the above table only.

Box and Whisker Plot in Excel 1.9

Next, we need to find the First Quartile Value, for this formula is below

First Quartile – Minimum Value.

Box and Whisker Plot in Excel 1.10

Next, we need to find the Median Value, for this formula is below

Median Value – First Quartile.

Box and Whisker Plot in Excel 1.11

Next, we need to find the Third Quartile Value, for this formula is below

 Third Quartile – Median Value.

Box and Whisker Plot in Excel 1.12

Next, we need to find the Maximum Value, for this formula is below

Maximum Value – Third Quartile.

Box and Whisker Plot in Excel 1.13

So, now our all the calculations are ready to insert a chart. Now select the data to insert the Stacked Column Chart.

BOX 2

Now our chart will look like the below.

B&WP in Excel 1.14

Select the chart; now, we can see chart tools appear on the ribbon. Under the DESIGN ribbon, select “Switch Row / Column”.

B&WP in Excel 1.15

This will interchange rows & column data in the chart is switched, so our new chart looks like the below one.

B&WP in Excel 1.16

Now we need to format the chart; follow the below steps to format the chart.

Select the bottom-placed bar, i.e. blue-coloured bar and make the fill as No Fill.

B&WP in Excel 1.17

So now the bottom bar disappears from the chart.

B&WP in Excel 1.18

Right-click on the chart and choose “Select Data”.

B&WP in Excel 1.19

In the below window, click on the EDIT button on the right side.

B&WP in Excel 1.20

Now select Axis Label as year headers.

B&WP in Excel 1.21

Now horizontal axis bars look like this.

B&WP in Excel 1.22

The BOX chart is ready to use in Box And Whisker Plot in Excel, but we need to insert WHISKER to the chart. To insert WHISKER, follow the below steps.

Now select the top bar of the chart makes NO FILL.

B&WP in Excel 1.23

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

B&WP in Excel 1.24

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

B&WP in Excel 1.25

Now we got Whisker lines on top of the bars.

B&WP in Excel 1.26

Now select Whisker lines and press Ctrl + 1 to open the format data series option.

Under “Forma Error Bars”, do the following changes.

>>> Direction “Minus”

>>> End Style “No Cap”.

>> Error Amount > Percentage > 100%.

B&WP in Excel 1.27.1

Under Format Error Bars, it will somewhat look like this-

B&WP in Excel 1.27.2

Now similarly, we need to insert a whisker to the bottom as well. For this selection, the bottom-placed bar and make the FILL as NO FILL.

B&WP in Excel 1.28

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

B&WP in Excel 1.29

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

B&WP in Excel 1.30

B&WP in Excel 1.31

Repeat the same steps as we did for top bar whiskers, now we will have our Box and Whisker plot chart ready to use.

Things to Remember

  • There is no built-in Box and Whisker plot chart in excel 2013 and earlier versions.
  • We need to calculate five-number summary items like “Minimum Value”, “First Quartile Value”, “Median Value”, “Third Quartile Value”, and “Maximum Value”.
  • All these calculations can be done by using excel built-in “Quartile.INC” formula.

Recommended Articles

This is a guide to Box and Whisker Plot in Excel. Here we discuss the meaning and how to create Box and Whisker plots in excel with examples. You can also go through our other suggested articles to learn more –

  1. Box Plot in Excel
  2. Plots in Excel
  3. 3D Scatter Plot in Excel
  4. Contour Plots in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formulas, charts, formatting creating excel dashboard & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Box And Whisker Plot in Excel

EDUCBA

Download Box And Whisker Plot in Excel

EDUCBA

डाउनलोड Box And Whisker Plot in Excel

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more