Search Contextures Sites

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Create an Excel Box Plot Chart

A Box Plot, or box and whisker chart, can show you the distribution of the numbers in your 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?.

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

  • Save Time With the Box Plot Builder: To save time and effort, you can invest in the Excel Chart Utility which makes it easy to create complex charts, such as a Box Plot, or Waterfall Chart. It's described below.
  • OR, Create Your Own Box Plot: Follow the instructions below, to create your own box plot chart

Quick and Easy Box Plot Builder

If you donít have the time or patience to create your own Excel Box Plot chart, you can invest in a Box Plot Builder. Excel charting guru, Jon Peltier, offers a time-saving Excel Chart Utility, which includes a Box Plot chart builder, along with 7 other custom chart types. You can relax, while the Peltier Tech chart utility does all the work.

You can see how well the Excel Box Plot Chart builder works, in this short video.

Just select your data, click the Box Plot Chart command on the Ribbon, set a few options, and click OK, and your Box Plot chart is ready. A few columns with formulas are added in your workbook, to provide the data for the box plotchart.

The Box Plot also includes an Average marker, and several options for the Quartile calculations, as you can see in the example below.

Box Plot utility

It's a good investment, and the Peltier Tech Excel chart add-in will quickly pay for itself, because you wonít have to waste your time fiddling with calculations and chart settings.

There are 8 custom chart types available in the Charting Utility.

Peltier Tech chart utility Ribbon

In addition to the 8 custom charts, there are useful charting tools.

chart tools peltier chart utility

There is also a data tool, and there are a few general tools, to make your Excel work easier.

general tools peltier chart utility

Build Your Own Box Plot

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

    data for box plot chart

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. Then, the differences between those amounts are calculated, to get the height for the boxes. The formulas are listed below the screenshot.

formulas for box plot chart

F4: =MIN(B4:B11)

F5: =QUARTILE(B4:B11,1)

F6: =MEDIAN(B4:B11)

F7: =QUARTILE(B4:B11,3)

F8: =MAX(B4:B11)

F10: =F5

F11: =F6-F5

F12: =F7-F6

F14: =F8-F7

F15: =F5-F4

Copy the formulas across to column G.

Create the Box Plot Chart

To create the Box Plot chart:

  1. Select cells E3:G3 -- the heading cells -- then press Ctrl and select E10:G12.
  2. On the Excel Ribbon, click the Insert tab, and click Column Chart, then click Stacked Column
  3. If necessary, click the Switch Row/Column command on the Ribbon's Design tab, to get the box series stacked.
  4. 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.
  5. 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. top error bar for box plot chart

  7. 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. bottom error bar for box plot chart

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

Video: Build Your Own Box Plot

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

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:

  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. bottom error bar for box plot chart

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

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

  12. 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
  13. Paste Special

  14. Click on the line, and on the Ribbon's Format tab, click Format Selection
  15. In the Fill & Line section, under Line, select No Line
  16. Paste Special

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

    Paste Special

Video: Add Average Marker to Box Plot

To see the steps for adding an average marker to the box plot chart, please watch this video tutorial.

Download the Sample Chart

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

Save Time with the Charting Utility

Remember, if you donít have the time to create complex Excel charts, you can invest in the time-saving Excel Chart Utility, which includes a Box Plot chart builder, along with 7 other custom chart types. You can relax, while the Peltier Tech chart utility does all the work.

Peltier Tech chart utility Ribbon

Note: These are affiliate links, and I'll earn a small commission if you purchase the chart utility through these links.

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright ©2013
All rights reserved.