How to Calculate Quartiles in Excel

Key Insights

• Excel provides three quartile functions—QUARTILE, QUARTILE.INC, and QUARTILE.EXC—each using different calculation methods that can produce different results on the same dataset
• The 1.5×IQR rule combined with quartile calculations gives you a robust, formula-based method for identifying outliers without subjective judgment
• QUARTILE.INC matches most statistical software defaults and handles smaller datasets better, while QUARTILE.EXC is preferred in academic research with larger samples

Introduction to Quartiles

Quartiles divide your dataset into four equal parts, giving you a clear picture of how your data is distributed. Q1 (the first quartile) marks the 25th percentile—25% of your data falls below this value. Q2 is the median (50th percentile), and Q3 marks the 75th percentile.

Why should you care about quartiles? Three reasons:

1. Spread measurement: The distance between Q1 and Q3 (the Interquartile Range) tells you how spread out the middle 50% of your data is, without being skewed by extreme values.
2. Outlier detection: Quartiles form the foundation of the 1.5×IQR rule, the most widely accepted statistical method for identifying outliers.
3. Distribution shape: Comparing the gaps between quartiles reveals whether your data is symmetric or skewed.

If you’re analyzing sales figures, survey responses, or any numerical dataset, quartiles give you more insight than simple averages ever could.

Understanding Excel’s Quartile Functions

Excel offers three functions for quartile calculations, and choosing the wrong one will give you incorrect results.

QUARTILE: The legacy function from older Excel versions. Microsoft keeps it for backward compatibility, but you shouldn’t use it for new work. It’s equivalent to QUARTILE.INC.

QUARTILE.INC: Uses inclusive quartile calculation, meaning Q0 equals the minimum value and Q4 equals the maximum. This method interpolates values and can return results that exist between actual data points.

QUARTILE.EXC: Uses exclusive quartile calculation, excluding the minimum and maximum from the quartile range. Q0 and Q4 are undefined with this function.

Here’s the syntax comparison:

=QUARTILE(array, quart)        ' Legacy - avoid
=QUARTILE.INC(array, quart)    ' Inclusive method
=QUARTILE.EXC(array, quart)    ' Exclusive method

The quart argument accepts values 0-4 for .INC (0=min, 1=Q1, 2=Q2, 3=Q3, 4=max) and 1-3 for .EXC.

The practical difference: on small datasets, these functions can return noticeably different values. On large datasets (1000+ rows), the results converge.

Step-by-Step: Calculating Quartiles with QUARTILE.INC

Let’s work through a real example. Assume you have monthly sales data in cells A2:A50 (49 values representing weekly sales figures).

' Cell B2: Calculate Q1 (25th percentile)
=QUARTILE.INC(A2:A50, 1)

' Cell B3: Calculate Q2 (Median/50th percentile)
=QUARTILE.INC(A2:A50, 2)

' Cell B4: Calculate Q3 (75th percentile)
=QUARTILE.INC(A2:A50, 3)

' Cell B5: Calculate Min (same as Q0)
=QUARTILE.INC(A2:A50, 0)

' Cell B6: Calculate Max (same as Q4)
=QUARTILE.INC(A2:A50, 4)

For a dataset with values: 12, 15, 18, 22, 25, 28, 31, 35, 42, 48, 55, 62, 71, 85, 92

=QUARTILE.INC(A2:A16, 1)    ' Returns 21.5
=QUARTILE.INC(A2:A16, 2)    ' Returns 35
=QUARTILE.INC(A2:A16, 3)    ' Returns 58.25

Notice that Q1 returns 21.5, which doesn’t exist in the original data. This is because QUARTILE.INC uses linear interpolation between the 4th value (22) and the 3rd value (18) based on the exact 25th percentile position.

Use QUARTILE.INC when:

• Your dataset has fewer than 100 values
• You need compatibility with other tools (R’s default, Python’s pandas)
• You’re working with business data where inclusive bounds make intuitive sense

Using QUARTILE.EXC for Statistical Analysis

The exclusive method treats quartiles differently. It assumes the data represents a sample from a larger population and excludes the endpoints from quartile calculations.

Using the same 15-value dataset:

=QUARTILE.EXC(A2:A16, 1)    ' Returns 20
=QUARTILE.EXC(A2:A16, 2)    ' Returns 35
=QUARTILE.EXC(A2:A16, 3)    ' Returns 60.5

Here’s a side-by-side comparison with a 20-row dataset (values 5, 8, 12, 15, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45, 48, 51, 54, 57, 60, 63):

