Calculate the quartile differences with the Excel subtraction formula

Select one numerical variable and one categorial variable that contains at least 3 different values from your data set.
Note: Selecting a categorical variable with a small number of categories will be preferable over one with many unique values.
For each of the qualitative variables, create a box plot following the steps outlined below.

Create a box plot

If you’re doing statistical analysis, you may want to create a standard box plot to show distribution of a set of data. In a box plot, numerical data is divided into quartiles, and a box is drawn between the first and third quartiles, with an additional line drawn along the second quartile to mark the median. In some box plots, the minimums and maximums outside the first and third quartiles are depicted with lines, which are often called whiskers.

While Excel 2013 doesn’t have a chart template for box plot, you can create box plots by doing the following steps:
Calculate quartile values from the source data set.
Calculate quartile differences.
Create a stacked column chart type from the quartile ranges.
Convert the stacked column chart to the box plot style.

In our example, the source data set contains three columns. Each column has 30 entries from the following ranges:
Column 1 (2013): 100–200
Column 2 (2014): 120–200
Column 3 (2015): 100–180

In this article

Step 1: Calculate the quartile valuesLinks to an external site.
Step 2: Calculate quartile differencesLinks to an external site.
Step 3: Create a stacked column chartLinks to an external site.
Step 4: Convert the stacked column chart to the box plot styleLinks to an external site.
Hide the bottom data seriesLinks to an external site.
Create whiskers for the box plotLinks to an external site.
Color the middle areasLinks to an external site.
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.
To do this, create a second table, and populate it with the following 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 should get a table containing the correct values. The following quartiles are calculated from the example data set:
Top of PageLinks to an external site.
Step 2: Calculate quartile differences
Next, calculate the differences between each phase. In effect, you have to calculate the differentials between the following:
First quartile and minimum value
Median and first quartile
Third quartile and median
Maximum value and third quartile
To begin, create a third table, and copy the minimum values from the last table there directly.

Calculate the quartile differences with the Excel subtraction formula (cell1 – cell2), and populate the third table with the differentials.
For the example data set, the third table looks like the following:
Top of PageLinks to an external site.
Step 3: Create a stacked column chart
The data in the third table is well suited for a box plot, and we’ll start by creating a stacked column chart which we’ll then modify.
Select all the data from the third table, and click Insert > Insert Column Chart > Stacked Column.
At first, the chart doesn’t yet resemble a box plot, as Excel draws stacked columns by default from horizontal and not vertical data sets.
To reverse the chart axes, right-click on the chart, and click Select Data.

 

Last Completed Projects