Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate a percentile rank in Excel and Google Sheets
Topic:RANK & statistical aggregatesExcel & Google Sheets
=PERCENTRANK($B$2:$B$6,B2)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your numeric data in column B (B2:B6).
- 2In column C2, enter =PERCENTRANK($B$2:$B$6,B2) and lock the range with $ signs.
- 3Copy the formula down for all data rows.
- 4Format column C as Percentage to display e.g. 75% meaning the value is at the 75th percentile.
Tips
- PERCENTRANK.INC (Excel) includes 0 and 1 in the output range; PERCENTRANK.EXC excludes them. PERCENTRANK is equivalent to PERCENTRANK.INC.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What is the difference between PERCENTRANK and PERCENTILE?
PERCENTRANK takes a value and returns its rank as a percentage; PERCENTILE takes a percentage and returns the corresponding value — they are inverses.
What does a percentile rank of 0.8 mean?
It means the value is higher than 80% of all values in the dataset.
More on RANK & statistical aggregates
See all →Reference
RANK.EQ
Returns the rank of a number in a list, assigning the same rank to ties (same as RANK). Essential for leaderboards.
How-toHow to rank values
Rank a number against a list with RANK — highest first or lowest first. Works the same in Excel and Google Sheets.
ReferenceRANK.AVG
Returns the rank of a number, averaging ranks for ties. Useful in statistical analysis where tied ranks should not cluster.
How-toHow to calculate standard deviation
Measure how spread out your numbers are with STDEV for a sample or STDEVP for a whole population. Works in Excel and Google Sheets.
ReferencePERCENTILE
Returns the k-th percentile value from a dataset. Useful for performance benchmarking and threshold analysis.
How-toHow to calculate a weighted average
Combine values and their weights with SUMPRODUCT divided by the total weight. Works the same in Excel and Google Sheets.