Percentile Rank in Excel: Find and Format It

To find a value’s percentile rank in Excel, use =PERCENTRANK.INC($A$2:$A$6,B2), replacing the range with your comparison data and B2 with the value you want to rank. Format the result as a percentage: a result of 0.625 means the value is at the 62.5th percentile on Excel’s inclusive scale. Before starting, make sure the comparison values are numeric and that you know which data should count as the comparison group.

What You’ll Need

Excel for Microsoft 365 on Windows, or Excel for Mac or the web. The PERCENTRANK.INC and PERCENTRANK.EXC functions are available in current Excel versions.
A column or row of numeric comparison values, such as scores, times, or sales figures. Keep headers outside the selected range.
The value or values whose percentile ranks you want to calculate. These can be inside or outside the comparison list, though a value outside its minimum-to-maximum range returns an error with the inclusive function.

Step-by-Step

Arrange and check the data. Put the comparison group in one continuous range, with one numeric value per cell. For the example, enter 10, 20, 30, 40, and 50 in A2:A6. Put the value to evaluate, 35, in B2. Do not include a heading such as Score in the range. Remove stray spaces and check that numbers stored as text are converted to numbers; Excel may otherwise ignore them or produce an unexpected result. Blank cells are usually ignored, but it is still worth checking whether blanks indicate missing data that should be resolved first.
Choose the inclusive function for the usual percentile rank. With comparison data in A2:A6 and the value 35 in B2, enter this in C2: =PERCENTRANK.INC($A$2:$A$6,B2). The answer is 0.625. The dollar signs lock the comparison range, so it will stay fixed if you copy the formula to calculate ranks for other values. PERCENTRANK.INC includes the endpoints: the smallest value ranks at 0 and the largest at 1. It is the standard choice for most classroom, reporting, and descriptive-analysis tasks.
Display the result as a percentage. Select C2, then choose Home > Number > Percent Style. The cell displays 62.5% if the default percentage formatting shows one decimal place, or 63% if it is set to zero decimal places. You can use the Increase Decimal or Decrease Decimal buttons in the Number group to control how many places are visible. Formatting changes how the answer appears, not the underlying value: the cell still contains 0.625.
Check what the example means. The five comparison values have inclusive ranks of 0%, 25%, 50%, 75%, and 100%. The value 35 falls halfway between 30, which is at 50%, and 40, which is at 75%. Excel interpolates between those positions, returning 62.5%. This is why a value that is not present in the data can still receive a percentile rank. It is a position on Excel’s rank scale, not necessarily a literal count of the percentage of observations below the value.
Calculate ranks for more than one value. Put each value to evaluate in B2:B6, while keeping the comparison group in A2:A6. In C2, use =PERCENTRANK.INC($A$2:$A$6,B2), then drag the fill handle at the lower-right corner of C2 down through C6. The fixed comparison range remains A2:A6, while the relative reference changes from B2 to B3, B4, and so on. Apply percentage formatting to the output column. Check that the list in column A is the intended reference group; if you include new observations in the comparison group, the ranks may change.
Use the exclusive version only when the method calls for it. PERCENTRANK.EXC uses a scale that excludes the endpoints rather than assigning the minimum 0 and maximum 1. With comparison data in A2:A6 and the value 35 in B2, enter =PERCENTRANK.EXC($A$2:$A$6,B2) to get 0.6, or 60%. This differs from the inclusive result of 62.5%. Choose the exclusive method only when a textbook, statistical procedure, or reporting specification explicitly requires it; do not switch versions just to make a result look more familiar.
Set precision when needed. PERCENTRANK.INC can take an optional significance argument that controls the number of significant digits in the returned decimal. With comparison data in A2:A6 and the value 35 in B2, =PERCENTRANK.INC($A$2:$A$6,B2,3) returns 0.625 for this example. Most users can omit the argument and adjust the displayed decimal places with cell formatting instead. Remember that significant digits and decimal places are different: the optional argument affects the calculated result, while number formatting affects only its display.

Common Mistakes

Including the header in the range. A text heading such as Score does not belong in the comparison range. Select only the numeric cells, for example A2:A101 rather than A1:A101. Similarly, avoid selecting an entire column if it contains notes, totals, or unrelated numbers that should not participate in the ranking.

Confusing percentile rank with percentile value. Percentile rank asks where a given value sits in a dataset. A percentile value asks what value corresponds to a selected percentile. These are opposite questions and use different Excel functions. PERCENTRANK.INC calculates a rank for a value; it does not tell you the score at, for example, the 90th percentile.

Reading 0.625 as 0.625%. The raw result is a decimal fraction. It represents 62.5% when formatted as a percentage, not six-tenths of one percent. Use Percent Style rather than multiplying the formula by 100 and then also applying percentage formatting, which would inflate the displayed result.

Assuming the result is the exact share of records below the value. The function interpolates between observed values, and ties can affect how values are positioned. Therefore, a result such as 62.5% is a rank under Excel’s calculation method, not always a direct count of records. If a report needs a specific definition based on counts, confirm the required statistical convention before choosing a calculation.

