Excel Percentile Function: Rank, Syntax, and Examples

Formulas & functions · Updated November 18, 2026

Excel has separate functions for percentiles and percentile ranks: use PERCENTILE.INC or PERCENTILE.EXC to find the value at a chosen percentile, and PERCENTRANK.INC or PERCENTRANK.EXC to find a value’s relative position. If you mean an ordinary first-place, second-place ranking, use RANK.EQ instead; it answers a different question.

Percentile, percentile rank, and ordinary rank

These terms sound similar, but they describe three different results. A percentile is a value threshold in a data set. For example, the 80th percentile is a cutoff intended to leave about 80% of the observations at or below it, subject to the calculation method and interpolation between data points. A percentile rank is a relative position expressed as a fraction or percentage. An ordinary rank assigns positions such as first, second, or third.

That distinction helps identify the right Excel function:

  • Find the score at a percentile cutoff: use PERCENTILE.INC or PERCENTILE.EXC.
  • Find the percentile rank of a score: use PERCENTRANK.INC or PERCENTRANK.EXC.
  • Order results from highest to lowest, or lowest to highest: use RANK.EQ.

For instance, a test score of 42 might be at the 80th percentile in one group. The score 42 is the percentile threshold; 80% is its percentile rank. Neither statement means the score is “ranked number 80.” Percentile rank is not the same as a competition rank such as 8th out of 100.

How PERCENTILE.INC works

PERCENTILE.INC returns a value at a requested percentile, including the endpoints of the data set. Its second argument, k, is a proportion from 0 to 1: enter 0.8 for the 80th percentile, not 80. The function sorts the numeric observations conceptually and may interpolate between adjacent values, so its answer does not have to appear in the original list.

Assume the values 10, 20, 30, 40, and 50 are in A2:A6. With that cell layout, =PERCENTILE.INC(A2:A6,0.8) returns 42. There are five observations, and the inclusive method places the 80th percentile between the fourth and fifth values: 40 plus 20% of the gap to 50. The returned 42 is therefore a calculated threshold, even though 42 is not one of the five inputs.

At the endpoints, PERCENTILE.INC returns the minimum when k is 0 and the maximum when k is 1. In Microsoft 365, use the explicit PERCENTILE.INC name in new workbooks because it makes the chosen method clear. The older PERCENTILE function remains for compatibility and behaves like the inclusive version.

How PERCENTILE.EXC differs

PERCENTILE.EXC uses an exclusive calculation that does not treat the minimum and maximum as the 0th and 100th percentile outputs. This method can produce a different threshold from the inclusive method, especially for small data sets. For the same five values in A2:A6, =PERCENTILE.EXC(A2:A6,0.8) returns 48. The exclusive method places the requested point closer to the maximum than the inclusive method does.

The exclusive function requires k to be greater than 0 and less than 1. It can return #NUM! if the percentile argument is outside that range, or if the data set is too small to support the requested calculation. Use it when a particular statistical procedure calls for the exclusive definition, not simply because its name sounds more precise. For many everyday summaries, the inclusive version is easier to interpret because its endpoints match the observed minimum and maximum.

There is no universal rule that one method is always correct. Different fields and published methods can define percentile calculations differently. If you are comparing with a report, a test provider, or another software package, check which convention it uses before comparing numbers.

Find a value’s percentile rank

Use PERCENTRANK.INC when you have a value and want its relative location within an array. It returns a decimal between 0 and 1 for values within the data range; format the result as a percentage if that is easier to read. For the values 10, 20, 30, 40, and 50 in A2:A6, with 42 supplied as the value being evaluated, =PERCENTRANK.INC(A2:A6,42) returns 0.8. In other words, 42 is at the 80th percentile under the inclusive method.

PERCENTRANK.EXC uses the exclusive convention for percentile ranks. With those same five values, =PERCENTRANK.EXC(A2:A6,42) returns 0.7. This difference is not a contradiction: the functions map positions in the same data set onto percentile scales differently. Use the same convention consistently when comparing people, periods, or groups.

Both functions accept an optional significance argument that controls the number of significant digits in the returned decimal. If omitted, Excel uses 3. For example, the default may display a result such as 0.8 rather than a long decimal; cell formatting can separately control how many decimal places are visible. A value outside the array’s minimum-to-maximum range can produce #N/A, so check the input range and the score being evaluated if that error appears.

Choose the right function for your question

Before writing a formula, decide whether you need a cutoff, a relative position, or an ordinal position. These outputs are not interchangeable. A manager asking for the sales value that marks the top 10% needs a percentile cutoff. Someone asking what percentile a particular sale occupies needs a percentile rank. A leaderboard that labels the highest sale “1” needs an ordinary rank.

For a standard high-to-low ranking, assume scores are in A2:A6, with 95 in A2, 90 in A3 and A4, 80 in A5, and 70 in A6. With that layout, =RANK.EQ(A3,$A$2:$A$6,0) returns 2. The final argument 0 requests descending order, so the highest score ranks first. Both scores of 90 receive rank 2, and the next score receives rank 4: RANK.EQ assigns equal values the same rank and leaves a gap after ties.

For ascending order, use 1 as the final argument. If you need tied values to receive distinct positions, RANK.EQ alone does not do that; you need an additional tie-breaking rule, such as a second field or a unique identifier. Do not present percentile rank as a workaround for that problem, because it expresses relative position rather than a unique place in a sorted list.

Enter and format the formula in Excel

In Excel for Microsoft 365 on Windows, select a blank cell, type the formula beginning with an equals sign, and press Enter. Use commas between arguments in the formulas shown here; installations configured for a different list separator may display or require semicolons instead. The formula names and calculation methods are the same in Excel for Mac and Excel for the web, although the interface and regional settings can differ.

To display a percentile rank as a percentage, select the result cell and choose Home > Number > Percent Style. This changes how the decimal is displayed; it does not change the underlying value. For example, a stored result of 0.8 displays as 80%. If you need a specific number of decimal places, use the increase or decrease decimal controls in the Number group.

For a percentile cutoff, format the result as a number in the same units as the source data. If your source values are dollars, for example, the cutoff is also a dollar amount. Applying percentage formatting to a cutoff of 42 would make Excel display it as 4,200%, which is a formatting mistake rather than a different calculation.

Common errors and data issues

Percentile formulas are straightforward when the input range contains the intended numeric observations. Several common issues can change the result or produce an error:

  • Entering 80 instead of 0.8: the percentile argument is a fraction from 0 to 1. A value such as 80 is outside the valid range and causes an error.
  • Including the wrong cells: headers, totals, old records, or hidden assumptions in the range can change the result. Confirm the first and last cells in the reference.
  • Numbers stored as text: text entries may not be treated as numeric observations. Convert or correct them if they belong in the data set.
  • Too few observations for an exclusive calculation: PERCENTILE.EXC has stricter limits than the inclusive version and may return #NUM!.
  • Evaluating a score outside the observed range: percentile-rank functions can return #N/A for a value below the minimum or above the maximum.
  • Comparing results from different methods: inclusive and exclusive definitions can give different answers even when they use the same data.

Excel ignores text and logical values contained in a referenced array for these calculations, but errors in the referenced data can still affect the result. Blank cells are not observations. If you have missing values, decide whether they should be excluded or corrected before drawing conclusions. Do not replace missing measurements with zero unless zero is genuinely the measured value; doing so can pull a percentile downward.

Interpreting results responsibly

A percentile is a position in a particular set of observations, not a permanent label for an individual value. The same score can have a different percentile rank in a different group, time period, or sample. A 90th-percentile sales figure for one month cannot automatically be compared with another month if the underlying group or data quality changed.

Percentiles also do not describe the size of gaps between observations. A score at the 90th percentile is high relative to the group, but the function does not say whether it is only slightly higher than the median or far above it. For a fuller picture, consider the distribution, sample size, and relevant measures of spread alongside the percentile.

With small samples, interpolation can make a threshold look more exact than the data justify. In the five-value example, the result 42 is mathematically consistent with the selected method, but there are only five observed values. Explain the convention and sample size when reporting the result, particularly in a dashboard or a decision-making document.

Frequently Asked Questions

Is PERCENTILE the same as PERCENTRANK in Excel?
PERCENTILE returns a data value at a requested percentile. PERCENTRANK returns the relative percentile position of a supplied value. One produces a threshold; the other produces a proportion.

Should I use PERCENTILE.INC or PERCENTILE.EXC?
Use the method specified by the statistical definition or comparison you need to match. If there is no external requirement, the inclusive function is often convenient because it includes the data set’s endpoints. Do not mix the methods in one comparison without explaining why.

How do I calculate the 90th percentile in Excel?
Use PERCENTILE.INC with an array and 0.9 as the second argument for the inclusive method, or PERCENTILE.EXC with the same proportion for the exclusive method. Check that the range contains only the intended observations.

Why does my percentile formula return a decimal?
A percentile rank is a proportion, so 0.8 represents 80%. Apply percentage formatting to the result cell to display it as a percent. A percentile cutoff, by contrast, is a value in the units of your data and generally should not be percentage-formatted.

Does RANK.EQ give the same answer as a percentile rank?
No. RANK.EQ gives an ordinal position such as 2, while PERCENTRANK.INC or PERCENTRANK.EXC gives a relative position such as 0.8. Use the function that matches the question you are answering.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.