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

Box and whisker plot in Excel demonstrates the assigned dataset’s distribution of quartiles, median, and outliers. This article will show how to create a box and whisker plot in Excel with multiple series. In this article, you can learn about the box and whisker plot components and their benefits. I hope you find this article interesting and gain lots of knowledge.


Download Practice Workbook

Download the practice workbook below.


What Is Box and Whisker Plot?

A box and whisker plot is used to analyze a given dataset’s median, quartiles, and max and min value. A box and whisker plot has two components: box and whisker. The rectangular box represents the Quartiles and Median of the dataset. The lower line represents the first quartile whereas the upper line denotes the third quartile. The middle line demonstrates the median of the given dataset. The vertical lines extending from the box are known as a whisker. The lower and upper extreme points represent the Min and Max values of the dataset.

Box and Whisker Plot in Excel with Multiple Series

The most important benefit of having a box and whisker plot is that it represents the mean, median, max, min, and quartile in a single plot. By using this plot, you can say whether the data is skewed or not if the median line doesn’t divide the box into equal space.


2 Easy Methods to Create Box and Whisper Plot in Excel with Multiple Series

To create a box and whisker plot in Excel with multiple series, we have found two different methods through which you can have a clear idea of how to do it for multiple series. In this article, we use the box and whisper plot and also a stacked column chart. Both cases are fairly easy to use and can give you a proper result.


1. Utilizing Box and Whisper Plot

To create a box and whisker plot in Excel with multiple series, you need to set up a dataset for this plot, then insert the box and whisper plot and finally, modify it to have better representations.

Steps

  • First, prepare a dataset containing multiple entries for a single record.

  • Then, select the range of cells B4 to E13.

  • After that, go to the Insert tab in the ribbon.
  • Then, select Insert Statistic Chart drop-down option from the Charts group.

  • Select the Box and Whisker chart.

Box and Whisker Plot in Excel with Multiple Series

  • As a result, you will get the following chart. See the screenshot.

Box and Whisker Plot in Excel with Multiple Series

  • Then, double-click on the box and whisker icon.
  • It will open up the Format Data Series.

Box and Whisker Plot in Excel with Multiple Series

  • In the Format Data Series dialog box, you can get several options.
  • Gap Width: Controls gap between the categories.
  • Show Inner Points: Displays the points that are situated between the lower whisker line and upper whisker line.
  • Show Outlier Points: Displays the outliers points that lie either below the lower whisker line or above the upper whisker line
  • Show Mean Markers: Displays the mean marker of the selected series.
  • Show Mean line: Displays the line connecting the means of the boxes in the selected series.
  • Inclusive Median: The median is included in the calculation if N (the number of values in the data) is odd.
  • Exclusive Median: The median is excluded in the calculation if N (the number of values in the data) is odd.

2. Using Stacked Column Chart

In this method, we will use the stacked column chart to create a box and whisker plot in Excel with multiple series. First, you need to calculate the min, max, median, quartile 1, and quartile 3 by using the MIN, MAX, MEDIAN, and QUARTILE functions. Then, use the stacked column chart to plot it. To understand the method properly, follow the steps.

Steps 1: Prepare Dataset

First, prepare the data containing multiple entries for a single record. Using this dataset, we will create further data for the box and whisker plot.

Step 2: Calculate Box and Whisker Plot Components

Then, we need to calculate the min, max, median, quartile 1, and quartile. In this step, we will some new columns where we will put the required component values.

  • First, select cell I5.
  • Write down the following formula.
=MIN(C5:C13)

Box and Whisker Plot in Excel with Multiple Series

  • Then, press Enter to apply the formula.

  • After that, drag the Fill Handle icon up to cell K5.

Box and Whisker Plot in Excel with Multiple Series

  • Select cell I6.
  • Write down the following formula.
=QUARTILE(C5:C13,1)

Box and Whisker Plot in Excel with Multiple Series

  • Then, press Enter to apply the formula.

  • After that, drag the Fill Handle icon up to cell K6.

Box and Whisker Plot in Excel with Multiple Series

  • Select cell I7.
  • Write down the following formula.
=MEDIAN(C5:C13)

  • Then, press Enter to apply the formula.

  • After that, drag the Fill Handle icon up to cell K7.

Box and Whisker Plot in Excel with Multiple Series

  • Select cell I8.
  • Write down the following formula.
