How to Use PERCENTILE in Excel
Percentiles divide your dataset into 100 equal parts, showing where a specific value ranks relative to others. If you're at the 75th percentile, you've outperformed 75% of the dataset. This matters...
Key Insights
- Excel offers three percentile functions (PERCENTILE, PERCENTILE.INC, PERCENTILE.EXC) that calculate differently—PERCENTILE.INC is the modern standard and matches most statistical software conventions
- The k parameter must be a decimal between 0 and 1 (use 0.75 for 75th percentile, not 75), and PERCENTILE.EXC has stricter limits that exclude exact 0 and 1 values
- Combine PERCENTILE with cell references and conditional formatting to build dynamic dashboards that automatically highlight outliers and performance tiers
Understanding Percentiles in Data Analysis
Percentiles divide your dataset into 100 equal parts, showing where a specific value ranks relative to others. If you’re at the 75th percentile, you’ve outperformed 75% of the dataset. This matters because averages and medians only tell part of the story—percentiles reveal distribution patterns, identify outliers, and help you make nuanced comparisons.
In business contexts, percentiles appear everywhere. HR teams use them to benchmark salaries against market data. Sales managers identify top performers by looking at the 90th percentile of revenue generation. Product teams analyze load times at the 95th percentile because that’s where user experience degrades. Understanding percentiles transforms raw data into actionable intelligence.
PERCENTILE vs PERCENTILE.INC vs PERCENTILE.EXC
Excel provides three percentile functions, and choosing the wrong one creates subtle inconsistencies in your analysis.
PERCENTILE is the legacy function from Excel 2007 and earlier. It still works for backward compatibility, but Microsoft recommends against using it in new workbooks. It calculates percentiles using the inclusive method.
PERCENTILE.INC (inclusive) is the modern replacement for PERCENTILE. It allows k values from 0 to 1 (inclusive) and uses linear interpolation between data points. This matches the definition used by most statistical software packages. When you request the 0th percentile, you get the minimum value. When you request the 100th percentile (k=1), you get the maximum value.
PERCENTILE.EXC (exclusive) uses a different calculation method that excludes the endpoints. It only accepts k values between 0 and 1 (exclusive), meaning you cannot calculate the 0th or 100th percentile. This method aligns with certain statistical textbooks and is preferred in some academic contexts.
Here’s how they differ with the same dataset:
Data: 10, 20, 30, 40, 50, 60, 70, 80, 90, 100
=PERCENTILE.INC(A1:A10, 0.25) // Returns 32.5
=PERCENTILE.EXC(A1:A10, 0.25) // Returns 27.5
=PERCENTILE(A1:A10, 0.25) // Returns 32.5 (same as INC)
=PERCENTILE.INC(A1:A10, 0) // Returns 10 (minimum)
=PERCENTILE.EXC(A1:A10, 0) // Returns #NUM! error
My recommendation: Use PERCENTILE.INC unless you have a specific reason to use the exclusive method. It’s the standard, produces results consistent with other tools, and handles edge cases more gracefully.
Basic Syntax and Usage
The PERCENTILE.INC function follows this structure:
=PERCENTILE.INC(array, k)
array is your data range—a single column, row, or array of numbers. It can include cell references like A1:A100 or named ranges.
k is the percentile value expressed as a decimal between 0 and 1. For the 25th percentile, use 0.25. For the 90th percentile, use 0.9. This trips up many users who instinctively type 25 or 90.
Let’s work with a practical example using 20 monthly sales figures:
A B
1 Month Sales
2 Jan 45000
3 Feb 52000
4 Mar 38000
5 Apr 61000
6 May 55000
7 Jun 49000
8 Jul 58000
9 Aug 44000
10 Sep 67000
11 Oct 53000
12 Nov 72000
13 Dec 81000
14 Jan 47000
15 Feb 59000
16 Mar 62000
17 Apr 48000
18 May 54000
19 Jun 69000
20 Jul 57000
21 Aug 64000
Calculations:
=PERCENTILE.INC(B2:B21, 0.25) // 25th percentile: 47,750
=PERCENTILE.INC(B2:B21, 0.5) // 50th percentile (median): 55,000
=PERCENTILE.INC(B2:B21, 0.75) // 75th percentile: 62,750
=PERCENTILE.INC(B2:B21, 0.9) // 90th percentile: 69,500
These calculations immediately show you that half your months generated at least $55,000, and only 10% exceeded $69,500. That’s more useful than knowing the average was $56,800.
Practical Applications
Salary Distribution Analysis
HR professionals use percentiles to ensure competitive compensation. Here’s a dataset of 15 employee salaries with percentile-based analysis:
A B C
1 Employee Salary Percentile Rank
2 Adams 52000 =PERCENTILE.RANK.INC($B$2:$B$16,B2)
3 Baker 68000
4 Chen 73000
5 Davis 45000
6 Evans 91000
7 Foster 58000
8 Garcia 62000
9 Hayes 55000
10 Irwin 78000
11 Jones 48000
12 Kumar 84000
13 Lopez 67000
14 Martin 71000
15 Nelson 53000
16 Owens 59000
Key Benchmarks:
25th Percentile: =PERCENTILE.INC(B2:B16, 0.25) // $53,500
50th Percentile: =PERCENTILE.INC(B2:B16, 0.5) // $62,000
75th Percentile: =PERCENTILE.INC(B2:B16, 0.75) // $72,000
90th Percentile: =PERCENTILE.INC(B2:B16, 0.9) // $82,600
Top 10% Threshold:
=PERCENTILE.INC(B2:B16, 0.9) // Anyone above $82,600 is top 10%
Identify Top Performers:
=IF(B2>=PERCENTILE.INC($B$2:$B$16,0.9),"Top 10%","")
This formula structure lets you automatically flag high performers, identify compensation gaps, and make data-driven salary decisions.
Performance Outlier Detection
You can combine PERCENTILE with conditional formatting to visualize outliers:
Dataset: Customer response times (in hours)
Range: C2:C50
Lower outlier threshold: =PERCENTILE.INC(C2:C50, 0.05)
Upper outlier threshold: =PERCENTILE.INC(C2:C50, 0.95)
Conditional formatting rule for outliers:
=OR(C2<PERCENTILE.INC($C$2:$C$50,0.05), C2>PERCENTILE.INC($C$2:$C$50,0.95))
Any response time below the 5th percentile or above the 95th percentile gets highlighted, drawing immediate attention to exceptional cases—both good and bad.
Common Mistakes and Troubleshooting
Using Percentages Instead of Decimals
The most frequent error looks like this:
=PERCENTILE.INC(A1:A100, 75) // WRONG - Returns #NUM! error
=PERCENTILE.INC(A1:A100, 0.75) // CORRECT
Excel expects k between 0 and 1. Typing 75 instead of 0.75 throws an error because 75 is outside the valid range.
#NUM! Errors with PERCENTILE.EXC
=PERCENTILE.EXC(A1:A10, 0) // ERROR - 0 not allowed
=PERCENTILE.EXC(A1:A10, 1) // ERROR - 1 not allowed
=PERCENTILE.EXC(A1:A10, 0.1) // ERROR - too few data points
=PERCENTILE.INC(A1:A10, 0) // WORKS - Returns minimum value
PERCENTILE.EXC requires k to be strictly between 0 and 1, and needs enough data points to calculate the requested percentile. With 10 data points, you cannot calculate the 5th percentile using the exclusive method.
Handling Empty Cells and Text
Data with gaps: 10, 20, [empty], 40, 50, "N/A", 60
=PERCENTILE.INC(A1:A7, 0.5) // Ignores empty cells and text, calculates on numeric values only
Excel automatically excludes blank cells and text from percentile calculations. This is usually helpful, but verify your dataset doesn’t have hidden non-numeric values that could skew your results.
Advanced Tips: Dynamic Percentile Calculations
Build interactive dashboards by referencing cells instead of hardcoding k values:
A B
1 Percentile Selector 0.75
2 Result =PERCENTILE.INC(Sales_Data, B1)
C D
1 Quick Select Percentile Value
2 25th Percentile =PERCENTILE.INC(Sales_Data, 0.25)
3 50th Percentile =PERCENTILE.INC(Sales_Data, 0.5)
4 75th Percentile =PERCENTILE.INC(Sales_Data, 0.75)
5 90th Percentile =PERCENTILE.INC(Sales_Data, 0.9)
6 95th Percentile =PERCENTILE.INC(Sales_Data, 0.95)
Add data validation to cell B1 (list: 0.25, 0.5, 0.75, 0.9, 0.95) and users can select different percentiles from a dropdown. The result updates automatically.
For sophisticated analysis, combine PERCENTILE with COUNTIFS to calculate percentile ranks within categories:
Employee data with departments:
=PERCENTILE.INC(IF($A$2:$A$50=A2,$B$2:$B$50),$D$1)
This array formula calculates percentiles within each department separately.
Enter with Ctrl+Shift+Enter in older Excel versions.
Create conditional formatting rules that adapt to data changes:
Highlight cells above 75th percentile:
=B2>PERCENTILE.INC($B$2:$B$100,0.75)
Highlight cells in the interquartile range (25th to 75th):
=AND(B2>=PERCENTILE.INC($B$2:$B$100,0.25), B2<=PERCENTILE.INC($B$2:$B$100,0.75))
These formulas recalculate automatically when your dataset changes, maintaining accurate highlighting without manual updates.
Percentiles transform spreadsheets from simple calculators into analytical tools. Master these functions, avoid the common decimal mistake, and you’ll extract insights that averages simply cannot provide. Start with PERCENTILE.INC, combine it with conditional logic, and build dashboards that make percentile analysis accessible to your entire team.