How to calculate the rank percentile of a list in Excel?
When working with data in Excel, assessing each value’s position relative to the rest of the data can be crucial for analysis, reporting, or visualization. For instance, if you frequently use Conditional Formatting’s Icon Set, you may have noticed these icons are assigned based on the percentile of each value rather than their direct ranking. Understanding how to calculate the rank percentile of each value in an Excel list can help you make more informed business or academic decisions, such as identifying top and bottom performers or segmenting data into quartiles.
Calculate rank percentile with PERCENTRANK function
Calculate rank percentile with PERCENTRANK function
Excel includes dedicated functions for calculating the percentile rank of a value within a range: PERCENTRANK, PERCENTRANK.INC, and PERCENTRANK.EXC. These functions are more precise and widely accepted in academic or statistical contexts for percentile calculations, as they are specifically built to determine how a value compares to the rest of the data set.
- PERCENTRANK (Excel 2007-2010): Calculates the percentile rank, including the lowest and highest value.
- PERCENTRANK.INC (Excel 2010+): The improved version, fully compatible and includes endpoints of data set.
- PERCENTRANK.EXC (Excel 2010+): Excludes the lowest and highest values and may be used for larger datasets or particular statistical treatments.
These functions are especially useful for data analysis projects where you need percentile ranks consistent with statistical standards or when sharing results with others who expect percentile-based grading or assessment.
1. In an empty cell next to your dataset (e.g., enter in cell C2), input one of the following formulas to find the percentile rank for the value in B2:
=PERCENTRANK.INC($B$2:$B$9, B2)Alternatively, you can use:
=PERCENTRANK.EXC($B$2:$B$9, B2)Or for older Excel versions:
=PERCENTRANK($B$2:$B$9, B2)where $B$2:$B$9 is your range of values and B2 is the specific value whose percentile you want to determine. PERCENTRANK.INC generally works best for most purposes.
2. Press Enter to calculate the percentile rank. To apply the formula to the whole dataset, drag the fill handle down alongside your list.
These functions return a decimal value between 0 and 1 (for example,0.83), representing the relative standing of that value in your range. To display the result as a percentage, select the result cells and choose the Percent Style format in Excel’s Home tab. If needed, increase or decrease the number of decimal places for clarity.
Parameter Explanation: "array" is the actual list you want to evaluate (such as $B$2:$B$9), and "x" is the individual value whose percentile rank is being calculated (such as B2 in each row). The functions interpolate if a value does not exactly match one in the range.
Pros: Offers standardized, statistically recognized calculation; compatible with complex datasets; interpolates percentile for values that fall between actual data points.
Cons: May not be as intuitive or transparent as the manual rank-based formula above, especially for new Excel users.
Tips: Use the INC version for most scenarios unless you specifically require the method used by EXC. Ensure data range and references are fixed with $ signs to prevent unexpected changes when copying formulas.
Troubleshooting: If you encounter #NUM! errors, check for non-numeric data or confirm that the referenced value exists within the evaluation range. Empty or invalid cells in the selected range can cause these errors.
Summary Suggestion: The PERCENTRANK function family provides robust, precise percentile calculations following statistical norms. For standardized analysis, especially in academic or business settings, these functions are generally preferred.
You maybe interested in this utility:
Convert Numbers to Ordinals Easily |
With Kutools for Excel's Convert Number to Ordianl feature, you can quickly transform cardinal numbers into ordinal numbers in just a few clicks, saving you time and effort in Excel. |
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now |
 |
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in