How To Create A Boxplot In Excel 2007

Contextures

How to Build an Excel Box Plot Chart

How to make a Box Plot, or box and whisker chart, to show the distribution of the numbers in your data

Introduction

With a Box Plot (box and whisker chart), you can see the distribution of numbers in data. What are the highest and lowest numbers? What was the median number? What was the range of numbers on either side of the median?.

Paste Special

Excel 2013 doesn't have a Box Plot chart type, but you can create your own version, by adding calculations to the worksheet, and using a Stacked Column chart type, with error bars.

Watch the short video to see the steps. There are step-by-step instructions below the video

Build Your Own Box Plot

To see the steps for creating a simple box plot chart, watch this short video. The written instructions are below the video.

Note: To see the video transcript, go to the Box Plot Chart Video page.

Set Up the Box Plot Data

To create your own box plot chart, the first step is to set up your data. In the screen shot below:

Column A has month labels, and those will not be used in the chart
Columns B and C have sales data, with headings in row 3

Enter the Box Plot Chart Formulas

The next step is to enter the formulas that will be used in the chart. Simple formulas calculate the Min, Quartile 1, Median, Quartile 3 and Max values.

Enter the following formulas, then copy them across to column G.

F4: =MIN(B4:B11)
F5: =QUARTILE(B4:B11,1)
F6: =MEDIAN(B4:B11)
F7: =QUARTILE(B4:B11,3)
F8: =MAX(B4:B11)

formulas for box plot chart

Box Heights

From those calculations, you can calculate the height of each box, and the length of the whiskers. The diagram below shows where each measure appears in the box plot.

formulas for box plot chart

To calculate the heights/lengths, subtract the bottom measure from the top one. For example, the upper box height is Quartile 3 minus the Median.. The formulas are listed below the screenshot.

Enter the following formulas, then copy them across to column G.

F10: =F5
F11: =F6-F5
F12: =F7-F6
F14: =F8-F7
F15: =F5-F4

Create the Box Plot Chart

To create the Box Plot chart:

Select cells E3:G3 -- the heading cells -- then press Ctrl and select E10:G12.
On the Excel Ribbon, click the Insert tab, and click Column Chart, then click Stacked Column
If necessary, click the Switch Row/Column command on the Ribbon's Design tab, to get the box series stacked.
Click on the Base series to select it, and format it with no fill and no border, so it isn't visible in the chart.
Remove the Legend

top error bar for box plot chart

To add the Top Whisker:

Click the top box, and on the Ribbon's Design tab, click Add Chart Element
Click Error Bars, and click More Error Bar Options
In the Error Bars Options, under Direction, click Plus
In the Error Amount Section, click Custom, and click Specify Value
Delete the contents of the Positive Error Value box, and select the Whisker Top values on the worksheet
Click OK to close the Custom Error Bars window.

To add the Bottom Whisker:

Click the hidden bottom box, and on the Ribbon's Design tab, click Add Chart Element
Click Error Bars, and click More Error Bar Options
In the Error Bars Options, under Direction, click Minus
In the Error Amount Section, click Custom, and click Specify Value
Delete the contents of the Negative Error Value box, and select the Whisker Bottom values on the worksheet
Click OK to close the Custom Error Bars window

To finish the formatting, change the fill color for the boxes to light gray, and give them a darker gray border.

Add an Average Marker to Box Plot

After you create a box plot chart, you can add an average marker to each box. To add the average, follow these steps in this video. There are step-by-step instructions below the video

To add an average marker to a Box Plot Chart:

Add a blank row in the box plot's data range.
Type the label, "Average" in the first column
In the remaining columns, enter an AVERAGE formula, to calculate the average for the data ranges.

bottom error bar for box plot chart

Copy the cells with the Average label, and the formulas
Click on the chart, and on the Ribbon's Home tab, click the arrow on the Paste button
Click Paste Special.
In the Paste Special dialog box, choose "New Series", Values in Rows, and "Series Names in First Column", and click OK
If the Average series appears as a Stacked Column, right-click one of the columns, and click Change Series Chart Type
Combo chart will be selected in the Chart Type dialog box. In the list of series, find the Average, and change its chart type to Line With Markers, and click OK
Click on the line, and on the Ribbon's Format tab, click Format Selection
In the Fill & Line section, under Line, select No Line
Close the Format Data Series window, and Average marker appears on the chart.

Another Box Plot Video

This box plot example is based on wait times at airport security checkpoints. There are written notes on my Contextures Blog: Airport Security Times in Excel Box Plot

Get the Sample Chart

You can download the sample file that was used in this video -- Simple Box Plot Chart. The zipped file is in xlsx format, and does not contain macros.