Box-plot and whiskers chart in Excel

Keywords: Excel, box-plot,box plot, whiskers, quartile chart.

Version: Excel 97 or later

UPDATE: See also the Improved advanced box-plot and whiskers chart in Excel.

In this tutorial I will describe how to create neat looking box-plot graphs with whiskers in Excel. This chart type is particularly useful when presenting data series representing many replicate samples and you want to quickly give the audience a idea on the spread of the data. My example are from the field of natural science, but the chart type is equally useful in other fields.

To create this type of box-plot chart we need four values from our data; the first and third quartile and the max and min values of the data set. Fortunately these functions are all included in Excel, so that makes our life a lot easier.

1. Start by creating two data series in column B and C, like the example in picture 1. In cell E3:E6 enter Q1, Min, Max and Q3.

Picture 1.

2. In cell F3 you enter =QUARTILE(B2:B19,1) which will give the 25^th percentile of the population (the Sample 1 replicates). In F4 you enter =MIN(B2:B19). This will give the minimum values in the population. In a similar manner you enter =MAX(B2:B19) in F5 and =QUARTILE(B2:B19,3) in F6 to get the max value and the 75^th percentile respectively (see picture 1).

3. Now select cell range E3:E6, press Ctrl and select cell range F3:G6. Click the Chart wizard button and select the Stock Open-High-Low-Close type diagram (picture 2, click Next and then in the Data Range tab select Series in: Rows. Click finish.

 

Picture 2.

4. That’s basically it! You now have a basic box-plot and whiskers diagram which you can format to your liking, see picture 3.

 

Picture 3.

This article was originally posted on BloggPro.com.

Popularity: 33% [?]