How to Calculate a Five Number Summary in Excel


five number summary is a way to summarize a dataset using the following five values:

  • The minimum
  • The first quartile
  • The median
  • The third quartile
  • The maximum

The five number summary is useful because it provides a concise summary of the distribution of the data in the following ways:

  • It tells us where the middle value is located, using the median.
  • It tells us how spread out the data is, using the first and third quartiles.
  • It tells us the range of the data, using the minimum and the maximum.

By simply knowing these five values, we can know a great deal about a dataset.

How to Find the Five Number Summary in Excel

We can find the five number summary of a dataset in Excel using the following steps:

Step 1: Enter the data values in one column.

How to calculate five number summary in Excel

Step 2: Find the five number summary.

The five values of the five number summary are shown in column D and the formulas used to find these values are shown in column E:

Five number summary in Excel calculations

Thus, the five number summary for this data is as follows:

Minimum: 4

1st Quartile: 7.5

Median: 17

3rd Quartile: 22

Max: 28

Technical Note:

 

There are technically two quartile functions in Excel:

 

QUARTILE.INC() – Calculates percentiles using “greater than or equal to” in an “inclusive” manner.

 

QUARTILE.EXC() – Calculates percentiles using “greater than” in an “exclusive” manner.

 

The default function QUARTILE() uses the QUARTILE.INC() method.

Visualizing a Five Number Summary Using a Boxplot

One of the easiest ways to visualize a five number summary is by creating a boxplot, sometimes called a box-and-whisker plot, which uses a box with a line in the middle along with “whiskers” that extend on each end. 

Boxplot explanation

You can perform the following steps to create a boxplot in Excel:

Step 1: Highlight the data values.

Boxplot for five number summary in Excel

Step 2: In the Insert tab in the Charts group along the top ribbon, click the tiny arrow in the bottom left corner to “See All Charts.”

Charts in Excel

Step 3: Select “Box & Whisker” and click OK.

Boxplot in Excel

A box and whisker plot will automatically be displayed.

Boxplot in Excel

The top whisker represents the max, the top of the box represents the 3rd quartile, the middle line in the box represents the median, the tiny “x” in the box represents the average, the bottom of the box represents the 1st quartile, and the bottom whisker represents the minimum value:

Five number summary boxplot in Excel

You can change the background color and the chart title as well to make it more aesthetically pleasing:

Five number summary using a boxplot in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *