How to Use the PERCENTRANK Function in Microsoft Excel to Calculate Relative Percentile Ranks

When evaluating performance distributions—such as analysing national examination test scores, ranking company sales representatives, comparing employee annual compensation bands, or assessing customer survey ratings—knowing an absolute numeric score rarely tells the whole story. Scoring 82 out of 100 might represent an average grade on an easy test, or an exceptional performance in the top 5% on a challenging exam. To determine exactly where an individual data point stands relative to its broader group, statisticians calculate percentile ranks.

In Microsoft Excel, the PERCENTRANK function family calculates the relative rank of a value within a dataset expressed as a percentage between 0 and 1 (or 0% to 100%). By converting raw figures into percentile rankings, you can identify top performers, establish grading thresholds, and compare disparate metrics on equal footing. This tutorial breaks down the syntax of PERCENTRANK, highlights critical differences between PERCENTRANK.INC and PERCENTRANK.EXC, provides practical step-by-step examples, and covers best practices for formula construction.

Understanding the PERCENTRANK Function Family in Excel

In modern versions of Microsoft Excel (Microsoft 365, Excel 2021, 2019, 2016, and 2013), Microsoft split the legacy PERCENTRANK function into two specific statistical formulas to adhere to international standards:

• PERCENTRANK.INC (Inclusive): Calculates the rank of a value as a percentage between 0 and 1 inclusive ($0 \le \text{rank} \le 1$). In this model, the lowest observation in the dataset receives a rank of exactly 0% (0.0), and the highest observation receives exactly 100% (1.0). This is the default standard for business reporting and school grade grading.
• PERCENTRANK.EXC (Exclusive): Calculates the rank as a percentage strictly between 0 and 1 exclusive ($0 < \text{rank} < 1$). In this model, neither the lowest nor the highest values can achieve 0% or 100%. It is preferred in advanced statistical modelling where population extremes should not hit theoretical boundary caps.
• PERCENTRANK (Legacy): Retained purely for backwards compatibility with legacy Excel 2007 and earlier workbooks. It functions identically to PERCENTRANK.INC. Modern spreadsheets should always use PERCENTRANK.INC.

Function Syntax and Arguments

Both PERCENTRANK.INC and PERCENTRANK.EXC share the same three-argument syntax:

=PERCENTRANK.INC(array, x, [significance])

=PERCENTRANK.EXC(array, x, [significance])

Argument Breakdown

• array (Required): The array or range of numeric data that defines the reference population (e.g., $B$2:$B$25).
• x (Required): The specific value or cell reference whose percentile rank you want to calculate.
• significance (Optional): The number of significant digits for the returned percentage value. If omitted, Excel defaults to 3 significant digits (e.g., 0.825 or 82.5%).

Step-by-Step Practical Example: Ranking Student Test Scores

Suppose you have a list of student exam scores stored in cells B2:B16. You want to calculate each student’s inclusive percentile rank in column C.

Step 1: Construct the Formula

1. Click into cell C2, directly adjacent to the first student’s score.
2. Enter the following formula, locking the array range with dollar signs:

   =PERCENTRANK.INC($B$2:$B$16, B2)

3. Press Enter. Excel will return a decimal value (for example, 0.714).

Step 2: Format as a Percentage

1. With cell C2 highlighted, navigate to the Home tab on the Excel ribbon.
2. In the Number section, click the Percent Style (%) button (or press Ctrl + Shift + %).
3. Click the Increase Decimal button once to display one decimal place (e.g., 71.4%).

Step 3: Copy Formula Down the Column

Double-click the small square fill handle at the bottom-right corner of cell C2 to copy the formula down to row 16. Because $B$2:$B$16 is locked with absolute referencing, each student’s relative standing is evaluated against the entire class cohort.

Comparing PERCENTRANK.INC vs PERCENTRANK.EXC

To understand when to choose one formula over the other, observe how they evaluate a 5-item dataset: {10, 20, 30, 40, 50}:

Value (x)	PERCENTRANK.INC Result	PERCENTRANK.EXC Result	Interpretation
10 (Minimum)	0.000 (0.0%)	0.166 (16.6%)	INC pins bottom at 0%; EXC offsets based on sample size.
30 (Median)	0.500 (50.0%)	0.500 (50.0%)	Both formulas converge on the exact median.
50 (Maximum)	1.000 (100.0%)	0.833 (83.3%)	INC pins top at 100%; EXC leaves room for theoretical tail.

Evaluating Values Not Present in the Array (Interpolation)

A major strength of PERCENTRANK is its ability to evaluate a target value that does not appear in your original dataset. Excel automatically performs linear interpolation between adjacent ranks:

=PERCENTRANK.INC($B$2:$B$16, 77)

If 75 corresponds to the 60th percentile and 80 corresponds to the 70th percentile, Excel smoothly calculates the estimated percentile for 77 (returning approximately 64%).

Troubleshooting Common Errors with PERCENTRANK

If your formula returns an error, examine these frequent root causes:

• #N/A Error in PERCENTRANK.EXC: If your target value x is less than the smallest value in the dataset, or greater than the largest value in the dataset, PERCENTRANK.EXC produces #N/A because exclusive percentiles cannot evaluate out-of-bounds observations. Use PERCENTRANK.INC if you need boundary handling.
• #NUM! Error: Occurs if the supplied significance argument is less than 1 ($< 1$), or if the reference array contains fewer data points than required to calculate an exclusive rank ($n < 3$ in EXC mode).
• #VALUE! Error: Returned if the target value x or values inside your array contain text characters or non-numeric strings that Excel cannot interpret as numbers.