• Home
  • Tools dropdown img
    • Spreadsheet Charts

      • ChartExpo for Google Sheets
      • ChartExpo for Microsoft Excel
    • Power BI Charts

      • Power BI Custom Visuals by ChartExpo
    • Word Cloud

  • Charts dropdown img
    • Chart Category

      • Bar Charts
      • Circle Graphs
      • Column Charts
      • Combo Charts
      • Comparison Charts
      • Line Graphs
      • PPC Charts
      • Sentiment Analysis Charts
      • Survey Charts
    • Chart Type

      • Box and Whisker Plot
      • Clustered Bar Chart
      • Clustered Column Chart
      • Comparison Bar Chart
      • Control Chart
      • CSAT Score Bar Chart
      • CSAT Score Survey Chart
      • Dot Plot Chart
      • Double Bar Graph
      • Funnel Chart
      • Gauge Chart
      • Likert Scale Chart
      • Matrix Chart
      • Multi Axis Line Chart
      • Overlapping Bar Chart
      • Pareto Chart
      • Radar Chart
      • Radial Bar Chart
      • Sankey Diagram
      • Scatter Plot Chart
      • Slope Chart
      • Sunburst Chart
      • Tornado Chart
      • Waterfall Chart
      • Word Cloud
    • Google Sheets
      Microsoft Excel
  • Services
  • Pricing
  • Blog
  • Support dropdown img
      • Gallery
      • Videos
      • Contact Us
      • FAQs
      • Resources
    • Accessible Support

      for Your Journey

Categories
All Data Visualizations Data Analytics Data Stories
Add-ons
  • Google Sheets
  • Microsoft Excel
  • Power BI
All Data Visualizations Data Analytics
Data Stories
  • Healthcare
Add-ons
  • Google Sheets
  • Microsoft Excel
  • Power BI

We use cookies

This website uses cookies to provide better user experience and user's session management.
By continuing visiting this website you consent the use of these cookies.

Ok

ChartExpo Survey



Home > Blog > Data Visualization > Add-ons > Microsoft Excel >

How to Make a Box and Whisker Plot?

A Box and Whisker Chart is one of the best-suited charts for displaying data distribution in quartiles while highlighting mean and outliers.

how to make a box and whisker plot

Besides, the chart is easy to interpret, even for non-technical audiences.

Excel does not natively support Box and Whisker Charts. And this means you’ve got to use other pricey tools or plot the chart manually. So, if your goal is to display attributes, such as mean, outliers, and quartiles in your data, you’ve got to think beyond Excel.

Don’t get us wrong.

We’re not recommending you do away with Excel, especially if your goal is to access ready-made and visually appealing Box and Whisker Charts.

Download and install a particular add-in (which we’ll mention later) into your Excel to access ready-to-go charts.

In this blog, you’ll learn:

  • How to Make a Box and Whisker Plot?
  • The benefits and limitations of Box and Whisker Plot
  • How to read a Box Plot?
  • We’ll also address the following question: what does the shape of a Box Plot tell you?
  • Also, we’ll recommend the tested and proven add-in to install in Excel to access visually stunning and ready-made Box and Whisker Charts.

How to Create a Box and Whisker Chart? (Easy-to-Follow Steps)

Before diving right into the how-to guide, let’s address the following question:

What is a Box and Whisker Plot?

A Box and Whisker Plot (also known as Box Plot) is a visualization design that uses box shapes to display insights into data.

The chart simplifies bulky and complex data sets into quartiles and averages. Also, you can use the chart to pinpoint outliers in your data. The Box Plot segments key variables in quarters or (quartiles).

For instance, you can draw boxes to connect the first quartile to the third quartile. In this case, the boxes will represent the average values of key data points.

Whiskers are lines that identify numbers outside of the average data points. The highest and lowest variables in your data can be outliers, depending on their magnitude and frequency of occurrence. You can use the Box and Whisker Chart to perform the following tasks:

  • Provide descriptive information, such as the average and quartile, of key variables in your data.
  • Show the best and worst-performing products in your inventory.
  • Display average fill volumes in your data.
  • Point out out-of-specification issues that occur the most.

