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) !!