| Quartile | QUARTILE.INC | QUARTILE.EXC | Difference |
|----------|--------------|--------------|------------|
| Q1       | 17.25        | 16.5         | 0.75       |
| Q2       | 34.5         | 34.5         | 0          |
| Q3       | 51.75        | 52.5         | 0.75       |

The median (Q2) is identical, but Q1 and Q3 differ. This matters when you’re calculating IQR for outlier detection—different quartile methods produce different outlier thresholds.

Use QUARTILE.EXC when:

• You’re doing academic or scientific research
• Your dataset exceeds 100 values
• You need to match R’s type=6 quantile method or SAS defaults

Important limitation: QUARTILE.EXC requires at least 4 data points and returns a #NUM! error on smaller arrays.

Calculating IQR and Identifying Outliers

The Interquartile Range (IQR) is simply Q3 minus Q1. Combined with the 1.5×IQR rule, it gives you a statistical basis for flagging outliers.

' Cell D2: Calculate IQR
=QUARTILE.INC(A2:A50, 3) - QUARTILE.INC(A2:A50, 1)

' Cell D3: Lower bound (values below this are outliers)
=QUARTILE.INC(A2:A50, 1) - (1.5 * D2)

' Cell D4: Upper bound (values above this are outliers)
=QUARTILE.INC(A2:A50, 3) + (1.5 * D2)

To flag individual values as outliers, add a helper column next to your data:

' Cell B2 (copy down): Flag outliers
=IF(OR(A2 < $D$3, A2 > $D$4), "Outlier", "Normal")

For a more elegant approach, use conditional formatting:

1. Select your data range (A2:A50)
2. Home → Conditional Formatting → New Rule
3. Choose “Use a formula to determine which cells to format”
4. Enter: =OR(A2<$D$3, A2>$D$4)
5. Set the format (red fill works well)

Now outliers are automatically highlighted. When your data changes, the highlighting updates automatically.

For extreme outliers (sometimes called “far outliers”), use the 3×IQR rule instead:

' Extreme lower bound
=QUARTILE.INC(A2:A50, 1) - (3 * D2)

' Extreme upper bound
=QUARTILE.INC(A2:A50, 3) + (3 * D2)

Visualizing Quartiles with Box Plots

Excel’s box-and-whisker chart displays all five quartile values (min, Q1, median, Q3, max) in a single visualization.

Creating the chart:

1. Select your data range including headers
2. Insert → Charts → Statistical → Box and Whisker
3. Excel automatically calculates quartiles and renders the plot

Customizing for clarity:

Right-click the chart and select “Format Data Series” to access these options:

• Show inner points: Displays individual data points within the IQR
• Show outlier points: Marks values beyond the whiskers
• Show mean markers: Adds an X at the mean position
• Show mean line: Draws a line connecting means across multiple series

If you need more control, build a box plot manually from your calculated quartile values:

' Set up these values in cells E2:E6
E2: =QUARTILE.INC(A2:A50, 0)    ' Min
E3: =QUARTILE.INC(A2:A50, 1)    ' Q1
E4: =QUARTILE.INC(A2:A50, 2)    ' Median
E5: =QUARTILE.INC(A2:A50, 3)    ' Q3
E6: =QUARTILE.INC(A2:A50, 4)    ' Max

Then create a stacked bar chart using these values with careful formatting to simulate the box plot appearance. This approach gives you pixel-level control over colors, labels, and dimensions.

Common Errors and Troubleshooting

When quartile functions fail, they typically return one of these errors:

Handling empty cells:

Excel’s quartile functions ignore empty cells but not cells containing zero. If your data has blanks that represent missing values (not actual zeros), you’re fine. If blanks should be zeros, fill them first.

Handling text in numeric ranges:

' This will error if A2:A50 contains any text
=QUARTILE.INC(A2:A50, 1)

' Workaround using array formula (Ctrl+Shift+Enter in older Excel)
=QUARTILE.INC(IF(ISNUMBER(A2:A50), A2:A50), 1)

Dealing with ties:

Quartile functions handle duplicate values correctly. If multiple data points share the same value at a quartile boundary, the function still returns the correct interpolated result.

Best practice: Before running any quartile analysis, validate your data with:

=COUNT(A2:A50)      ' Count of numeric values
=COUNTA(A2:A50)     ' Count of non-empty cells

If these numbers differ, you have non-numeric data that needs cleaning.

Quartile calculations in Excel are straightforward once you understand which function to use and why. Start with QUARTILE.INC for most business applications, validate your data first, and use the IQR method for objective outlier detection. The box plot visualization ties everything together for stakeholder presentations.