How to Add Horizontal Box and Whisker Plot in Excel?

In this article, we will show you how to insert horizontal box and whisker plots in Excel. If you are looking for such unique tricks, you’ve come to the right place. Here, we will take you through 2 easy and convenient ways to insert horizontal box and whisker plots in Excel.

What Is Horizontal Box and Whisker Plot?

The box and whisker plot is a chart used to demonstrate the statistical analysis. It is a way of graphically displaying the localization, spread, and imbalance groups of numerical data through their quartiles in descriptive statistics. If we put the box and whiskers horizontally, this is called the horizontal box and whisker plot.
Also, a horizontal box and whisker plot is a chart that effectively illustrates how the values in the dataset are distributed. They have the benefit of taking up less space than histograms, which is useful for comparing distributions over numerous groups or information.

Five-Number Summary of Horizontal Box and Whisker Plot

A horizontal box and whisker plot, often known as a box plot, shows a data set’s five-number summary. The minimum, 1^st quartile, median, 3^rd quartile, and maximum make up the five-number summary.
A box is drawn from the first quartile to the third quartile in a box plot. At the median, a perpendicular line passes through the rectangular box. Each quartile’s whiskers lead to the minimum or maximum values.

How to Add Horizontal Box and Whisker Plot in Excel: 2 Methods

In and after version 2016, Excel added a box and whisker chart. However, it is not very versatile and lacks some of the anticipated customization features for graphs. However, by using the stacked bar or column charts, error bars, and other components, you may make your own fully functional horizontal box and whisker plot.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.

1. Insert Single Horizontal Box and Whisker Plot in Excel

In our first method, we’ll learn how to make a whisker plot and a horizontal box in Excel. Here, we have an Age List of Students of a certain institution.

Now, we’ll insert a single horizontal box and whisker plot in Excel. Let’s follow the method step-by-step.

Step 01: Calculate Statistical Terms to Insert Horizontal Box and Whisker Plot in Excel

First of all, create a table in cells in the B16:C21 range. And, give the heading, and elements as in the image below.

Then, select cell C17, write down the formula below, and press ENTER.

=MIN(D5:D14)

Here, D5 and D14 represent the age of the students.

The MIN function returns the minimum values among the selected data arrays.

After that, select cell C18, write down the following formula, and press the ENTER key.

=QUARTILE.INC(D5:D14,1)

In this case, D5 and D14 represent the age of the students.

The Quartile.INC function finds the quartile (each of four equal groups) for a conveyed set of data. Here, we used 1 as the quart argument. As a result, we got the first quartile.

At this moment, select cell C19. Then put the formula below, and hit the ENTER key.

=MEDIAN(D5:D14)

The MEDIAN function returns the median of a group of numbers. That is actually the number in the middle of a set of numbers.

Later, we’ll calculate the third quartile. For this, select cell C20 and put the following formula.

=QUARTILE.INC(D5:D14,3)

This calculates the third quartile of the dataset.

Then, press ENTER.

Lastly, we’ve to determine the Maximum value.
At first, select cell C21.
Then, paste the formula below.

=MAX(D5:D14)

The MAX function returns the largest value in a given data array.

Finally, tap ENTER.

In this instance, create another table in cells in the B23:C28 range. Also, give the heading, and elements as in the image below.

In cell C24, give the following formula and press ENTER.

=C18

That means it’s equal to the first quartile.

In cell C25, give the following formula and press ENTER.

=C19-C18

This means the Q2 Box is equal to the difference between the Median and Q1.

In cell C26, give the following formula and press ENTER.

=C20-C19

This means the Q3 Box is equal to the difference between the Q3 and Median.

In cell C27, give the following formula and press ENTER.

=C21-C20

That means the Whisker+ is equal to the difference between the Maximum and Q3.

In cell C28, give the following formula and press ENTER.

=C18-C17

That means the Whisker- is equal to the difference between the Q1 and Minimum.

Step 02: Insert Bar Chart

At this stage, we’ll insert a bar chart.
First of all, select cells in the B23:C26 range.
After that, go to the Insert tab.
Then, click on the Insert Column or Bar Chart drop-down.
Later, select the 2-D Stacked Bar from the available options.

Hence, we’ve inserted a bar chart into the sheet.

At this point, right-click anywhere on the plot area.
Then, choose the Select Data option on the context menu.

Meanwhile, the Select Data Source window opens.
Later, tap on Switch Row/Column.
Lastly, click OK.

