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 A	Brand B	Brand C
144	98	184
117	158	113
147	170	72
111	107	229
143	170	132
193	163	97
152	135	194
157	140	85
215	79	157
183	122	109
74	97	148
144	103	115
161	178	101
117	132	71
163	167	95
150	182	172

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

Value	Formula
Minimum value	MIN(cell range)
First quartile	QUARTILE.INC(cell range, 1)
Median value	QUARTILE.INC(cell range, 2)
Third quartile	QUARTILE.INC(cell range, 3)
Maximum value	MAX(cell range)

As a result you will get the following table

	Brand A	Brand B	Brand C
Minimum	74	79	71
First Quartile	136.5	106	96.5
Median Value	148.5	137.5	114
Third quartile	161.5	167.75	160.75
Maximum value	215	182	229

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 A	Brand B	Brand C
Minimum	74	79	71
First Quartile-Minimum	62.5	27	25.5
Median-First Quartile	12	31.5	17.5
Third Quartile-Median	13	30.25	46.75
Maximum -Third Quartile	53.5	14.25	68.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

 

So the resultant chart will be

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

So the resultant chart will be

Excel Boxplot Step 5: Create whiskers for the box plot

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

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

So the final chart will be like

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