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:
Before diving right into the how-to guide, let’s address the following question:
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:
Use the Box and Whisker Chart in the following scenarios:
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:
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:
To execute the five-number summary principle, enable quartiles to get descriptive insights.
The Box and Whisker Chart has three quartiles, namely:
Let’s check out other insights displayed by the visualization design.
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.
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.
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.
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 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.
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).
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.
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.
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.
To add additional details, such as the title in your chart, follow the steps below:
As we said, the Box and Whisker Chart displays the distribution, outliers, and median attributes of your data.
How to read a Box Plot should never be a stressful task.
Check out the benefits of a Box and Whisker Chart in data analysis
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.
Unlike other visualization designs, the chart provides in-depth details into your data, such as median, mean, quartile, and standard deviation from the mean.
A Box and Whisker Chart is best-suited for displaying a summary of one or more data sets.
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.
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.
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.
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.
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.
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.