Excel Boxplot – Create Box and Whisker Chart in Excel

Boxplots are a measure of how well distributed is the data. Boxplot is a convenient way of graphically depicting groups of numerical data through their quartiles. Box plot represents the minimum, maximum, median, first quartile and third quartile in the data set. Lets see how to plot the Excel Boxplot in this chapter .

The data we use for depicting the Excel Boxplot is shown below

Brand ABrand BBrand C
14498184
117158113
14717072
111107229
143170132
19316397
152135194
15714085
21579157
183122109
7497148
144103115
161178101
11713271
16316795
150182172

Excel Boxplot Step 1: Calculate the quartile values:

First you need to calculate the minimum, maximum and median values, as well as the first and third quartiles, from the data set with the following set of formulas

ValueFormula
Minimum valueMIN(cell range)
First quartileQUARTILE.INC(cell range, 1)
Median valueQUARTILE.INC(cell range, 2)
Third quartileQUARTILE.INC(cell range, 3)
Maximum valueMAX(cell range)

As a result you will get the following table

 Brand ABrand BBrand C
Minimum747971
First Quartile136.510696.5
Median Value148.5137.5114
Third quartile161.5167.75160.75
Maximum value215182229

Excel Boxplot Step 2: Calculate quartile differences

Next, calculate the differences between each phase. So we will be calculating

  • Minimum Value
  • Difference between First Quartile & Minimum
  • Difference between Median & First Quartile
  • Difference between Third Quartile & Median
  • Difference between Maximum & Third Quartile.

So the resultant data set will be

 Brand ABrand BBrand C
Minimum747971
First Quartile-Minimum62.52725.5
Median-First Quartile1231.517.5
Third Quartile-Median1330.2546.75
Maximum -Third Quartile53.514.2568.25

Excel Boxplot Step 3: Create a stacked column chart

We use the Above table to create the stack column chart

Select all the data from the third table, and click Insert > Recommended chart >All charts >Stacked Column  select Second chart as shown below and click OK

Excel boxplot boxplot in Excel

 

So the resultant chart will be

Excel boxplot boxplot in Excel

Excel Boxplot Step 4: Convert the stacked column chart to the box plot.

Right click on the bottom column -> Format Data Series

  • Under FILL click “No fill”
  • Under BORDER click “No line”

Do the same by selecting the Top column

Excel boxplot boxplot in Excel

So the resultant chart will be

Excel boxplot boxplot in Excel

Excel Boxplot Step 5: Create whiskers for the box plot

From the ribbon, click Design > Add Chart Element > Error Bars > Standard Deviation.

boxplot-in-excel-4

Now right click on the error bars -> Format error bars

  • Under Direction click Minus
  • Under End Style Click No Cap
  • Under error amount mention 100%

As shown below

Excel boxplot boxplot in Excel

So the final chart will be like

Excel boxplot boxplot in Excel

There you go!! successfully created an Excel Boxplot (Excel Box and Whiskers Chart) !!

next small box plot in excel