How to Add Horizontal Box and Whisker Plot in Excel

Need to learn how to insert horizontal box and whisker plot 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 for inserting horizontal box and whisker plots in Excel.


Download Practice Workbook

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


What Is Horizontal Box and Whisker Plot?

Box and whisker plot is a chart used to demonstrate the statistical analysis. It is a way for graphically displaying the localization, spread, and imbalance groups of numerical data through their quartiles in descriptive statistics. And 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, 1st quartile, median, 3rd 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.


2 Methods to Insert Horizontal Box and Whisker Plot in Excel

In and after version 2016, Excel added a box and whisker chart. But 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 know how to insert a single horizontal box and whisker plot in Excel. Here, we have an Age List of Students of a certain institution.

Horizontal Box and Whisker Plot Excel

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.

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

That means it’s equal to the first quartile.

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel


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 Insert Column or Bar Chart drop-down.
  • Later, select 2-D Stacked Bar from the available options.

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel


Step 03: Add Error Bars

  • Firstly, select series Q3 Box.
  • Then, move to the Chart Design tab.
  • After that, click on 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.

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

  • Similarly, select the series Bottom Box.
  • Then, move to the Chart Design tab.
  • After that, click on 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.

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel


Step 04: Format Horizontal Box and Whisker Plot

  • Firstly, double-click on the Bottom Box series.

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel

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

Horizontal Box and Whisker Plot Excel


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.

Dataset

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

📌 Steps

  • At first, find out the statistical terms just like we did in Step 01.

Inserting Multiple Dataset Statistical Chart

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

Inserting Multiple Dataset Statistical Chart

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

Inserting Multiple Dataset Statistical Chart

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

Inserting Multiple Dataset Statistical Chart

  • 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 Horizontal axis crosses as At maximum category.

Inserting Multiple Dataset Statistical Chart

  • Now, the axis titles are in the right sequence.

Inserting Multiple Dataset Statistical Chart

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

Inserting Multiple Dataset Statistical Chart

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

Inserting Multiple Dataset Statistical Chart

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

Inserting Multiple Dataset Statistical Chart


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.

Inserting Vertical Statistical Chart

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

Inserting Vertical Statistical Chart

  • Again, select the chart.
  • Then, move to the Chart Design tab.
  • After that, click on 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.

Inserting Vertical Statistical Chart

  • Thus, the legends are also available on the chart.

Inserting Vertical Statistical Chart

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

Inserting Vertical Statistical Chart


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. Please visit our website Exceldemy to explore more.

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo