How to Divide Data into Quartiles in Excel

Breaking down a list of numbers into meaningful groups can feel like a chore, but it's one of the best ways to find the real story hiding in your data. Using quartiles to segment your dataset into four equal parts is a fantastic way to do this. This article will show you exactly what quartiles are and walk you through the simplest formulas and charts in Excel to find them.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Are Quartiles and Why Should You Care?

In simple terms, quartiles are values that divide your data into four equal quarters. Imagine you have a list of last month's sales figures for 30 different products, sorted from lowest to highest. Quartiles split that sorted list into four chunks with the same number of products in each.

This gives you three quartile boundary points:

• Q1 (First Quartile): Also known as the 25th percentile. 25% of your data points fall below this value.
• Q2 (Second Quartile): This is the median, or the 50th percentile. Exactly half of your data is below this value, and half is above it.
• Q3 (Third Quartile): Also known as the 75th percentile. 75% of your data points fall below this value.

Why is this so useful? Quartiles help you quickly understand the spread and center of your data without getting lost in every single data point. For a sales manager, this could mean:

• The bottom 25% of products (below Q1) might be candidates for discontinuation or a marketing boost.
• The middle 50% of products (between Q1 and Q3) are your steady, core performers. This range is also called the Interquartile Range (IQR).
• The top 25% (above Q3) are your superstar products. What makes them so successful, and how can you replicate it?

By segmenting the data, you move from just looking at a long list of numbers to identifying distinct performance groups. You can apply this same logic to student test scores, website traffic by session duration, customer satisfaction ratings, or employee performance metrics.

The Easiest Method: Using the QUARTILE.INC Function

For most day-to-day business analysis, Excel's QUARTILE.INC function is your best friend. It’s the most common and direct way to find quartiles. The "INC" stands for "inclusive," which means it includes the 0th and 100th percentiles (the minimum and maximum values) in its calculation, matching the traditional definition of quartiles.

Let's use a sample dataset of monthly website conversions over the past year.

Example Data (Range A2:A13):

150, 210, 185, 190, 230, 255, 240, 215, 205, 195, 270, 160

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step Guide to QUARTILE.INC

The syntax for the function is simple:

=QUARTILE.INC(array, quart)

• array: This is the range of cells containing your numeric data. In our example, it's A2:A13.
• quart: This is a number from 0 to 4 that tells Excel which quartile value you want to return.

Calculating Each Quartile:

To find the first quartile (Q1), type into a blank cell:

=QUARTILE.INC(A2:A13, 1)

Excel will calculate this and return the value 188.75.

You can create a small summary table to find all key values:

• To find the Minimum (0):
• To find the First Quartile (1):
• To find the Median (2):
• To find the Third Quartile (3):
• To find the Maximum (4):

Just from these five calculations, you now have a powerful summary: 25% of months had fewer than ~189 conversions, the median was ~208, and the top-performing 25% of months saw more than ~233 conversions.

An Alternative Approach: The QUARTILE.EXC Function

Excel also offers another function called QUARTILE.EXC. The "EXC" stands for "exclusive." This function calculates quartile boundaries based on a percentile range that is strictly between 0 and 1. Think of it as excluding the absolute minimum and maximum values when determining the groupings.

The syntax is nearly identical:

=QUARTILE.EXC(array, quart)

The key difference is that the quart argument only accepts 1, 2, or 3. You can't use it to find the minimum (0) or maximum (4) value.

Using the same website conversion data (range A2:A13):

• To find the First Quartile (1):
• To find the Median (2):
• To find the Third Quartile (3):

Notice the Q1 and Q3 values are slightly different from those calculated with QUARTILE.INC. This is because the underlying mathematical formula is different. It calculates quartile values based on subsets of the data that exclude the endpoints.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

So, Which One Should You Use? QUARTILE.INC vs. QUARTILE.EXC

This is a common point of confusion, but the answer is usually simple: stick with QUARTILE.INC.

QUARTILE.INC aligns with the standard method of quartile calculation taught in most statistics courses and used by many analytics tools. It's the expected method for general business reporting.

QUARTILE.EXC is used in more specific statistical contexts where you may want to ensure that the median of a subsection of data is analyzed versus the full data set. Unless you have a specific statistical reason to use it, choosing QUARTILE.INC will give you the results that most people—including your boss and coworkers—will recognize and expect.

Visualizing Quartiles with a Box and Whisker Plot

Numbers in a table are great, but a visual chart is often better for communicating insights. Excel has a perfect built-in chart for visualizing quartiles: the Box and Whisker plot.

A Box and Whisker plot visually displays your five-number summary (Min, Q1, Median, Q3, Max) and instantly shows the spread of your data.

How to Create a Box and Whisker Plot in Excel:

1. Select Your Data: Highlight your range of numbers (in our case, A2:A13). Make sure to include the column header if you have one.
2. Go to the Insert Tab: In the Excel ribbon at the top, click on the "Insert" tab.
3. Find Statistical Charts: In the "Charts" section, look for an icon that looks like a small histogram or bar chart and click it. It's the "Insert Statistic Chart" option.
4. Choose "Box and Whisker": From the dropdown menu, select "Box and Whisker."

Excel will instantly generate a chart. Here’s how to interpret it:

• The main box represents the Interquartile Range (IQR). The bottom edge of the box is Q1, and the top edge is Q3. The length of the box shows the spread of the middle 50% of your data.
• The line inside the box represents the median (Q2).
• The "whiskers" (lines extending from the box) show the range of the data outside the middle 50%. The bottom whisker goes down to the minimum value, and the top whisker goes up to the maximum.
• The small "x" inside the box indicates the mean (average), giving you a quick way to see how it differs from the median.

This simple chart tells a compelling story. A short box means your core data is tightly packed, while a long box means it's widely spread out. If the median line is not centered in the box, it tells you the data is skewed.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Going Further with Percentiles

Quartiles are just specific types of percentiles. If you want to segment your data into groups other than quarters, you can use the PERCENTILE.INC function. This gives you much more flexibility.

For example, what if you wanted to find the cutoff for the top 10% of performing months? You'd be looking for the 90th percentile.

The syntax is very similar to the quartile function:

=PERCENTILE.INC(array, k)

• array: The range of your data (A2:A13).
• k: A number between 0 and 1 representing the percentile you want. 0.1 for 10th percentile, 0.9 for 90th percentile, etc.

To find the 90th percentile:

=PERCENTILE.INC(A2:A13, 0.9)

Result: 259.5

This tells you that 90% of your months had fewer than 259.5 conversions, putting the top 10% above that mark. You can use PERCENTILE.INC(A2:A13, 0.25) to get the same exact result as QUARTILE.INC(A2:A13, 1).

Final Thoughts

Breaking your data into quartiles using functions like QUARTILE.INC is a foundational skill in Excel that moves you from simply collecting data to actually understanding it. By identifying the key performance segments - your bottom 25%, your core middle 50%, and your top 25% - you can make smarter, more informed decisions with confidence.

Calculating quartiles in Excel is a powerful skill, but it’s often just one step in a much longer manual reporting process. Each week, you might download a new CSV, clean the data, run formulas, build charts, and email the results. We built Graphed to break this tedious cycle. Instead of wrestling with spreadsheet functions, you can connect data sources like Google Analytics or Shopify and simply ask for what you need. AI handles the calculations for you, presenting insights instantly in live, shareable dashboards so you can spend less time crunching numbers and more time acting on what they mean.