• Home
  • Tools
    • ChartExpo for Google Sheets
    • ChartExpo for Microsoft Excel
  • 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
      • Overlapping Bar Chart
      • Pareto Chart
      • Radar Chart
      • Radial Bar Chart
      • Sankey Diagram
      • Scatter Plot Chart
      • Sunburst Chart
    • Chart category
      • Bar Charts
      • Combo Charts
      • Comparison Charts
      • Line Graphs
      • Survey Charts
  • Gallery
  • Pricing
  • Contact Us
  • FAQs
  • Blog

Get a reward

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

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
Home > Blog > Data Visualization > Add-ons > Microsoft Excel >

Box and Whisker Chart Generator in Excel

A Box and Whisker Chart is one of the graphs you can use to display data distribution in quartiles while highlighting mean and outlier attributes.

box and whisker chart excel

The graph is a simple and clear design, which eases interpretation.

Excel lacks Box and Whisker Charts. The only viable options are using other pricey data visualization tools or plotting the Chart manually. So, if your goal is to display data attributes, such as mean, outliers, and quartiles in your data, think beyond Excel.

We’re not advocating you do away with Excel.

The tested and proven option is downloading and installing a particular add-in into your Excel to access ready-to-go Box and Whisker Charts.

In this blog, you’ll learn:

  • How to create a Box and Whisker Chart in Excel for your data stories?
  • We’ll also address the following question: What is a Box and Whisker Chart used for?
  • Also, we’ll recommend the best add-in to install in Excel to access the visually stunning and ready-to-go Box and Whisker Charts.

How to Create a Box and Whisker Chart in Excel for Your Data Stories?

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

Why use Box and Whisker Chart?

The Box and Whisker Chart Excel 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 values 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.

What does a Box and Whisker Chart Look Like?

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

Use the chart if your goal is to display high-level insights, such as mean, quartiles, standard deviation, and median.

Take a look at the table below.

Can you provide reliable insights into the table below?

Subjects Male Female
Chemistry 54 2
Physics 45 5
Mathematics 35 15
Computer 36 14
Chemistry 38 20
Physics 52 15
Mathematics 28 20
Computer 58 15
Chemistry 47 12
Physics 30 20
Mathematics 41 21
Computer 52 18
Chemistry 48 12
Physics 35 15
Mathematics 62 8
Computer 56 29
Chemistry 27 13
Physics 79 21
Mathematics 75 15
Computer 55 25

Note the difference after visualizing the data.

For Male Data:

box and whisker chart using male data

For Female data:

box and whisker chart using female data

You can display data using horizontally or vertically oriented Box and Whisker Chart Excel.

  • Horizontally-Oriented Box and Whisker Chart
horizontally oriented box and whisker chart
  • Vertically- Oriented Box and Whisker Chart
vertically oriented box and whisker chart

How to create a Box and Whisker Chart should never be a stressful affair. Keep reading to discover more.

Advantages of a Box and Whisker Chart Excel

Check out the advantages of the chart below:

  • A Box and Whisker Graph can help you to visualize large datasets.
  • You can easily detect the symmetry of the data at a glance by using the chart.
  • Unlike other data visualization techniques, the Box Plot displays outliers.

Excel is one of the go-to data visualization tools for many readers.

However, the data visualization 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 advanced charts by installing third-party apps, such as ChartExpo.

How to create a Box and Whisker chart in Excel should never consume your valuable time. Keep reading to learn more.

What is ChartExpo?

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

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

The cutting-edge application produces simple, ready-made, and visually stunning charts with just a few clicks.

Yes, ChartExpo generates ready-made Box and Whisker Chart Excel that’s amazingly easy to interpret.

In the coming section, you’ll get to see ChartExpo in action as a Box and Whisker Chart generator. You don’t want to miss this.

How to Create a Box and Whisker Chart in Excel?

Let’s learn how to create a Box and Whisker Plot with examples in Excel.

Example

Subjects Male Female
Chemistry 54 2
Physics 45 5
Mathematics 35 15
Computer 36 14
Chemistry 38 20
Physics 52 15
Mathematics 28 20
Computer 58 15
Chemistry 47 12
Physics 30 20
Mathematics 41 21
Computer 52 18
Chemistry 48 12
Physics 35 15
Mathematics 62 8
Computer 56 29
Chemistry 27 13
Physics 79 21
Mathematics 75 15
Computer 55 25

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

  • Open your Excel and paste the table above.
  • 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.
list of charts in excel
  • Once the Chart pops up, click on Box and Whisker Bar Chart icon to get started, as shown below.
