How to Rotate Box and Whisker Plot in Excel?

Get FREE Advanced Excel Exercises with Solutions!

To represent the variation in a set of data, we often try to apply a graphical display. A Box and Whisker Plot is just another kind of visual representation of data through graphs. In this article, I am going to explain in step-by-step process of rotating box and whisker plot in Excel. I will try to demonstrate it as simply as possible. I hope it will be very helpful for those who are having trouble rotating box and whisker plots in Excel.


What Is a Box and Whisker Plot?

In Excel, a box and whisker plot is a graphical depiction of a dataset’s numerical values. It displays the minimum, maximum, first quartile, second quartile (median), and third quartile as five numbers that represent the data as its whole. The median among them is a measure of the middle, whereas the others are measurements of dispersion. Therefore, a box plot displays the dataset’s center and the degree of dispersion from it.


How to Rotate Box and Whisker Plot in Excel: 4 Easy Methods

In order to rotate the box and whisker plot, we have to create a box and whisker plot first and we can rotate that afterward. So, we can divide the whole process into 4 different sections. They are:

  1. Calculate the Related Particulars to Create Box and Whisker Plot
  2. Find out the Differences between those Particulars
  3. Create a Box and Whisker Plot
  4. Rotate Box and Whisker Plot

For the clarification of the whole process, I have considered the following dataset. In the dataset, I have decorated the Annual Result of a school in the Name, Physics, Chemistry, and Mathematics columns.

 How to Rotate Box and Whisker Plot in Excel


1. Calculate Related Particulars for a Box and Whisker Plot

As mentioned before a box and whisker plot shows a data set’s five-number summary. The minimum, 1st quartile, median, 3rd quartile, and maximum make up the five-number summary. So, this is the first step to calculate all of these particulars.

Steps:

  • As the first parameter, calculate the minimum value of the available values. Here, I have calculated the minimum mark obtained in Physics using the following formula.
=MIN(C5:C14)

Here, the MIN function calculates the minimum value among the cells C5:C14.

Related Particulars Calculation to Create a Box and Whisker Plot

  • Now, press ENTER to have the minimum value.

  • Use Fill Handle to AutoFill the related cells.

Related Particulars Calculation to Create a Box and Whisker Plot

  • Next, input the following formula to have the Quartile1 value.
=QUARTILE.EXC(C5:C14,1)

The QUARTILE.EXC function returns the quartile of the above dataset based on the first 25% values.

  • Hit the ENTER button to have the Quartile1 value.

Related Particulars Calculation to Create a Box and Whisker Plot

  • AutoFill the cells connected with Quartile1.

  • In the following course of this step, apply the following formula to calculate the median value.
=MEDIAN(C5:C14)

Here, the MEDIAN function returns the central value of the dataset.

  • Now, press ENTER to have the median value.

Related Particulars Calculation to Create a Box and Whisker Plot

  • Next, AutoFill the rest cells.

  • After that, use the following formula to have the Quartile3 value.
=QUARTILE.EXC(C5:C14,3)

The QUARTILE.EXC function returns the quartile of the above dataset based on the last 25% values.

  • To have the Quartile3 value, press ENTER.

Related Particulars Calculation to Create a Box and Whisker Plot

  • For the related cells, use Fill Handle to AutoFill.

  • Along with these, input the following formula to have the maximum value.
=MAX(C5:C14)

The MAX function here calculates the maximum value among the cells C5:C14.

  • Hit the ENTER button.

Related Particulars Calculation to Create a Box and Whisker Plot

  • AutoFill the connected cells.

Related Particulars Calculation to Create a Box and Whisker Plot

Read More: How to Make a Modified Box Plot in Excel


2. Find Differences Between Those Particulars

Differences between those particulars play a vital role in creating box and whisker plot, actually, these are the main particulars with which we are gonna create box and whisker plot.

Steps:

  • Input the following formula to have the value Up to Quartile1.
=C18

In this case, the value entered in cell C18 is equal to Up to Quartile1.

Find Differences Between Particulars

  • Next, apply the formula mentioned below to have the Median – Quartile1 value.
=C19-C18

Where,
C19 = Median
C18 = Quartile1

  • Similarly, input the formula mentioned below to have the Quartile3 – Median value.
=C20-C19

Here,
C20 = Quartile3
C19 = Median

  • After that, find the Whisker at Bottom using the following formula:
=C18-C17

Where,
C18 = Quartile1
C17 = Minimum

Find Differences Between Particulars

  • Lastly, find the Whisker at Top using the following formula:
=C21-C20

Here,
C21 = Maximum
C20 = Quartile3

  • Now, use Fill Handle to AutoFill the connected cells.


3. Create a Box and Whisker Plot

In the above two sections, I have tried to find out the related particulars to make box and whisker plot. We are going to create a box and whisker plot in this section.

Steps:

  • As a first step to create a box and whisker plot, select the cells in Up to Quartile1, Median – Quartile1, Quartile3 – Median along with the titles.
  • Next, go to the Insert tab.
  • Then, click on Insert Column or Bar Chart from the ribbon.
  • From the available options in the 2-D Column section, choose Stacked Column.

Create a Box and Whisker Plot

  • Afterward, select the lower quartile for all the bars.
  • Click on Add Chart Element from the ribbon.
  • Next, go to Error Bars, and from there, pick More Error Bars Options.

On the right side, a Format Error Bars wizard will appear.

  • Choose Both as Direction.
  • From the End Style section, pick Cap.
  • To enter the Error Amount according to the dataset, select Custom and then, click on Specify Value.

Create a Box and Whisker Plot

A Custom Error Bars wizard will come forward.

  • As we are dealing with Quartile1, select all the cells in Whisker at Bottom in the Negative Error Value option.

  • Along with this, select the top quartile for all the bars.
  • Then, click on Add Chart Element from the ribbon.
  • Next, go to Error Bars, and from there, pick More Error Bars Options.

A Format Error Bars wizard will appear on the right side.

  • Now, choose Both as Direction.
  • From the End Style section, pick Cap.
  • Select Custom and then, click on Specify Value to enter the Error Amount according to the dataset.

Create a Box and Whisker Plot

A Custom Error Bars wizard will appear.

  • As we are dealing with Quartile3, select all the cells in Whisker at Top in the Positive Error Value

  • Now, select all the lower quartile boxes.
  • Apply No Fill from the Fill option.

Create a Box and Whisker Plot

  • Next, apply No Outline from the Outline option.

Create a Box and Whisker Plot

  • Then, select all the horizontal lines and press the Delete button to have them removed.

Finally, we have our desired box and whisker plot.

Create a Box and Whisker Plot


4. Rotate Box and Whisker Plot

We have created our box and whisker plot in the above section. Now, we are going to rotate the box and whisker plot.

Steps:

  • Firstly, select the created box and whisker plot.
  • Next, go to the Insert tab.
  • Then, click on Insert Column or Bar Chart from the ribbon.
  • From the available options in the 2-D Bar section, choose Stacked Bar.

Rotate Box and Whisker Plot

Thus, we can rotate the box and whisker plot with just a single command.

Rotate Box and Whisker Plot


Download Practice Workbook


Conclusion

That’s all for today. In the end, I like to explain the step-by-step process of rotating the box and whisker plot in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo