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.

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

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.

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

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

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.

• Select ChartExpo and click the Insert button to get started with ChartExpo.

• Once ChartExpo is loaded, you will see a list of charts. Paste the table above into your Excel.

• Once the Chart pops up, click on Box and Whisker Column Chart icon to get started, as shown below.

• Select the sheet holding your data and click the Create Chart From Selection button.

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.

• Once the Chart Header Top Properties window pops up, click the Line 1 button and fill in the title.

• Save your new changes by clicking the Apply button.

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

• Check out the final chart below.

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.