How to Build an Excel Box Plot Chart

How to make a Box Plot, or box and whisker chart, in Microsoft Excel, to show the distribution of the numbers in your data set. Watch the video to see the steps for making a simple box plot chart, and there are written steps too, and a sample file to download. Another video show how to add an average line in the chart, and a third video shows a different box plot chart example.

Author: Debra Dalgleish

Box Plot Chart Overview

Most people are familiar with a line chart, where you show data over a period of time.

First, we'll take a quick look at a line chart, then we'll see how a box plot chart would show the data in a different way.

For both types of chart, the same data will be used:

• sales for three stores in a retail chain,
• over the 12 months of a year

Line Chart - Monthly Data

First, in the line chart below, you can:

• see how each store's sales progress over the year
• compare each store to the other two stores

Box Plot Chart - Quartiles

Instead of showing data over time, a box and whisker chart shows the data in numerical order, divided into 4 equal sections (quartiles).

This diagram shows the different parts of the box plot chart, and you'll see how to create this, in the video and written steps, further down on this page.

Store Sales Box Plot

Here is a horizontal box plot chart for the three stores' sales data. This chart lets you compare:

• Highest and lowest sales numbers for each store
• Median number (middle value) for each store
• Range of numbers on either side of the median for each store

What Does This Box Plot Show?

At a quick glance, this chart show the following nformation:

• Store 3 had 6 months of low sales (light blue box) and 6 months with a wide range of sales amounts (darker blue box).
• Store 1 had the most consistent sales over the year, with small boxes, and the shortest span from its minimum, to its maximum.

See Steps and More Examples

Excel 2013 doesn't have a Box Plot chart type, but you can build your own Box and Whisker plot, by following the examples below.

• Build a Box Plot: In the next section, my video shows how to build a vertical box plot chart, comparing sales in two regions. There are written steps below the video.
• Average Marker: Further down the page, there's another example, that shows how to add an average marker to a Box Plot chart. Watch the video to see the steps, and there are written steps too
• Wait Times: The third example compares airport security line wait times. The video shows the set-up steps, and there is a link where you can find written notes about the example.

In these examples, you'll discover how to:

• Add calculations to the worksheet
• Create a Stacked Column chart type, or a stacked bar chart
• Add custom error bars
• Format the boxes

Video: Build Your Own Box Plot

To see the steps for creating a simple box plot chart (box and whisker plot), 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.

Video Timeline

• 00:00 Box Plot Introduction
• 00:12 Add Calculations
• 00:54 Plan the Chart
• 01:14 Box Size Calculations
• 02:30 Create Stacked Column Chart
• 03:17 Add Whiskers at Top
• 03:51 Add Whiskers at Bottom
• 04:29 Add Formatting
• 05:12 Get the Sample File

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 Box Plot Chart Formulas

The next step is to enter the formulas that will be used in the chart. Simple formulas calculate the minimum value, first Quartile, Median, 3rd Quartile, and the Maximum value.

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

• F4 - Min: =MIN(B4:B11)
• F5 - Q1: =QUARTILE(B4:B11,1)
• F6 - Median: =MEDIAN(B4:B11)
• F7 - Q3: =QUARTILE(B4:B11,3)
• F8 - Max: =MAX(B4:B11)

Box Heights

From those calculations, you can calculate the height of each box, and the length of the upper whisker and lower whiskers.

The diagram below shows where each measure appears in the box plot.

To calculate the heights/lengths, subtract the bottom measure in each section, from the top one.

For example:

• The upper box height is Quartile 3 (75th percentile) minus the Median.
• The lower box height is the Median, minus Quartile 1 (25th percentile)

The formulas are listed below the screenshot, which shows the five-number summary.

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

The completed worksheet with data and formulas, is shown in the screenshot below.

Create the Box Plot Chart

To start the Box Plot chart:

1. Select cells E3:G3 -- the heading cells
2. Next, press Ctrl and select the blue data cells and labels, E10:G12.
3. On the Excel Ribbon, click the Insert tab
4. In the Charts group, click Column Chart, then, under 2-D Column, click Stacked Column

A chart is added to the worksheet, with stacked columns

To Fix the Box Plot Chart:

If the chart shows 3 stacked columns, instead of 2 (for East and West), follow these steps to fix the problem

1. Click on the chart, to select it
2. On the Excel Ribbon, go to the Chart Design tab
3. In the Data group, click the Switch Row/Column command, to get the box series stacked correctly.

The chart should have 2 stacked columns now - East and West.

To format the Box Plot Chart:

First, follow these steps to hide the bottom series (Box 1 - hidden) in the stacked columns

1. In the East column, right-click on the Box 1 - hidden series
2. In the pop-up menu that appears, click the Fill drop down arrow
3. Choose No Fill from the list
4. Next, click the drop down arrow for the Outline colour
5. Choose No Outline from the list, so it isn't visible in the chart.

Next, follow these steps to remove the legend, which isn't needed

1. Click on the chart's Legend, to select it
2. On your keyboard, press the Delete key, to remove the legend

To add the Top Whisker:

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

   

6. Click OK to close the Custom Error Bars window.

To add the Bottom Whisker:

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

   

6. Click OK to close the Custom Error Bars window

Final Formatting Steps

To finish the box plot chart formatting, follow these steps:

1. Right-click on top box in the East column
2. In the pop-up menu that appears, click the Fill drop down arrow
3. Choose a light grey colour from the list
4. Next, click the drop down arrow for the Outline colour
5. Choose a darker grey colour from the list - this will visually separate the two boxes
6. Next, format the lower box with the same fill colour and outline colour

(Optional) Move the chart, so it covers the cells where you created the formulas.

Ex 2) Add 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:

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



4. Copy the cells with the Average label, and the formulas
5. Click on the chart, and on the Ribbon's Home tab, click the arrow on the Paste button
6. Click Paste Special.
7. In the Paste Special dialog box, choose "New Series", Values in Rows, and "Series Names in First Column", and click OK

   

8. If the Average series appears as a Stacked Column, right-click one of the columns, and click Change Series Chart Type

   

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

   

10. Click on the line, and on the Ribbon's Format tab, click Format Selection
11. In the Format Data Seriex pan, go to the Fill and Line tab (paint can)
12. On the Line sub-tab, in the Line section, select No Line

    

13. Close the Format Data Series window, and the Average marker appears on the chart.

    

Ex 3) Video: Wait Times Box Plot

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

Regon Sales: You can download the sample file that was used in the East/West Region Sales example video -- Simple Box Plot Chart. The zipped file is in xlsx format, and does not contain macros.

More Tutorials

Box Plot Chart Video

Charts, Cluster Stack Utility

Charts, Line-Column 2 Axes

Charts, Panel

Charts, Waterfall

VBA Code, Copy to a workbook