How to Calculate Percentiles in Excel
Percentiles divide your data into 100 equal parts, telling you what percentage of values fall below a given point. The 90th percentile means 90% of your data points are at or below that value. This...
Key Insights
- Excel offers two percentile functions—PERCENTILE.INC and PERCENTILE.EXC—that produce different results for the same data, and choosing the wrong one can skew your analysis
- PERCENTRANK functions work in reverse, telling you where a specific value falls within your dataset, which is essential for benchmarking individual data points
- Combining percentile calculations with named ranges and other statistical functions creates dynamic dashboards that update automatically as your data changes
Introduction to Percentiles
Percentiles divide your data into 100 equal parts, telling you what percentage of values fall below a given point. The 90th percentile means 90% of your data points are at or below that value. This isn’t just academic—percentiles are the backbone of practical data analysis.
Consider these real scenarios: HR departments use percentiles to ensure salary offers are competitive (targeting the 50th or 75th percentile of market rates). Healthcare providers use percentiles to track child growth against population norms. DevOps teams monitor API response times at the 95th and 99th percentiles because averages hide the pain points that affect real users.
Unlike averages, percentiles resist distortion from outliers. If your CEO’s salary is in your compensation dataset, the average becomes meaningless, but the median (50th percentile) still tells you what a typical employee earns.
PERCENTILE.INC vs PERCENTILE.EXC Functions
Excel provides two percentile functions, and they’re not interchangeable. Understanding the difference prevents subtle but significant errors in your analysis.
PERCENTILE.INC (inclusive) interpolates between values and can return the minimum (0th percentile) and maximum (100th percentile) of your dataset. It uses the formula where k can range from 0 to 1.
PERCENTILE.EXC (exclusive) excludes the endpoints. The k value must be between 1/(n+1) and n/(n+1), where n is the number of data points. This method aligns with statistical software like R and is preferred in academic research.
Here’s the practical difference:
' Sample dataset in cells A1:A10
' Values: 10, 20, 30, 40, 50, 60, 70, 80, 90, 100
' Inclusive calculation (25th percentile)
=PERCENTILE.INC(A1:A10, 0.25)
' Result: 32.5
' Exclusive calculation (25th percentile)
=PERCENTILE.EXC(A1:A10, 0.25)
' Result: 27.5
' The difference becomes more pronounced at extremes
=PERCENTILE.INC(A1:A10, 0.1)
' Result: 19
=PERCENTILE.EXC(A1:A10, 0.1)
' Result: 15
When to use each:
- Use PERCENTILE.INC for business applications where you need to include boundary values and want results consistent with Excel’s legacy PERCENTILE function
- Use PERCENTILE.EXC for statistical analysis that needs to match academic standards or other statistical software
My recommendation: Pick one and stick with it throughout your analysis. Mixing methods creates inconsistent results that undermine your conclusions.
Basic Percentile Calculations
Let’s walk through calculating the most commonly used percentiles. Assume your data is in column A, rows 2 through 101 (100 data points).
' Quartiles (dividing data into four equal parts)
=PERCENTILE.INC(A2:A101, 0.25) ' Q1 - First quartile
=PERCENTILE.INC(A2:A101, 0.50) ' Q2 - Median (second quartile)
=PERCENTILE.INC(A2:A101, 0.75) ' Q3 - Third quartile
' Common performance percentiles
=PERCENTILE.INC(A2:A101, 0.90) ' 90th percentile
=PERCENTILE.INC(A2:A101, 0.95) ' 95th percentile
=PERCENTILE.INC(A2:A101, 0.99) ' 99th percentile
' Deciles (dividing data into ten equal parts)
=PERCENTILE.INC(A2:A101, 0.1) ' 10th percentile
=PERCENTILE.INC(A2:A101, 0.2) ' 20th percentile
' ... and so on
For a complete quartile analysis, set up your worksheet like this:
' Cell B1: "Statistic" Cell C1: "Value"
' Cell B2: "Minimum" Cell C2: =MIN(A2:A101)
' Cell B3: "Q1 (25th)" Cell C3: =PERCENTILE.INC(A2:A101, 0.25)
' Cell B4: "Median" Cell C4: =PERCENTILE.INC(A2:A101, 0.5)
' Cell B5: "Q3 (75th)" Cell C5: =PERCENTILE.INC(A2:A101, 0.75)
' Cell B6: "Maximum" Cell C6: =MAX(A2:A101)
' Cell B7: "IQR" Cell C7: =C5-C3
The interquartile range (IQR) in row 7 is particularly useful for identifying outliers. Values below Q1 - 1.5×IQR or above Q3 + 1.5×IQR are typically considered outliers.
Calculating Percentile Rank with PERCENTRANK
While PERCENTILE tells you the value at a given percentile, PERCENTRANK does the opposite—it tells you where a specific value ranks within your dataset.
' Find the percentile rank of a specific value
' Dataset in A2:A101, value to rank in B2
=PERCENTRANK.INC(A2:A101, B2)
' Example: Student scores in A2:A101
' John's score of 85 in cell B2
=PERCENTRANK.INC(A2:A101, 85)
' Result: 0.78 (John scored better than 78% of students)
' Convert to percentage for readability
=PERCENTRANK.INC(A2:A101, B2) * 100
' Result: 78
You can also specify significance (decimal places):
' Default significance is 3 decimal places
=PERCENTRANK.INC(A2:A101, B2)
' Result: 0.784
' Specify 2 decimal places
=PERCENTRANK.INC(A2:A101, B2, 2)
' Result: 0.78
This function is invaluable for benchmarking. If you’re comparing an individual’s performance against a group, PERCENTRANK gives you immediate context.
Practical Applications
Salary Benchmarking Analysis
Here’s a complete salary analysis template:
' Column A: Employee names (A2:A51)
' Column B: Current salaries (B2:B51)
' Column C: Department (C2:C51)
' Summary Statistics (in cells E2:F10)
' E2: "Metric" F2: "Value"
' E3: "Count" F3: =COUNT(B2:B51)
' E4: "Average" F4: =AVERAGE(B2:B51)
' E5: "10th Percentile" F5: =PERCENTILE.INC(B2:B51, 0.1)
' E6: "25th Percentile" F6: =PERCENTILE.INC(B2:B51, 0.25)
' E7: "Median" F7: =PERCENTILE.INC(B2:B51, 0.5)
' E8: "75th Percentile" F8: =PERCENTILE.INC(B2:B51, 0.75)
' E9: "90th Percentile" F9: =PERCENTILE.INC(B2:B51, 0.9)
' E10: "Spread (IQR)" F10: =F8-F6
' Individual employee ranking (Column D)
' D1: "Percentile Rank"
' D2: =PERCENTRANK.INC($B$2:$B$51, B2)
' Copy D2 down for all employees
API Response Time Analysis
For DevOps teams monitoring performance:
' Response times in milliseconds (A2:A1001)
' Key metrics for SLA monitoring
' B2: "P50 (Median)" C2: =PERCENTILE.INC(A2:A1001, 0.5)
' B3: "P90" C3: =PERCENTILE.INC(A2:A1001, 0.9)
' B4: "P95" C4: =PERCENTILE.INC(A2:A1001, 0.95)
' B5: "P99" C5: =PERCENTILE.INC(A2:A1001, 0.99)
' Count of requests exceeding SLA (500ms threshold)
' B7: "Over SLA" C7: =COUNTIF(A2:A1001, ">500")
' B8: "SLA Compliance" C8: =1-(C7/COUNT(A2:A1001))
Common Errors and Troubleshooting
#NUM! Error This occurs when:
- The k value is outside the valid range (less than 0 or greater than 1 for PERCENTILE.INC)
- The array is empty
- For PERCENTILE.EXC, k must be between 1/(n+1) and n/(n+1)
' This will cause #NUM! error
=PERCENTILE.INC(A2:A101, 1.5)
' This will also error with PERCENTILE.EXC on small datasets
=PERCENTILE.EXC(A2:A5, 0.1) ' Only 4 values, 0.1 < 1/5
Handling Empty Cells PERCENTILE functions ignore empty cells, which is usually what you want. However, cells containing zero are included:
' If A2:A10 contains: 10, 20, [empty], 40, 0, 60, 70, 80, 90, 100
=PERCENTILE.INC(A2:A10, 0.5)
' Calculates median of: 0, 10, 20, 40, 60, 70, 80, 90, 100 (9 values)
Non-Numeric Data Text values cause #VALUE! errors. Clean your data first:
' Check for non-numeric values
=SUMPRODUCT(--(ISNUMBER(A2:A101)))
' Should equal COUNT(A2:A101) if all values are numeric
Tips for Large Datasets
Use Named Ranges
Named ranges make formulas readable and maintainable:
' Define a named range: Formulas > Define Name
' Name: SalesData
' Refers to: =Sheet1!$A$2:$A$10001
' Now use in formulas
=PERCENTILE.INC(SalesData, 0.75)
' For dynamic ranges that grow with your data:
' Name: DynamicSales
' Refers to: =OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A)-1, 1)
Dynamic Percentile Dashboard
Create a dashboard where users can input any percentile value:
' User input cell for percentile (D1): 75
' Convert to decimal (D2): =D1/100
' Dynamic result (D3):
=PERCENTILE.INC(SalesData, D2)
' With error handling:
=IFERROR(PERCENTILE.INC(SalesData, D2), "Enter value 0-100")
Combining with Other Functions
' Compare percentile to median
=PERCENTILE.INC(A2:A1001, 0.75) - MEDIAN(A2:A1001)
' Use QUARTILE for standard quartiles (slightly faster)
=QUARTILE.INC(A2:A1001, 1) ' Same as PERCENTILE.INC(..., 0.25)
=QUARTILE.INC(A2:A1001, 2) ' Same as MEDIAN
=QUARTILE.INC(A2:A1001, 3) ' Same as PERCENTILE.INC(..., 0.75)
' Conditional percentile (requires array formula in older Excel)
' 75th percentile of sales over $1000
=PERCENTILE.INC(IF(A2:A1001>1000, A2:A1001), 0.75)
For datasets exceeding 100,000 rows, consider whether Excel is the right tool. At that scale, database solutions or statistical programming languages (Python, R) offer better performance and more sophisticated analysis options.
Percentiles are foundational to meaningful data analysis. Master these functions, and you’ll extract insights that averages simply cannot provide.