exceltoxl.com

Hi there,

 

In this post, we will look into a very challenging and interesting chart. The chart name is Box and Whisker Plot Chart. This chart will give the better understanding of the data in terms of visual presentation. Here I have a sales data for last 2 years and I want to showcase the maximum, minimum, average sales between two years.

Before I showcase the raw data, the required work around with raw data are: 

1. Find Min, Max, Median, Quartile1 and Quartile2 values. (using functions)

2. Find the values for Height of the Box and (using formulas)

3. Find the values for Whiskers (using calculations)

Now, the data looks like this.

...

From this data, we need to find the above said visuals through Box Plot Chart. To do that, we need find the Minimum value, Quartile 1, Median, Quartile 3, Maximum value. Here is the formula to extract these figures.

And, here is the values populated after using above functions.

 

Once done, we need to find the height of Box-Plots through max and min values so that we can showcase visually. And, here you go:

And, here is the values populated for the above formulas.

And, here is the output populated for the above formulas.

Now, its turn to prepare a Box and Whisker Plot Chart. To do that, select Years (blank cell, year 1 and year 2) and the quartiles data using CTRL button. Now, choose 2D stacked column chart. 

You might be seeing year in the legend part. Need to switch rows/column from the DESIGN tab. Make sure you select the chart so that you can see DESIGN tab (flying ribbon).

Now, select the max value (blue colour) and follows these instructions.

1. Click on DESIGN tab (flying ribbon)

2. Click on Add Chart Elements dropdown under Chart Layouts Group

3. Select Error Bars and

4. Click on last option "More Error Bars Options"

5. Now, under Vertical Error Bar select option "Plus"

6. Under "Error Amount", Select "Custom" --> Specify Value

7. Now, Select the Upper Hinge Values in place of Positive Error Values.

Now, repeat all the above steps by selecting min value till 4. And, 

5. Now, under Vertical Error Bar selection option "minus"

6. Under "Error Amount". Select "Custom" --> Specify Value

7. Now, Select the Lower Hinge Values in place of Negative Error Values.

That's it. Here is how output looks like.

To explore more on this chart, you can click download working file here.

DID YOU LIKE BOX-WHISKER-CHART?

Please do share your views or opinions or success stories on this chart using below comments section. If you like this post, please do share it on your social networking sites by clicking on the icons placed around the blog.

If you want to receive such updates to your inbox, REGISTER here for free.

SOME IMPORTANT LINKS:

New Charts Added in Excel 2016

Difference Between Columns Vs Bar Chart

Customize Markers in Charts

Awesome Charts for Sale!!!

Thanks,