=QUARTILE(C5:C13,3)

  • Then, press Enter to apply the formula.

  • After that, drag the Fill Handle icon up to cell K8.

Box and Whisker Plot in Excel with Multiple Series

  • Select cell I9.
  • Write down the following formula.
=MAX(C5:C13)

Box and Whisker Plot in Excel with Multiple Series

  • Then, press Enter to apply the formula.

  • After that, drag the Fill Handle icon up to cell K9.

Box and Whisker Plot in Excel with Multiple Series

Step 3: Create Dataset for Stacked Column Chart

Then, we want to create a dataset for a stacked column chart which is acted as the box. Follow the steps

  • Select cell I12.
  • Write down the following formula.
=I6-0

  • Then, Press Enter to apply the formula.
  • After that, drag the Fill Handle icon up to cell K12.

Box and Whisker Plot in Excel with Multiple Series

  • Select cell I13.
  • Write down the following formula.
=I7-I6

  • Then, Press Enter to apply the formula.
  • After that, drag the Fill Handle icon up to cell K13.

Box and Whisker Plot in Excel with Multiple Series

  • Select cell I14.
  • Write down the following formula.
=I8-I7

  • Then, Press Enter to apply the formula.
  • After that, drag the Fill Handle icon up to cell K14.

Box and Whisker Plot in Excel with Multiple Series

Step 4: Create Dataset for Whisker

Then, we need to create a dataset for creating a whisker. Here, we utilize error bars to create a whisker. Follow the steps.

  • To do this, select cell I17.
  • Write down the following formula.
=I6-I5

  • Then, press Enter to apply the formula.
  • After that, drag the Fill Handle icon up to cell K17.

Box and Whisker Plot in Excel with Multiple Series

  • select cell I18.
  • Write down the following formula.
=I9-I8

  • Then, press Enter to apply the formula.
  • After that, drag the Fill Handle icon up to cell K18.

Box and Whisker Plot in Excel with Multiple Series

Step 5: Insert Stacked Column Chart

We create the stacked column chart, we need to utilize our prepared dataset.

  • To create a stacked column chart, select the range of cells I11 to K14.

  • Then, go to the Insert tab in the ribbon.
  • From the Charts group, select Recommended Charts.

  • After that, select the Stacked Column chart option.
  • Finally, click on OK.

  • It will give us the following result. See the screenshot.

Box and Whisker Plot in Excel with Multiple Series

  • We need to remove the blue box from the chart.
  • First, double-click on the blue box.
  • It will open the Format Data Series.
  • Then, select the Fill & Line tab at the top.

  • After that, select No fill from the Fill section.
  • Then, select the No line from the Border section.

  • It will remove the blue box from the chart. See the screenshot.

Box and Whisker Plot in Excel with Multiple Series

Step 6: Create Box and Whisker Plot

Then, we need to create the whisker using the error bar. Here, we utilize the prepared dataset for the whisker plot.

  • First, select the lower box, it will open up the Chart Design tab.

  • Then, go to the Chart Design tab in the ribbon.
  • Select Add Chart Element from the Chart Layouts group.

  • Then, select the Error Bars option.
  • From there, select More Error Bars options.

  • Set Vertical Error Bars direction as Minus.
  • Then, select Custom from the Error Amount.
  • After that, select Specify Value.

  • It will open up the Custom Error Bars dialog box.
  • Select the negative error value range.
  • Finally, click on OK.

Box and Whisker Plot in Excel with Multiple Series

  • It will create an error bar like a whisker.

Box and Whisker Plot in Excel with Multiple Series

  • To create a whisker in the positive direction, select the upper box.

  • Then, again go to the Chart Design tab.
  • From there, select the Error Bars option.
  • Set Vertical Error Bars direction as Plus.
  • Then, select Custom from the Error Amount section.
  • After that, select Specify Value.

  • It will open up the Custom Error Bars dialog box.
  • Select the positive error value range.
  • Finally, click on OK.

  • As a result, we get our desired chart which is similar to a box and whisker plot with multiple series. See the screenshot.

Box and Whisker Plot in Excel with Multiple Series


Conclusion

We have shown two different methods to create a box and whisker plot in Excel with multiple series. In this article, we utilize a box and whisker plot and a stacked column chart. Both of these methods provide a proper solution. We covered all possible areas regarding the box and whisker plot in Excel with multiple series. If you have any further questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo