To calculate a percentile rank for every value in Excel, enter =PERCENTRANK.INC($A$2:$A$11,A2) in the first result cell, then fill the formula down. Format the results as percentages to read each value’s position on a 0%–100% scale; this calculates percentile rank, not the value at a chosen percentile.
What a percentile rank tells you
A percentile rank describes where a value falls relative to the values in a data set. For example, a result of 75% means Excel places that value around the 75th percentile of the selected data, using the inclusive calculation method. It is a way to compare an observation with the rest of a group, such as a test score, delivery time, or monthly sale.
In Microsoft Excel for Microsoft 365 on Windows, PERCENTRANK.INC returns a decimal from 0 to 1. A result of 0.75 is the same as 75% when displayed as a percentage. The function can also interpolate a rank for a number that lies between values in the data set, rather than requiring an exact match.
The distinction between a percentile rank and a percentile value is important. A percentile rank asks, “Where does this value sit in the data?” A percentile value asks, “What value marks this percentile?” Excel uses different functions for those questions.
Set up the data and enter the formula
Put the observations in one column, with one numeric value per row and a heading in the row above if you want one. Keep the range consistent: the formula should compare each observation against the full group, not just against the rows above it. A dollar sign locks the comparison range when you copy the formula.
- Place the numbers in A2:A11 and leave the header, if any, in A1.
- Click B2, next to the first number.
- Enter the formula below, then press Enter.
- Copy B2 down alongside all the values. You can drag the fill handle or double-click it if the neighboring column has continuous data.
- Select the results and choose Home > Number > Percent Style. Adjust the decimal places with the increase or decrease decimal buttons if needed.
With the full data set in A2:A11 and the value to evaluate in A2, use =PERCENTRANK.INC($A$2:$A$11,A2). The fixed range stays at A2:A11 as you fill down, while the second reference changes to A3, A4, and so on. The same formula works if the observations are in a row or in another column; update both references to match the layout.
In Excel for Mac, enter the same formula and use the Home tab’s number-format controls to apply Percentage. Excel for the web also supports this function; the ribbon labels and layout may differ slightly, but the formula syntax is the same.
Worked example with five values
Suppose A2:A6 contains 10, 20, 30, 40, and 50. To calculate a percentile rank for each observation, enter the following in B2 and fill through B6.
With the five values in A2:A6 and the current row’s value in A2, enter =PERCENTRANK.INC($A$2:$A$6,A2). The results are 0%, 25%, 50%, 75%, and 100%, respectively. For the value 30, Excel returns 0.5, displayed as 50%; it occupies the midpoint of this evenly spaced set.
The endpoints are useful checks. In the inclusive version, the smallest value in the range receives 0% and the largest receives 100%. When you fill the formula down, each row is evaluated against all five values. Changing the range to only the current row would make the comparison meaningless, which is why the reference to A2:A6 is absolute.
How Excel determines the rank
PERCENTRANK.INC calculates a relative position across the selected array, with both endpoints included. For a sorted set of five distinct numbers, the first and last values map to 0 and 1, and the three intervening values map to 0.25, 0.5, and 0.75. More generally, the function may return a fractional position when the requested number falls between two observations.
That calculation is not necessarily the same as taking the count of values below a score and dividing by the number of observations. Different definitions of percentile rank exist, especially around whether to include the value itself and how to handle ties. Excel’s function gives a consistent inclusive, interpolated measure, but the method should be stated when you report or compare results.
The optional third argument controls the number of significant digits in the returned rank; if omitted, Excel uses 3. It does not set how many decimal places appear on the worksheet. Number formatting controls the displayed decimals, while the function’s significance argument affects the calculated result. Most everyday worksheets can use the default and format the output as a percentage.
Choose the right Excel function
Use the function that matches the question you are trying to answer:
PERCENTRANK.INCcalculates the percentile rank of a particular value against a data range and includes the endpoints.PERCENTRANK.EXCcalculates rank using an exclusive method. It does not assign the endpoints 0% and 100%, so it may be unsuitable when those endpoint ranks are required.PERCENTILE.INCreturns the data value at a selected percentile, such as the score that marks the 90th percentile.RANK.EQreturns an ordinal rank, such as first, second, or third, rather than a percentile from 0 to 1.
For instance, if the question is “What is the 90th-percentile value in this sample?”, use a percentile-value function rather than calculating a rank for every row. With the data in A2:A11 and the desired percentile set to 0.9, enter =PERCENTILE.INC(A2:A11,0.9). The result is a value from the data’s scale, not a percentage rank. It can fall between observed values because Excel interpolates.
What happens with ties and repeated values
Repeated values receive the same percentile rank when their numeric values are identical, because the formula evaluates the value against the same comparison range. This can make several rows show the same percentage. That is expected and does not mean Excel has omitted a record. It does mean that the display will not always assign a unique position to every row.
For example, if several students have the same score, a percentile rank describes the score’s position in the score distribution; it does not break the tie by assigning each student a different rank. If the use case requires a unique ordered position, decide on a tie-breaking rule separately and document it. Adding a row number to a score or making arbitrary adjustments to scores can misrepresent the underlying data.
Ties also explain why a percentile rank may not equal the fraction of records strictly below the value. If a report needs that specific interpretation, define whether equal values count as below, at-or-below, or tied. A count-based calculation can answer a different question from PERCENTRANK.INC.
Common problems and how to fix them
- Every result is 0% or 100%: Check that the first argument points to the entire data set. A range that shrinks as the formula is copied can leave each row compared with the wrong subset. Use dollar signs around the range, as in
$A$2:$A$11. - The answer appears as 0.5 instead of 50%: The calculation is a decimal fraction. Apply Percentage formatting; the underlying numeric result remains suitable for further calculations.
- The formula returns an error: Confirm that the array range contains usable numeric observations and that the value being evaluated is numeric. Check that both references cover the intended cells and that the formula uses the function name spelled correctly.
- The result differs from a website or another spreadsheet: Compare the definition being used. Inclusive versus exclusive methods, interpolation, and tie handling can produce different answers even when the source data is identical.
- A value is outside the range: Do not assume the result will describe a meaningful rank within the observed group. Check whether the value and the comparison range belong to the same population and whether the value falls within the data’s minimum and maximum.
Also check for accidental omissions: filtering rows does not automatically mean the formula compares only visible records. A normal range reference includes its cells, including rows hidden by a filter. If you intend to rank only a particular subset, build the range to represent that subset rather than relying on the displayed rows alone.
Alternative: count the share at or below each value
Some readers use “percentile” to mean the percentage of observations that are less than or equal to a given value. That is an empirical proportion, and it can be calculated with COUNTIF. It differs from PERCENTRANK.INC, particularly when there are ties or a small sample, so use it only when that definition fits your reporting need.
With numeric observations in A2:A11 and the value for the current row in A2, enter =COUNTIF($A$2:$A$11,"<="&A2)/COUNT($A$2:$A$11). Format the result as a percentage. The numerator counts values at or below the current value, and the denominator counts numeric cells. If the value is the largest in the range, this method returns 100%; a repeated value includes all matching observations in the numerator.
For the five-value example 10, 20, 30, 40, 50, this method gives 60% for 30 because three of the five observations are at or below 30. PERCENTRANK.INC gives 50% for that same value. Neither result is automatically wrong: they answer different questions. Use the method that matches the definition expected by the audience, and label the column clearly.
Frequently Asked Questions
How do I calculate a percentile for each row in Excel?
Put the observations in one range, enter =PERCENTRANK.INC($A$2:$A$11,A2) beside the first observation, and fill down. Replace the example references with the locations of your data and format the results as percentages.
Is PERCENTRANK.INC the same as PERCENTILE.INC?
No. PERCENTRANK.INC returns a relative rank for a value, from 0 to 1. PERCENTILE.INC returns a value at a requested percentile. Choose based on whether you need a position or a threshold value.
Why does Excel show 0.75 instead of 75%?
The function returns a decimal fraction. Select the result cells and apply Percentage format from Home > Number > Percent Style. The displayed format changes; the numeric value remains 0.75.
Can percentile ranks be the same for multiple values?
Yes. Duplicate observations can share a percentile rank. The function measures the value’s position in the distribution and does not create a unique row-by-row order for ties.
Should I use PERCENTRANK.INC or PERCENTRANK.EXC?
Use the inclusive version when the minimum and maximum should map to 0% and 100%. Choose the exclusive version only when its endpoint convention matches the definition required for your analysis.
Does filtering the list change the percentile ranks?
Not automatically. A standard range reference still includes cells hidden by a filter. To rank a specific subset, make the formula’s data range contain that subset or otherwise calculate against the intended data only.
Related
- Attendance Point System Template Excel: Build and Use
- Attendance Excel Template: Free Options and Setup
- Attendance Log Excel Template: Build One That Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Lock Cells in Excel and Protect a WorksheetTo lock cells in Excel, select the cells, open Format Cells, check Locked, then go to Review >…
- Excel Dropdown Lists: How to Create and Manage ThemYes, Excel has dropdown lists. In Microsoft Excel for Microsoft 365 on Windows, you can create one with…
- Add Sequential Page Numbers in WordTo add sequential page numbers in Microsoft Word, choose Insert > Page Number, select where the numbers should…
- How to Make a Copy of a Sheet in ExcelTo copy a sheet in Excel for Microsoft 365 on Windows, right-click its sheet tab, choose Move or…
- Excel Sheet Password Remover: Safe Ways to Unlock ItIf you know the worksheet password, open the sheet in Excel for Microsoft 365 on Windows and choose…
- Protect Sheet in Excel: Allow Editing in Certain CellsYes. In Excel for Microsoft 365, you can protect a worksheet while leaving particular cells editable: unlock those…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.