Changing the reference range when copying. Without dollar signs, a formula copied down can shift the comparison data range, changing the group used for each result. Lock the comparison range, as in $A$2:$A$6, and leave the cell holding the value to rank relative, as in B2.

Mixing inclusive and exclusive results. The two functions can give different answers, particularly near the ends of the range. Use the same method consistently throughout a worksheet or report, and label the method if readers might otherwise assume a different convention.

Tips

Keep the comparison group consistent. If you are ranking students in one class, use only that class as the reference range. If you include multiple classes, locations, or time periods, the results answer a different question.
Use a clear label. Name the output column Percentile rank (inclusive) or Percentile rank (exclusive). That makes the method visible when someone reviews or reuses the workbook.
Check the endpoints. With PERCENTRANK.INC, the minimum and maximum values in the reference range should return 0 and 1 respectively. If they do not, inspect the range, the values, and the selected function.
Decide how to treat duplicate values. Repeated scores are valid inputs, but tied values share a position in the distribution. Do not remove duplicates merely to make results look evenly spaced unless the analysis specifically requires unique values.
Keep extra precision in the calculation. Formatting a result to show one decimal place does not round away the underlying value. If another formula uses the percentile rank, it can still use the stored precision. Use ROUND only if a later calculation or written specification requires actual rounding.
Use matching data types. Dates and times are stored as numbers in Excel and can be ranked, but ensure the entire comparison range uses the same kind of value. Avoid mixing dates with text labels or values measured in different units.

Troubleshooting

The formula returns #N/A. For PERCENTRANK.INC, check whether the value being ranked is smaller than the minimum or larger than the maximum in the comparison range. The function does not extrapolate beyond the range. Decide whether the value belongs in the comparison group, or whether the report should leave its rank unavailable. Also confirm that the formula refers to the intended cells and that the comparison range contains numeric values.

The formula returns #NUM!. This often points to an invalid input or an inappropriate exclusive endpoint. PERCENTRANK.EXC does not return ranks for the minimum or maximum endpoints, and it can return an error for values outside the range. Confirm that the range contains at least two usable numeric values and that the chosen function matches the calculation you need. If using the optional significance argument, enter a positive number.

Excel shows the formula instead of a result. Make sure the cell is not formatted as Text. Change its format to General, select the formula cell, press F2, and then press Enter to make Excel recalculate the entry. Also check that Show Formulas has not been turned on; press Ctrl+` on Windows to toggle it. On Mac, use Control+` where that key combination is available, or turn off Show Formulas from the Formulas tab.

The answer looks like 0.625 instead of 62.5%. The calculation is probably fine. Select the output cell and choose Home > Number > Percent Style. If the displayed percentage has too many or too few decimal places, use the decimal-place buttons in the same ribbon group.

The result is unexpected or inconsistent across rows. Click each formula cell and inspect the colored references in the formula bar. The comparison range should stay fixed, while the value-to-rank reference should move to the corresponding row. Look for text-formatted numbers, accidental blanks, hidden extra data in the range, and formulas that point to different comparison groups. If your Excel installation uses a regional setting with semicolons as function separators, Excel may require semicolons rather than commas when you enter a formula; use the separator your installation expects.

Excel does not recognize the function name. Confirm you have typed PERCENTRANK.INC or PERCENTRANK.EXC correctly, including the period and underscore. In older Excel workbooks, the legacy PERCENTRANK function may appear, but for a new worksheet use the explicit inclusive or exclusive version so the calculation method is clear.

Frequently Asked Questions

What does percentile rank mean in Excel?
It describes the relative position of a value within a comparison range. A result of 0.80, displayed as 80%, is the 80th percentile on the selected function’s scale. It does not automatically mean exactly 80% of the observations are lower, because Excel can interpolate and tied values affect interpretation.

Should I use PERCENTRANK.INC or PERCENTRANK.EXC?
Use PERCENTRANK.INC for the common inclusive scale, where the minimum is 0 and the maximum is 1. Use PERCENTRANK.EXC only if your instructions or chosen statistical method specifies the exclusive scale. They are not interchangeable, so name the method in your workbook.

Can I find a percentile rank for a number that is not in my list?
Yes. Excel interpolates between values in the comparison range. In the example, 35 is not listed, but it falls between 30 and 40 and receives an inclusive rank of 62.5%. The value must still fall within the range’s minimum and maximum for PERCENTRANK.INC to return a result.

Does the data need to be sorted?
No. PERCENTRANK.INC and PERCENTRANK.EXC calculate the rank from the numeric values in the range; you do not need to sort them first. Sorting can make the data easier to inspect, but it is not a prerequisite for the formula.

How do I calculate the percentile value instead of the rank?
That is a different task: you provide a percentile and ask Excel for the corresponding value in the dataset. For the inclusive method, use PERCENTILE.INC; for the exclusive method, use PERCENTILE.EXC. Check which convention your analysis requires rather than using a rank function for this reverse question.

Will the formula work in Excel for Mac or Excel for the web?
Yes, current Excel for Mac and Excel for the web support these functions. The formula structure is the same. Ribbon labels or keyboard shortcuts can differ slightly by platform, but you can format the result as a percentage from the Number formatting controls.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.