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.

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.

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

• Now, press ENTER to have the minimum value.

• Use Fill Handle to AutoFill the related cells.

• Next, input the following formula to have the Quartile1 value.

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.

• AutoFill the cells connected with Quartile1.

• In the following course of this step, apply the following formula to calculate the median value.

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

• Now, press ENTER to have the median value.

• Next, AutoFill the rest cells.

• After that, use the following formula to have the Quartile3 value.

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

• To have the Quartile3 value, press ENTER.

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

• Along with these, input the following formula to have the maximum value.

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

• Hit the ENTER button.

• AutoFill the connected cells.

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.

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

• Next, apply the formula mentioned below to have the Median – Quartile1 value.

Where,
C19 = Median
C18 = Quartile1

• Similarly, input the formula mentioned below to have the Quartile3 – Median value.

Here,
C20 = Quartile3
C19 = Median

• After that, find out the Whisker at Bottom using the following formula:

Where,
C18 = Quartile1
C17 = Minimum

• Lastly, find out the Whisker at Top using the following formula:

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

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

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.

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.

• Next, apply No Outline from the Outline option.

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

Finally, we have our desired 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.

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

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.