When to Use Box Plots?

Use the Box and Whisker Chart in the following scenarios:

  • Your data should have continuous or categorical attributes to get the most from the visualization design.
  • Use the visualization design to conduct tests like A/B tests in pay-per-click (PPC) marketing.
  • You can use Box Plot as an Outlier detector.

What does the shape of a Box Plot tell you?

The five-number summary is the descriptive information that a Box and Whisker Chart displays in a data story. And it’s made up of the following:

  • Minimum range
  • First quartile
  • Median
  • Upper quartile
  • Maximum range
box and whisker plot

Three out of the five summary numbers are displayed by default (median, minimum number, and maximum number). You have an option to display the other two summary numbers (upper and lower quartiles).

Besides the five summary numbers, the visualization displays the following:

  • Mean
  • Mean Deviation
  • Standard Deviation
  • Quartile Deviation
  • Outliers

To execute the five-number summary principle, enable quartiles to get descriptive insights.

The Box and Whisker Chart has three quartiles, namely:

  • Q1 (lower quartile)
  • Q2 (median)
  • Q3 (upper quartile).

Let’s check out other insights displayed by the visualization design.

  • Mean

The mean is the usual average.

The formula to calculate the mean is:

ΣXi /N

N is the total number of elements in a set of data. And X is the data points present in the set of data. i is the position of the values.

  • Median

Arrange your data in ascending order (from the smallest to the greatest number) to find the median. The element that resides in the center-most position is the median.

You can also use the following formula (N+1)/2 to find the position, where N is the number of elements in the data. If your data consists of an even number of elements, get two middle numbers. The average of the two numbers is the median.

  • Lower Quartile (Q1)

The median divides the Box and Whisker Chart into a lower half and an upper half.

The lower quartile is the middle value of the lower half, i.e., the element between the minimum number and the median.

Use the following formula to derthe ive lower quartile (N+1)/4.

  • Upper Quartile (Q3)

The upper quartile is the middle value between the maximum number and the median.

The formula for deriving the upper quartile is (3N+3)/4. An for even number of elements is 3N/4.

  • Mean Deviation

Mean deviation is the average of the absolute differences between each variable and the mean. The statistic gives you an idea of how spread-out key variables are from the mean.

Use the following formula to calculate the mean deviation:

Σ|Xi – mean|/N, where N is the total number of elements in a set of data. And X is the data points or values present in the data.

i is the position of the values in the set of data.

For grouped data, the formula is:

Σf |Xi – mean|/N, where f is the frequency of occurrence.

Why are Box Plots Useful?

Check out the benefits of a Box and Whisker Chart below.

You can use the chart to show whether your data set is normally distributed or skewed.

box plot 1

When the median is in the middle of a Box and Whisker Chart, the distribution is symmetric.

box plot 2

Conversely, when the median is closer to the bottom of the box, then the distribution is positively skewed (skewed right).

box plot 3

When the median is closer to the top of the box, then the distribution is negatively skewed (skewed left).

How to Make a Box and Whisker Plot? Step by Step Guide with Example

Excel is one of the go-to data visualization tools for businesses and professionals.

However, this freemium spreadsheet tool does not natively support Box and Whisker Charts. In other words, you’ll never find this visualization design in Excel.

Well, you don’t have to do away with the spreadsheet app.

You can turn Excel into a reliable data visualization tool loaded with ready-made and visually stunning Box and Whisker Charts by installing third-party apps, such as ChartExpo.

How to plot a Box Plot in Excel should never be a stressful task. Keep reading to learn more.

Why ChartExpo?

ChartExpo is an add-in you can easily install in your Excel.

With many advanced visualizations, ChartExpo turns your complex, raw data into compelling, easy-to-digest, visual renderings that tell the story of your data.

The application produces simple, ready-to-go, and clear visualization designs with just a few clicks.

Yes, ChartExpo is the best Box and Whisker Chart generator that generates visualizations that are amazingly easy to interpret, even for non-technical audiences.

In the coming section, you’ll get to see ChartExpo in action. You don’t want to miss this. We will tell you how to create Box and Whisker Plot with Examples.

Example

This section will use a Box and Whisker Graph to display insights into the table below.

Employee Current Orders Previous Orders
John 148 129
Smith 127 196
Rhonda 168 120
Simon 146 129
John 176 119
Smith 176 141
Rhonda 166 181
Simon 128 148
John 172 121
Smith 158 146
Rhonda 149 192
Simon 100 152
John 191 124
Smith 105 96
Rhonda 118 99
Simon 10 60
John 99 192
Smith 104 149
Rhonda 164 135
Simon 122 183

Click here to install ChartExpo into your Excel. Once you’re done, follow the easy steps below.

  • Open your Excel.
  • Open the worksheet and click the Insert Menu button.
  • Click the My Apps button.
insert chartexpo in excel
  • Select ChartExpo and click the Insert button to get started with ChartExpo.
open chartexpo in excel
  • Once ChartExpo is loaded, you will see a list of charts. Paste the table above into your Excel.
list of charts in excel
  • Once the Chart pops up, click on Box and Whisker Column Chart icon to get started, as shown below.
search chart in excel
  • Select the sheet holding your data and click the Create Chart From Selection button.
create chart in excel

How to Edit the Box and Whisker Chart Excel?

To add additional details, such as the title in your chart, follow the steps below:

  • Click the Edit button and then click the pencil-like marker near the title section.
edit chart in excel
  • Once the Chart Header Top Properties window pops up, click the Line 1 button and fill in the title.
edit chart properties in excel
  • Save your new changes by clicking the Apply button.
edit quartile in excel
  • To display First Quartile, Second Quartile, Third Quartile, and Mean, toggle the buttons to the right side, as shown above.
  • Save your new changes by clicking the Save Changes button.
save chart in excel
  • Check out the final chart below.
box and whisker chart in excel

How to Read a Box Plot?

As we said, the Box and Whisker Chart displays the distribution, outliers, and median attributes of your data.

  • The Box Plot summarizes your data into five categories: mean, median, upper and lower quartiles, and standard deviation.
  • The mark with the lowest value is called the minimum.
  • The box contains the lower quartile, the upper quartile, and the median in the center.
  • The median is the value separating the higher half from the lower half of your data. Think of the median as “the middle” value in a set of numbers based on a count of your values rather than the middle based on the numeric value.

How to read a Box Plot should never be a stressful task.

Benefits and Limitations of Box and Whiskers Plot

Check out the benefits of a Box and Whisker Chart in data analysis

  • Takes less space

Box And Whisker Plots use less space compared to other graphs and charts. And this implies you can use the visualization design to visualize complex and bulky data sets.

  • Provides high-level insights

Unlike other visualization designs, the chart provides in-depth details into your data, such as median, mean, quartile, and standard deviation from the mean.

  • Clear Summary

A Box and Whisker Chart is best-suited for displaying a summary of one or more data sets.

  • Displays Outliers

The chart is among the recommended visualization designs that automatically display outliers in your data. It uses whiskers to show both extremes of key variables in your data.

Limitations

  • Exact Values Not Retained

A Box Plot shows only a simple summary of the distribution of results. Combine the Chart with other graphs to get comprehensive insights into your data.

When to Use Box Plots?

FAQs:

What is Box and Whisker Plot?

A Box and Whisker Plot (also known as Box Plot) is a visualization design that uses Japanese candle shapes to display insights into data.

The chart simplifies bulky and complex data sets into quartiles and averages. Also, you can use the chart to pinpoint outliers in your data.

What are the advantages of using Box and Whisker Plot?

Box And Whisker Plots use less space compared to other graphs and charts. And this implies you can use the visualization design to visualize complex and bulky data sets.

