How to Use QUARTILE in Excel
Quartiles divide your dataset into four equal parts, each containing 25% of your data points. This statistical measure helps you understand data distribution beyond simple averages. When you're...
Key Insights
- Excel offers two quartile functions—QUARTILE.INC (inclusive) and QUARTILE.EXC (exclusive)—which calculate different boundary values for Q1 and Q3, with INC being the standard choice for most business analysis
- The quartile parameter ranges from 0 to 4, where 0 returns the minimum, 1-3 return the first through third quartiles, and 4 returns the maximum value in your dataset
- Combining QUARTILE with conditional logic lets you automatically categorize data into performance tiers, making it invaluable for salary benchmarking, sales rankings, and identifying statistical outliers
Understanding Quartiles and Excel’s QUARTILE Functions
Quartiles divide your dataset into four equal parts, each containing 25% of your data points. This statistical measure helps you understand data distribution beyond simple averages. When you’re analyzing sales performance, salary ranges, or student test scores, quartiles reveal where individual values fall within the broader dataset.
Excel provides two modern quartile functions: QUARTILE.INC and QUARTILE.EXC. The INC variant uses an inclusive calculation method that includes the minimum and maximum values as potential quartile boundaries. The EXC variant uses an exclusive method that calculates quartiles strictly between the data extremes. For most business applications, QUARTILE.INC is the appropriate choice and aligns with standard statistical practices.
The basic syntax looks like this:
=QUARTILE.INC(array, quart)
=QUARTILE.EXC(array, quart)
Where array is your data range and quart is a number from 0 to 4 specifying which quartile you want.
The Quartile Parameter Explained
The quartile parameter determines which statistical boundary you’re calculating. Here’s what each value returns:
- 0: Minimum value (same as MIN function)
- 1: First quartile (Q1) - 25th percentile
- 2: Second quartile (Q2) - 50th percentile/median
- 3: Third quartile (Q3) - 75th percentile
- 4: Maximum value (same as MAX function)
Let’s examine a practical dataset of monthly sales figures (in thousands):
A1: Sales
A2: 23
A3: 31
A4: 28
A5: 45
A6: 52
A7: 38
A8: 41
A9: 29
A10: 47
A11: 35
A12: 42
A13: 39
A14: 50
A15: 33
A16: 44
A17: 37
A18: 48
A19: 36
A20: 40
A21: 46
C1: Quartile
C2: Minimum (Q0)
C3: Q1 (25th)
C4: Q2 (Median)
C5: Q3 (75th)
C6: Maximum (Q4)
D1: Value
D2: =QUARTILE.INC($A$2:$A$21, 0) // Returns 23
D3: =QUARTILE.INC($A$2:$A$21, 1) // Returns 34
D4: =QUARTILE.INC($A$2:$A$21, 2) // Returns 39.5
D5: =QUARTILE.INC($A$2:$A$21, 3) // Returns 45.25
D6: =QUARTILE.INC($A$2:$A$21, 4) // Returns 52
This breakdown immediately shows you that 25% of months had sales below $34k, half were below $39.5k, and only 25% exceeded $45.25k.
QUARTILE.INC vs QUARTILE.EXC: When It Matters
The difference between inclusive and exclusive methods becomes apparent at the Q1 and Q3 boundaries. QUARTILE.INC includes the dataset extremes in its calculations, while QUARTILE.EXC excludes them, resulting in quartiles that fall more strictly within your data range.
Using the same sales dataset:
F1: Metric
F2: Q1 (INC)
F3: Q1 (EXC)
F4: Q2 (INC)
F5: Q2 (EXC)
F6: Q3 (INC)
F7: Q3 (EXC)
G1: Value
G2: =QUARTILE.INC($A$2:$A$21, 1) // Returns 34
G3: =QUARTILE.EXC($A$2:$A$21, 1) // Returns 35
G4: =QUARTILE.INC($A$2:$A$21, 2) // Returns 39.5
G5: =QUARTILE.EXC($A$2:$A$21, 2) // Returns 39.5
G6: =QUARTILE.INC($A$2:$A$21, 3) // Returns 45.25
G7: =QUARTILE.EXC($A$2:$A$21, 3) // Returns 45
Notice that Q2 (the median) remains identical, but Q1 and Q3 differ. The EXC method produces slightly tighter boundaries. For business reporting and performance analysis, stick with QUARTILE.INC—it’s the industry standard and matches how most statistical software calculates quartiles.
The legacy QUARTILE function (without .INC or .EXC) defaults to INC behavior but exists only for backward compatibility. Always use the explicit .INC or .EXC versions in new workbooks.
Practical Applications in Business Analysis
Quartiles excel at transforming raw data into actionable insights. Here are scenarios where quartile analysis delivers immediate value:
Sales Performance Ranking: Identify your top performers, middle tier, and underperformers. Create a performance dashboard that automatically categorizes sales reps:
A1: Sales Rep
A2: Johnson
A3: Martinez
A4: Chen
...
B1: Q4 Sales
B2: 156000
B3: 142000
B4: 178000
...
C1: Performance Tier
C2: =IF(B2>=QUARTILE.INC($B$2:$B$50,3),"Top 25%",
IF(B2>=QUARTILE.INC($B$2:$B$50,2),"Above Average",
IF(B2>=QUARTILE.INC($B$2:$B$50,1),"Below Average","Bottom 25%")))
Salary Benchmarking: When evaluating compensation, quartiles show where positions fall within market ranges. HR teams use Q1 for entry-level targets, Q2 for midpoint, and Q3 for experienced hires.
Outlier Detection: Values below Q1 - 1.5×IQR or above Q3 + 1.5×IQR are statistical outliers. The Interquartile Range (IQR) is Q3 minus Q1:
D1: IQR
D2: =QUARTILE.INC($B$2:$B$50,3) - QUARTILE.INC($B$2:$B$50,1)
E1: Lower Fence
E2: =QUARTILE.INC($B$2:$B$50,1) - 1.5*$D$2
F1: Upper Fence
F2: =QUARTILE.INC($B$2:$B$50,3) + 1.5*$D$2
G2: =IF(OR(B2<$E$2, B2>$F$2), "Outlier", "Normal")
Troubleshooting Common Errors
#NUM! Error: This occurs when your quartile parameter is outside the 0-4 range. Double-check that you’re using integers only:
// Wrong
=QUARTILE.INC(A2:A20, 3.5) // #NUM! error
// Correct
=QUARTILE.INC(A2:A20, 3)
#VALUE! Error: Your data range contains text or logical values. QUARTILE functions require numeric data. Use IFERROR to handle mixed data gracefully:
=IFERROR(QUARTILE.INC(A2:A20, 1), "Check data for non-numeric values")
Empty Cells: QUARTILE functions automatically ignore empty cells, which is usually beneficial. However, if your dataset has too few values (fewer than the quartile calculation requires), you’ll get inaccurate results. Ensure you have at least 4 data points for meaningful quartile analysis.
Insufficient Data for QUARTILE.EXC: The exclusive method requires more data points than the inclusive method. With small datasets, QUARTILE.EXC may return #NUM! errors where QUARTILE.INC works fine.
Advanced Techniques and Function Combinations
Dynamic Quartile Categorization: Build a formula that automatically assigns tier labels based on quartile performance:
=IF(B2>=QUARTILE.INC($B$2:$B$100,3),"Q4 - Top Performers",
IF(B2>=QUARTILE.INC($B$2:$B$100,2),"Q3 - Above Average",
IF(B2>=QUARTILE.INC($B$2:$B$100,1),"Q2 - Below Average",
"Q1 - Needs Improvement")))
Counting by Quartile: Determine how many values fall within each quartile range:
// Count values in Q1 (bottom quartile)
=COUNTIFS($B$2:$B$100,"<"&QUARTILE.INC($B$2:$B$100,1))
// Count values in Q2
=COUNTIFS($B$2:$B$100,">="&QUARTILE.INC($B$2:$B$100,1),
$B$2:$B$100,"<"&QUARTILE.INC($B$2:$B$100,2))
Conditional Formatting with Quartiles: Create visual indicators that automatically highlight top and bottom quartile performers. Select your data range, then create a conditional formatting rule using a formula:
// Highlight top quartile in green
=$B2>=QUARTILE.INC($B$2:$B$100,3)
// Highlight bottom quartile in red
=$B2<=QUARTILE.INC($B$2:$B$100,1)
Dynamic Named Ranges: Combine QUARTILE with OFFSET and COUNTA for ranges that automatically expand:
// Define named range "SalesData"
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
// Use in QUARTILE formula
=QUARTILE.INC(SalesData, 3)
This approach ensures your quartile calculations update automatically as you add new data rows.
Quartile analysis transforms raw numbers into strategic insights. Whether you’re evaluating employee performance, analyzing customer behavior, or identifying pricing anomalies, quartiles provide the statistical framework for data-driven decisions. Master these functions, and you’ll add a powerful analytical tool to your Excel arsenal.