Introduction to Excel Percentile Formula
Percentiles help you analyze the relative standing of values in a dataset. With Excel’s built-in percentile formulas, you can quickly determine how a value compares to others in a range. Whether you are ranking student test scores, assessing income brackets or measuring performance metrics, Excel provides powerful functions to simplify the process 📊. This guide walks you through how to use the Excel Percentile Formula on Windows, macOS, and Ubuntu/Linux using either Microsoft Excel or LibreOffice.
What is a Percentile
A percentile indicates the position of a value in a dataset relative to other values. For example, the 90th percentile is the value below which 90 percent of the data falls.
- 25th percentile = first quartile
- 50th percentile = median
- 75th percentile = third quartile
Percentiles are useful for understanding distribution, outliers and rankings.
How Percentiles Help in Excel
Using percentiles in Excel allows you to:
- 🎓 Rank test scores
- 💰 Analyze salary ranges
- 📈 Identify outliers in sales data
- 🏆 Recognize top performers in large datasets
- 📋 Segment data for better insights
Excel makes percentile calculation fast and accurate.
Syntax of Excel Percentile Functions
Excel provides two main functions:
=PERCENTILE.INC(array, k)
Includes the 0th and 100th percentiles
=PERCENTILE.EXC(array, k)
Excludes the 0th and 100th percentiles
- array: Range of numeric values
- k: The percentile (between 0 and 1)
For example, k = 0.9 means the 90th percentile.
Differences Between PERCENTILE.INC and PERCENTILE.EXC
| Feature | PERCENTILE.INC | PERCENTILE.EXC |
|---|---|---|
| Includes 0 and 1 | ✅ Yes | ❌ No |
| 0 < k < 1 | ✅ Allows full range | ✅ Excludes extremes |
| Use case | General analysis | Statistical precision |
Use PERCENTILE.INC for most everyday tasks
Use PERCENTILE.EXC for more rigorous statistical analysis
Step-by-Step Guide to Calculate a Percentile
Let’s say you have scores in cells A2 to A11 and you want to find the 80th percentile.
=PERCENTILE.INC(A2:A11, 0.8)
- A2:A11 is your dataset
- 0.8 means the 80th percentile
- Result: the score above which only 20% of values lie
Practical Examples of Percentile Calculations
| Data (A2:A11) |
|---|
| 55 |
| 72 |
| 63 |
| 89 |
| 94 |
| 88 |
| 70 |
| 60 |
| 77 |
| 85 |
Formula:
=PERCENTILE.INC(A2:A11, 0.75)
📈 Result: 85
This means 75% of scores fall below 85.
Percentiles for Grading and Ranking
Percentiles are commonly used in:
- Standardized testing
- School report cards
- Academic placement
If a student is in the 90th percentile, they performed better than 90% of peers.
=IF(B2>=PERCENTILE.INC(B2:B100, 0.9), "Top 10%", "Others")
✅ Useful for classification and segmentation
Using Percentiles in Salary and Income Analysis
Percentiles help categorize:
- Top earners (90th percentile)
- Median earners (50th percentile)
- Entry-level salaries (25th percentile)
Formula:
=PERCENTILE.EXC(C2:C200, 0.9)
💼 Helps HR and finance teams make data-driven decisions
Calculating Percentile from a Named Range
If you name your range “Scores” then:
=PERCENTILE.INC(Scores, 0.6)
✅ Makes your formulas easier to read and maintain
🧠 Great for dashboards and templates
Creating a Percentile Chart in Excel
Visualize your percentile results:
- Sort your dataset
- Use
=PERCENTILE.INC()to calculate values - Plot data points on a scatter or line chart
📊 Useful for dashboards and reports
Using Percentile in Conditional Formatting
Highlight top 10%:
- Select your data range
- Go to Home > Conditional Formatting > New Rule
- Use formula:
=A2>=PERCENTILE.INC($A$2:$A$100, 0.9)
🎨 Apply formatting to instantly spot top values
Combining PERCENTILE with IF or AVERAGE
You can create logical formulas:
=IF(A2>=PERCENTILE.INC(A2:A100, 0.75), "Above Average", "Below Average")
Or calculate average of top 25%:
=AVERAGEIF(A2:A100, ">" & PERCENTILE.INC(A2:A100, 0.75))
📌 Great for custom reporting
Percentile for Performance Reviews and HR
Rank employees based on sales or reviews:
=IF(B2>=PERCENTILE.INC(B2:B50, 0.9), "Outstanding", "Regular")
Used in:
- 🧑💼 Employee evaluations
- 🏅 Incentive planning
- 📋 Talent segmentation
How to Interpret the Output of a Percentile
If a value is at the 85th percentile:
- It is higher than 85% of the other values
- Only 15% of values are greater
- It’s a relative performance indicator
✅ Helps in benchmarking and decision-making
Troubleshooting Percentile Formula Errors
| Error | Reason | Fix |
|---|---|---|
#NUM! | Invalid percentile (k not between 0 and 1) | Use valid range: 0 to 1 |
#VALUE! | Non-numeric data in array | Remove or clean text values |
#REF! | Range deleted or moved | Update the reference |
Use IFERROR() to handle gracefully:
=IFERROR(PERCENTILE.INC(A2:A100, 0.8), "Invalid Input")
Keyboard Shortcuts Across Windows macOS Ubuntu
| Action | Windows | macOS | Ubuntu/Linux |
|---|---|---|---|
| Insert Function | Shift + F3 | Shift + Fn + F3 | Ctrl + F2 (LibreOffice) |
| Edit Formula | F2 | Control + U | F2 |
| Auto-fill Formula | Ctrl + D | Command + D | Ctrl + D |
⏱️ Boost your productivity across systems
Using Percentiles in Google Sheets and LibreOffice
Both support:
=PERCENTILE()in Google Sheets=PERCENTILE.INC()in LibreOffice Calc
🌐 Great for cross-platform teams
FAQs About Excel Percentile Formula
What is the percentile formula in Excel?
Use =PERCENTILE.INC(range, k) to find the value below which k percent of data falls.
What is the difference between PERCENTILE.INC and PERCENTILE.EXC?.INC includes 0 and 1. .EXC excludes them and is more suitable for statistical modeling.
Can I use text in percentile arrays?
No. The array must contain only numeric values.
Does percentile work on macOS and Linux?
Yes. Available on all modern Excel versions and in LibreOffice on Ubuntu.
What is a good use case for percentiles?
Grading systems, income analysis, performance reviews and data segmentation.
Final Thoughts on Excel Percentile Use
The Excel percentile formula is a versatile tool that provides powerful insights into how data is distributed. With just a few keystrokes, you can uncover outliers, rank performance or set thresholds. Whether you’re analyzing salaries, test scores or performance metrics, Excel offers a reliable and consistent way to work with percentiles on Windows, macOS, and Ubuntu/Linux.
Mastering percentiles improves your ability to analyze and communicate data effectively 🎯.
Complete List of Windows Keyboard Shortcuts
If you need help for Windows, you can find a whole list of all keyboard shortcuts here.
https://keyboard-shortcuts.org/
Advertisment