Secondly, the chart is best suited for displaying a summary of one or more data sets.

What is a Box and Whisker Chart used for?

A Box and Whisker Chart shows the data distribution into quartiles highlighting the mean and outliers. The boxes may have lines extending vertically called “whiskers”. These lines indicate variability outside the upper and lower quartiles.

Other points outside those lines or whiskers are considered outliers.

Wrap Up

As we said, the Box and Whisker Chart is one of the best-suited charts for displaying the distribution of data in quartiles, mean, and outliers.

Excel does not natively support Box and Whisker Charts. If your goal is to display attributes, such as mean, outliers, and quartiles in your data, you’ve got to think beyond Excel.

So, what’s the solution?

We recommend installing third-party apps, such as ChartExpo, into your Excel to access ready-made Box and Whisker Charts.

ChartExpo is an add-in you can easily download and install in your Excel app. Besides, this tool comes loaded with insightful and easy-to-interpret Box Plots.

Here’s the bottom line: You don’t need programming or coding skills to visualize your data using ChartExpo.

Sign up for a 7-day free trial today to access easy-to-interpret and ready-made Box and Whisker Charts.

How much did you enjoy this article?

chartexpo ad
chartexpo ad
chartexpo ad
chartexpo ad
119188

Related articles

next previous
Microsoft Excel9 min read

Visualizing Data Using Customer Satisfaction Survey Software

Discover the best customer satisfaction survey software. You’ll also learn how to visualize and analyze survey results in Excel for actionable insights.

Microsoft Excel9 min read

How to Analyze Customer Experience Measures in Excel?

Learn how to measure and analyze customer experience using Excel. Discover the six key customer experience measures and how to create effective charts.

Microsoft Excel9 min read

How to Analyze Competitive Analysis Research Data in Excel?

Click here to learn how to effectively analyze competitive analysis research data in Excel with the best charts and visualization techniques.

Microsoft Excel9 min read

Mastering the Art of Analyzing and Interpreting Data in Excel

Learn the essential techniques for analyzing and interpreting data in Excel. We’ll show you how to create charts, interpret data, and unlock insights.

Microsoft Excel9 min read

How to Analyze Market Research Survey Data in Excel?

Learn how to conduct and analyze a market research survey. This guide also has the best market research questions to ask in your next survey.

ChartExpo logo

Turn Data into Visual
Stories

CHARTEXPO

  • Home
  • Gallery
  • Videos
  • Services
  • Pricing
  • Contact us
  • FAQs
  • Privacy policy
  • Terms of Service
  • Sitemap

TOOLS

  • ChartExpo for Google Sheets
  • ChartExpo for Microsoft Excel
  • Power BI Custom Visuals by ChartExpo
  • Word Cloud

CATEGORIES

  • Bar Charts
  • Circle Graphs
  • Column Charts
  • Combo Charts
  • Comparison Charts
  • Line Graphs
  • PPC Charts
  • Sentiment Analysis Charts
  • Survey Charts

TOP CHARTS

  • Sankey Diagram
  • Likert Scale Chart
  • Comparison Bar Chart
  • Pareto Chart
  • Funnel Chart
  • Gauge Chart
  • Radar Chart
  • Radial Bar Chart
  • Sunburst Chart
  • see more
  • Scatter Plot Chart
  • CSAT Score Bar Chart
  • CSAT Score Survey Chart
  • Dot Plot Chart
  • Double Bar Graph
  • Matrix Chart
  • Multi Axis Line Chart
  • Overlapping Bar Chart
  • Control Chart
  • Slope Chart
  • Clustered Bar Chart
  • Clustered Column Chart
  • Box and Whisker Plot
  • Tornado Chart
  • Waterfall Chart
  • Word Cloud
  • see less

RESOURCES

  • Blog
  • Resources
  • YouTube
SIGN UP FOR UPDATES

We wouldn't dream of spamming you or selling your info.

© 2023 ChartExpo, all rights reserved.