How To Create A Box And Whisker Plot On Excel

Box Plot in Excel

Excel Box Plot (Table of Contents)

What is a Box Plot?
How to Create Box Plot in Excel?

Introduction to Box Plot in Excel

If you are a statistic geek, you often might come up with a situation where you need to represent all the 5 important descriptive statistics which can be helpful in getting an idea about the spread of the data (namely minimum value, first quartile, median, third quartile and maximum) in a single pictorial representation or in a single chart which is called as Box and Whisker Plot. For example, the first quartile, median, and third quartile will be represented under a box, and whiskers give you minimum and maximum values for the given set of data. Box and Whisker Plot is an added graph option in Excel 2016 and above. However, previous versions of Excel do not have it built-in. In this article, we are about to see how a Box-Whisker plot can be formatted under Excel 2016.

What is a Box Plot?

In statistics, a five-number summary of Minimum Value, First Quartile, Median, Last Quartile, and Maximum value is something we want to know in order to have a better idea about the spread of the data given. This five value summary is visually plotted to make the spread of data more visible to the users. The graph on which statistician plot these values is called a Box and Whisker plot. The box consists of First Quartile, Median and Third Quartile values, whereas the Whiskers are for Minimum and Maximum values on both sides of the box respectively. This Chart was invented by John Tuckey in the 1970s and has recently been included in all the Excel versions of 2016 and above.

We will see how a box plot can be configured under Excel.

How to Create Box Plot in Excel?

Box Plot in Excel is very simple and easy. Let's understand how to create the Box Plot in Excel with some examples.

You can download this Box Plot Excel Template here – Box Plot Excel Template

Example #1 – Box Plot in Excel

Suppose we have data as shown below, which specifies the number of units we sold of a product month-wise for years 2017, 2018 and 2019, respectively.

Box plot in Excel 1-1

Step 1: Select the data and navigate to the Insert option in the Excel ribbon. You will have several graphical options under theCharts section.

Box plot in Excel 1-2

Step 2: Select the Box and Whisker option, which specifies the Box and Whisker plot.

Box plot in Excel 1-3

Right-click on the chart, select the Format Data Series option, then select the Show inner points option. You can see a Box and Whisker plot as shown below.

Chart Title - Box Plot in Excel

Example #2 – Box and Whisker Plot in Excel

In this example, we will plot the Box and Whisker plot using the five-number summary that we have discussed earlier.

Step 1: Compute the Minimum Maximum and Quarter values. MIN function allows you to give your Minimum value; MEDIAN will provide you the median Quarter.INC allows us to compute the quarter values, and MAX allows us to calculate the maximum value for the given data. See the screenshot below for five-number summary statistics.

Box and Whisker Plot in Excel 2-1

Step 2: Now, since we are about to use the stack chart and modify it into a box and whisker plot, we need each statistic as a difference from its subsequent statistic. Therefore, we use the differences between Q1 – Minimum and Maximum – Q3 as Whiskers. Q1, Q2-Q1, Q3-Q2 (Interquartile ranges) as Box. And combine together, it will form a Box-Whisker Plot.

Box and Whisker Plot in Excel 2-2

Step 3: Now, we are about to add the boxes as the first part of this plot. Select the data from B24:D26 for boxes (remember Q1 – Minimum and Maximum – Q3 are for the Whiskers?)

Box and Whisker Plot in Excel 2-3

Step 4: Go to the Insert tab on the excel ribbon and navigate to Recommended Charts under the Charts section.

Box and Whisker Plot in Excel 2-4

Step 5: Inside Insert Chart window > All Charts > navigate to Column Charts and select the second option, which specifies the Stack Column Chart and click OK.

Box and Whisker Plot in Excel 2-5

This is how it looks.

Box and Whisker Chart

Step 6: Now, we need to add whiskers. I will start with the lower whisker first. Select the stack chart portion, which represents the Q1 (Blue bar) > Click on Plus Sign > Select Error Bars > Navigate to More Options… dropdown under Error Bars.

Error Bars

Step 7: As soon as you click on More Options… Format Error Bars menu will appear > Error Bar Options > Direction : Minus Radio Button (since we are adding the lower whisker) > End Style : Cap radio button > Error Amount : Custom > Select Specify Value.

Format Data Bars

It opens up a window within which specifies the lower whisker values (Q1 – Minimum B23:D23) under Negative Error Value and Click OK.

Custom Error Bars

Step 8: Do the same for the upper Whiskers. Select the gray bar (Q3-Median bar); instead of selecting Direction as Minus, use Plus and add the values of Maximum – Q3, i.e. B27:D27, under the Positive Error Values box.

Format Error Bars

It opens up a window within which specifies the lower whisker values (Q3 – Maximum B27:D27) under Positive Error Value and Click OK.

Custom Error Bars 1

The Graph now should look like the screenshot below:

Box and Whisker plot

Step 9: Remove the bars associated with Q1 – Minimum. Select the Bars > Format Data Series > Fill & Line > No Fill. This will remove the lower part as it is not useful in the Box-Whisker plot and just added initially because we want to plot the stack bar chart as a first step.

Format Data Series 1

Step 10: Select the Orange bar (Median – Q1) > Format Data Series > Fill & Line > No fill under Fill section > Solid line under Border section > Color > Black. This will remove the colors from the bars and represent them just as outline boxes.

Format data Series -box plot 1

Box and Whisker plot 1

Follow the same procedure for the gray bar (Maximum – Q3) to remove the color from it and represent it as a solid line bar. The plot should look like the one in the screenshot below:

gray bar

This is how we can create Box-Whisker Plot under any version of Excel. If you have Excel 2016 and above, you anyway have the direct chart option for the Box-Whisker plot. Let's end this article with some points to be remembered.

Things to Remember

Box plot gives an idea about the spread/distribution of the dataset with the help of a five-number statistical summary which consists of Minimum, First Quarter, Median/Second Quarter, Third Quarter, Maximum.
Whiskers are nothing but the boundaries, which are distances of minimum and maximum from first and third quarters, respectively.
Whiskers are useful to detect outliers. Anything point lying outside the whiskers is considered an outlier.