How to Find 5 Number Summary in Excel

Finding the five-number summary in Excel is a great way to quickly understand the distribution of your data. This simple set of statistics breaks down your dataset into five key points, giving you a clear picture of its spread and central tendency. In this tutorial, we’ll walk through exactly how to calculate the minimum, first quartile, median, third quartile, and maximum in Excel using a few simple functions.

What Exactly is the Five-Number Summary?

Before we jump into the formulas, let’s quickly clarify what these five numbers represent. The five-number summary is a set of descriptive statistics that gives you a snapshot of your data at a glance. It's the core component used to create a box plot (or box-and-whisker plot).

Here’s the breakdown:

• Minimum (Min): The smallest value in your dataset. It shows you the lowest boundary of your data.
• First Quartile (Q1): Also known as the 25th percentile. This means 25% of your data points fall below this value. It marks the end of the first quarter of your data.
• Median (Q2): The middle value of the dataset when it's sorted from smallest to largest. 50% of the data is below the median, and 50% is above it. It's a great measure of the center of your data.
• Third Quartile (Q3): Known as the 75th percentile. This tells you that 75% of your data points fall below this value. It marks the boundary for the top quarter of your data.
• Maximum (Max): The largest value in your dataset. It represents the highest boundary.

Together, these five stats show you the range (from min to max) and the interquartile range (the spread of the middle 50% of your data, spanning from Q1 to Q3). It’s incredibly useful for identifying the distribution and potential skewness of your data without getting lost in the weeds.

Method 1: Using Individual Excel Functions

The most direct way to get the five-number summary is by using five separate but very simple Excel functions. This method gives you full control and helps you understand exactly where each number comes from.

Let’s assume you have a list of sales figures for last month in cells B2 through B31. Here’s how you’d calculate each part of the summary.

Step 1: Find the Minimum Value

To find the smallest value in your dataset, use the MIN function. Click on an empty cell, type the following formula, and press Enter:

=MIN(B2:B31)

Excel will immediately return the lowest sales figure from your list.

Step 2: Find the First Quartile (Q1)

Next up is the first quartile. For this, we’ll use the QUARTILE.INC function. The .INC part stands for "inclusive," which is the standard statistical method and the one you should almost always use.

In another empty cell, type this formula:

=QUARTILE.INC(B2:B31, 1)

The formula takes two arguments: your data range (B2:B31) and the quartile you want (1 for the first quartile). This will give you the 25th percentile value.

Step 3: Find the Median (Q2)

The median is the middle value. The easiest way to find it is with the MEDIAN function.

=MEDIAN(B2:B31)

Alternatively, you could also get the median using the quartile function, since the median is technically the second quartile:

=QUARTILE.INC(B2:B31, 2)

Both formulas will give you the exact same result, but the MEDIAN function is a bit more self-explanatory.

Step 4: Find the Third Quartile (Q3)

Similar to finding Q1, we’ll use the QUARTILE.INC function again, but this time we'll tell Excel we're looking for the third quartile by using 3 as the second argument.

=QUARTILE.INC(B2:B31, 3)

This will calculate the 75th percentile, which marks the top of the middle 50% of your data.

Step 5: Find the Maximum Value

Last but not least, we need to find the highest value. For this, simply use the MAX function.

=MAX(B2:B31)

And that’s it! You have now calculated all five numbers. To keep things clean, you can label them in adjacent cells so you have a tidy summary table.

Method 2: Using the Data Analysis ToolPak

If you want a broader statistical overview beyond just the five-number summary, Excel’s built-in Data Analysis ToolPak is a fantastic alternative. It's an add-in, so you may need to enable it first. It provides a huge list of stats with just a few clicks.

How to Enable the Data Analysis ToolPak

If you don't see "Data Analysis" under the Data tab in your ribbon, here’s how to turn it on:

1. Click on File in the top-left corner, then choose Options at the bottom.
2. In the Excel Options window, click on Add-ins from the left-hand menu.
3. At the bottom of the window, next to "Manage," make sure Excel Add-ins is selected and click the Go... button.
4. A small box will appear. Check the box next to Analysis ToolPak and click OK.

The "Data Analysis" button will now be available on the far right of your Data tab.

Running the Descriptive Statistics Report

With the ToolPak enabled, follow these steps:

1. Navigate to the Data tab and click on Data Analysis.
2. Select Descriptive Statistics from the list and click OK.
3. In the dialog box, for the Input Range, select your data (e.g., $B$2:$B$31).
4. Choose where you want your results. Selecting Output Range and picking a cell like E1 is usually easiest.
5. Most importantly, make sure you check the box for Summary statistics.
6. Click OK.

Excel will instantly generate a table full of useful statistics, including the Minimum, Maximum, and Median. However, it's important to note that the ToolPak does not directly provide the first and third quartiles (Q1 and Q3). You'll get plenty of other valuable info like mean, mode, and standard deviation, but you will still need to use the =QUARTILE.INC() function from Method 1 to complete the traditional five-number summary.

Visualizing the Five-Number Summary with a Box Plot

The primary use of the five-number summary is to create a Box and Whisker plot, and modern versions of Excel make this incredibly easy. This chart type gives you a powerful visual representation of your data's distribution.

Here’s how to create one:

1. Select your raw data (just the column of numbers, e.g., B2:B31).
2. Go to the Insert tab.
3. In the Charts section, click the Insert Statistic Chart icon (it looks like a small histogram).
4. Choose Box and Whisker.

Excel will generate a chart that visually displays your five-number summary:

• The bottom "whisker" shows the minimum value.
• The top "whisker" shows the maximum value.
• The box itself represents the interquartile range (the middle 50% of your data).
• The bottom of the box is Q1.
• The top of the box is Q3.
• The line in the middle of the box is the median.

This allows you to see the spread and skewness of your data in an instant.

QUARTILE.INC vs. QUARTILE.EXC: Which to Use?

You might notice that Excel offers two quartile functions: QUARTILE.INC and QUARTILE.EXC. While they seem similar, they use slightly different calculations.

• QUARTILE.INC (inclusive) is based on a method that includes the 0th and 100th percentiles (your min and max values) within the calculation spectrum. This is the more traditional and widely accepted method taught in most statistics courses.
• QUARTILE.EXC (exclusive) excludes those endpoints from the calculation.

For most business and general data analysis, sticking with QUARTILE.INC is the best practice. It aligns with the standard definition of quartiles and will give you the results you most likely expect.

Final Thoughts

Calculating the five-number summary in Excel is a fundamental skill for anyone working with data. Using simple functions like MIN, MAX, MEDIAN, and QUARTILE.INC, you can quickly build a clear and powerful statistical picture of any dataset from right within your spreadsheet.

When you're ready to move beyond manual spreadsheet analysis, dedicated tools can automate this entire process. With Graphed, for example, we built a way to get these insights without any formulas at all. Once you connect your data sources like Google Analytics or Salesforce, you can just ask a question like, "Show me a dashboard of my monthly sales performance with the five-number summary." We designed it to handle the data connections and calculations for you, turning hours of manual work into a 30-second task.