Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Rotate Box and Whisker Plot in Excel

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 to rotate 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 to rotate box and whisker plot in Excel.


Download Practice Workbook


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.


Step by Step Procedures to Rotate Box and Whisker Plot in Excel

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 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 it is mentioned before that 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.

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: [Fixed!] Box and Whisker Plot Not Showing 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 out the Whisker at Bottom using the following formula:
=C18-C17

Where,
C18 = Quartile1
C17 = Minimum

Find Differences Between Particulars

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

Here,
C21 = Maximum
C20 = Quartile3

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

Read More: How to Create Box and Whisker Plot in Excel with Multiple Series


3. Create a Box and Whisker Plot

In the above two sections, I have tried to find out the related particulars to create 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

Read More: How to Add Horizontal Box and Whisker Plot in Excel


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


Conclusion

That’s all for today. In the end, I like to explain in step by step process to rotate 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. You can visit our Exceldemy site for more details on Excel.

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo