How to make a Box Plot, or box and whisker chart, to show the distribution of the numbers in your data
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?.
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.
To create your own box plot chart, the first step is to set up your data. In the screen shot below:
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.
Copy the formulas across to column G.
To finish the formatting, change the fill color for the boxes to light gray, and give them a darker gray border.
To see the steps for creating a simple box plot chart, please watch this video tutorial. The written instructions are above the video.
After you create a box plot chart, you can add an average marker to each box. To add the average, follow these steps:
To see the steps for adding an average marker to the box plot chart, please watch this video tutorial.
You can download the sample file that was used in this video -- Simple Box Plot Chart. The file is in xlsx format, and zipped.
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. 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 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.
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.
In addition to the 8 custom charts, there are useful charting tools.
There is also a data tool, and there are a few general tools, to make your Excel work easier.
Note: These are affiliate links, and I'll earn a small commission if you purchase the chart utility through these links.
Last updated: October 16, 2016 7:06 PM
Contextures RSS Feed