search chart in excel
  • Select the sheet holding your data and click the Create Chart from Sheet button, as shown below.
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.
box and whisker 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.
  • To change the color of the Box and Whisker Charts, click the Edit
  • Once the Legend Properties window shows, click on the Color box and select your preferred color, as shown below.
edit chart color in excel
  • Click the Apply button to preserve your changes.
  • To display First Quartile, Second Quartile, Third Quartile, and Mean, toggle the buttons to the right side.
edit quartile in excel
  • Save your new changes by clicking the Apply button.
  • Check out the final chart below.
box and whisker graph in excel

When to Use Box and Whisker Chart?

Use Box and Whisker Chart Excel if you have multiple data sets from independent sources related to each other. Other practical uses of a Box and Whisker Chart Excel are highlighted below.

  • Test scores between schools or classrooms
  • Data from before and after a process change
  • Similar features on one part, such as camshaft lobes
  • Data from duplicate machines manufacturing the same products

Significant Elements in a Box and Whisker Chart

box and whisker chart element
  • Mean

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 positions. 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 derive the 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 even number of elements is 3N/4.

  • Maximum and Minimum Range

The maximum range is the highest value of a key variable in your data. On the other hand, the minimum range is the lowest value of a key data point.

FAQs:

How do you analyze a Box and Whisker Plot?

If the median is in the middle of a Box and Whisker Chart Excel, and the whiskers are about the same, then the distribution is symmetric.

Conversely, if the median is closer to the bottom of the box and the whisker is shorter, then the distribution is positively skewed to the right.

How can you use a Box and Whisker Chart practically?

Box Plots are commonly used within the business, research, and data analysis settings for data storytelling.

The visualization design is incredibly handy, especially if your goal is to display skew level, mean, outliers, and quartile insights. The chart is amazingly easy to read and interpret.

Which type of data is best displayed in a Box Plot?

A Box and Whisker Chart Excel can provide you with high-level insights regarding the shape, variability, and center (or median) of your data.

Also known as Box and Whisker Charts, Box Plots are particularly useful for displaying data skewness.

Wrap Up

A Box and Whisker Chart is a visualization design that displays data distribution in quartiles while highlighting mean and outlier attributes.

Besides, the Box and Whisker Chart Excel is straightforward to read and interpret due to its minimalist design.

Excel lacks Box and Whisker Charts. The only viable options are using other pricey data visualization tools or plotting the Chart manually.

Think beyond the spreadsheet application if you intend to display attributes, such as mean, outliers, and quartiles in your data.

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

Get a reward

chartexpo ad
119236

Related articles

next previous
Microsoft Excel7 min read

How to Create a Multi Axis Chart in Excel?

Click to learn how to plot Multi Axis Chart in Excel. Also, we’ll address the following question: what is a Multi Axis Chart?

Microsoft Excel9 min read

How to Analyze Data Using 4-Point Likert Scale?

Click to learn How to Create, Gather and Analyze Data Using the 4-Point Likert Scale? A complete guide with definition, examples, uses, and applications.

Microsoft Excel7 min read

How to Create a Tornado Chart in Excel? A Complete Guide

Click to learn how to plot a Tornado Chart in Excel using easy-to-follow steps. Also, we’ll address the following question: what is a Tornado Chart?

Microsoft Excel9 min read

How to Create a Treemap Chart in Excel?

Click to learn how to get started with Treemap in Excel in your data stories. We’ll also address the following question: what is a Treemap Chart?

Microsoft Excel8 min read

How to Use a Time Series Chart in Excel for Analysis?

Click to discover how to use a Time Series Chart in Excel for better analysis? We will also learn what is a Time Series Chart, its uses, and its components.

ChartExpo logo

Turn Data into Visual
Stories

CHARTEXPO

  • Home
  • Gallery
  • Videos
  • Pricing
  • Contact us
  • FAQs
  • Privacy policy
  • Patent
  • EULA
  • Sitemap

TOOLS

  • ChartExpo for Google Sheets
  • ChartExpo for Microsoft Excel

CATEGORIES

  • Bar Charts
  • Combo Charts
  • Comparison Charts
  • Line Graphs
  • Survey Charts

TOP CHARTS

  • Box and Whisker Plot
  • Clustered Bar Chart
  • Clustered Column Chart
  • Combo Charts
  • 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
  • Overlapping Bar Chart
  • Pareto Chart
  • Radar Chart
  • Radial Bar Chart
  • Sankey Diagram
  • Scatter Plot Chart
  • Sunburst Chart

RESOURCES

  • Blog
  • YouTube
SIGN UP FOR UPDATES

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

© 2022 ChartExpo, all rights reserved.