By our previous action, the chart looks like the one below.

Step 03: Add Error Bars

Firstly, select series Q3 Box.
Then, move to the Chart Design tab.
After that, click on the Add Chart Element drop-down on the Chart Layouts group.
Later, click on the right arrow of Error Bars.
Next. from the sub-menu, select More Error Bar Options.

Instantly, the Format Error Bars task pane opens up.
Now, select Plus under Direction.
Then, choose the Cap as the End Style.
Later, select Custom under the section of Error Amount.
Lastly, click on the Specify Value button.

Suddenly, it opens the Custom Error Bars input box.
Then, input cell C27 in the Positive Error Value box.
Lastly, click OK.

Similarly, select the series Bottom Box.
Then, move to the Chart Design tab.
After that, click on the Add Chart Element drop-down on the Chart Layouts group.
Later, click on the right arrow of Error Bars.
Next. from the sub-menu, select More Error Bar Options.

Instantly, the Format Error Bars task pane opens.
Now, select Minus under Direction.
Then, choose the Cap as the End Style.
Later, select Custom under the section of Error Amount.
Lastly, click on the Specify Value button.

Immediately, it opens the Custom Error Bars input box.
Then, input cell C28 in the Negative Error Value box.
Lastly, click OK.

At this moment, the chart looks like the one below.

Step 04: Format the Horizontal Box and Whisker Plot

Firstly, double-click on the Bottom Box series.

Instantly, the Format Data Point task pane opens up.
Then, click on the Fill & Line icon.
After that, select No Fill under the Fill section.

Now, the horizontal box and whisker plot look like the one below.

At this moment, double-click on the series Q2 Box.
Subsequently, the Format Data Point task pane opens.
Then, click on the Fill & Line icon.
After that, select Solid Fill under the Fill section.
Later, click on the Fill Color drop-down and select Gold, Accent 4 from the available colors.

Also, do the same to series Q3 Box.
Presently, the chart looks like the below one.

Again, open the Format Data Point task pane for the Q3 Box series.
Then, click on the Fill & Line icon.
After that, select Solid Line under the Border section.
Later, click on the Outline Color drop-down and select Black, Text 1 from the available colors.
Lastly, set the Width as 1.5 pt.

Also, do the same for another series.
Finally, the horizontal box and whisker plot are ready to interpret.

2. Insert Multiple Horizontal Box and Whisker Plots in Excel

In this method, we’ll know how to insert a multiple horizontal box and whisker plot in Excel. Here, we have the Scores of Mid-Term Tests of some students.

Now, we’ll insert a multiple horizontal box and whisker plot in Excel. Follow us carefully.

📌 Steps

First, find out the statistical terms just like we did in Step 01.

After that, calculate the chart elements as we did in Method 1.

At this moment, select cells in the B23:E26 range.
After that, go to the Insert tab.
Then, click on the Insert Column or Bar Chart drop-down.
Later, select the 2-D Stacked Bar from the available options.

Thus, we inserted the chart into our worksheet.
In this instance, double-click on the Vertical Axis Title.

Immediately, the Format Axis task pane appears.
Then, click on the Axis Option icon.
After that, check the box of Categories in reverse order.
Finally, select the Horizontal axis crosses as At maximum category.

Now, the axis titles are in the right sequence.

Then, add the whiskers just like we did it before.

Also, apply the No Fill in the Bottom Box series.

After all types of formatting (shown in Step 04), the horizontal box and whisker plot look as in the image below.

How to Insert Vertical Box and Whisker Plot in Excel?

Also, we can insert a vertical box and whisker plot in Excel. But, it is available as a default plot type in Excel. So, let’s follow along to be able to replicate that.

📌 Steps

Initially, select cells in the C4:E14 range.
After that, proceed to the Insert tab.
Then, click on the Insert Statistic Chart drop-down.
Later, select Box and Whisker from the available options.

Hence, we can see the vertical box and whisker chart appeared on our worksheet.

Again, select the chart.
Then, move to the Chart Design tab.
After that, click on the Add Chart Element drop-down on the Chart Layouts group.
Later, click on the right arrow of Legend.
Next. from the sub-menu, select Right.

Thus, the legends are also available on the chart.

Finally, give a suitable Chart Title to our vertical box and whisker plot.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Horizonal Box and Whisker Plot.xlsx

Conclusion

This article provides easy and brief solutions to insert horizontal box and whisker plots in Excel. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

<< Go Back to Box and Whisker Plot in Excel | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!