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.

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:

For Female data:

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

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

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

• Once ChartExpo is loaded, you will see a list of charts.

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

• Select the sheet holding your data and click the Create Chart from Sheet button, as shown below.

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

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

• Save your new changes by clicking the Apply button.
• Check out the final chart below.

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

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