RANK Function in Excel
The RANK function returns the rank of a number within a list. Learn to rank data in ascending or descending order with examples.
Excel
Google SheetsExcelGoogle Sheets=RANK(number, ref, [order])Quick Answer
RANK function RANK function is a statistical function in Excel and Google Sheets that returns the rank of a number within a list of numbers. It assigns a position to each value based on its magnitude, where the largest value typically gets rank 1 (in descending order).
Practical Examples
Basic Sales Ranking
Rank sales representatives by their total quarterly revenue
Student Test Score Ranking
Determine class ranking based on final exam scores
Product Performance with Ascending Order
Rank products by cost to identify most economical options
Handling Tied Values
Understanding how RANK deals with duplicate scores
Dynamic Range with Table References
Using RANK with Excel Tables for automatic range expansion
Combining RANK with Conditional Formatting
Visual identification of top and bottom performers
Multi-Criteria Ranking Setup
Preparing data for complex ranking with primary and tiebreaker criteria
Common Errors and Solutions
RANK returns #N/A when the number is not found in the reference range
The value you're trying to rank doesn't exist in the range you're comparing against. This typically happens when the number parameter references a value that's not included in the ref parameter range.
Ensure the value being ranked is actually included in the reference range. For example, if ranking B2, make sure your range includes row 2 (like $B$2:$B$100 instead of $B$3:$B$100). Alternatively, use IFERROR to handle cases where values might not be in the range: =IFERROR(RANK(B2,$B$2:$B$100,0),"Not in range")
Always double-check that your reference range includes all values you want to rank. A common best practice is to include the header row number in your range to ensure coverage.
Example:
RANK returns #VALUE! when non-numeric values are used
Either the number parameter or the ref parameter contains text, dates formatted as text, or other non-numeric values. RANK requires numeric values for proper comparison.
1. Check the number parameter is actually numeric (not text that looks like a number) 2. Verify the reference range contains only numbers 3. Use VALUE() function to convert text to numbers if needed: =RANK(VALUE(B2),$B$2:$B$100,0) 4. Use ISNUMBER() to identify problematic cells
Format columns as 'Number' before entering data. When importing data from external sources, use Text-to-Columns feature (Data tab) to convert text-formatted numbers to actual numbers.
Example:
RANK returns #REF! when the reference range is invalid or deleted
The ref parameter points to cells that have been deleted, or the range reference is malformed. This can happen when rows/columns containing the reference range are deleted.
1. Rebuild the formula with a valid range reference 2. If using cross-sheet references, verify the source sheet still exists 3. Use named ranges for more stable references that survive sheet reorganization
Use named ranges or Excel Tables (structured references) instead of cell ranges. These are more resilient to sheet changes. Avoid deleting rows/columns that contain formula references.
Example:
Ranks appear correct but don't match expected order
The order parameter is set incorrectly for your use case. Using 0 (descending) when you wanted ascending order (1), or vice versa.
Review the order parameter: - Use 0 (or omit) when larger values should rank higher (sales, scores, revenue) - Use 1 when smaller values should rank higher (costs, times, error rates) Example fixes: - For costs: =RANK(B2,$B$2:$B$100,1) [1 = ascending, lowest cost = rank 1] - For sales: =RANK(B2,$B$2:$B$100,0) [0 = descending, highest sales = rank 1]
Before applying RANK to your entire dataset, test on a few rows and verify the rankings make sense. Create a test case with known values (like 100, 200, 300) to confirm the order parameter is correct.
Example:
Ranks skip numbers (e.g., 1, 2, 2, 4 instead of 1, 2, 2, 3)
This is actually correct behavior for RANK when there are tied values. When multiple items share the same rank, RANK assigns them all that rank number and skips the next rank(s).
This is normal RANK behavior and usually desired for competitions or leaderboards. However, if you need consecutive ranks without gaps: Option 1 - Use RANK.AVG (Excel 2010+): =RANK.AVG(B2,$B$2:$B$100,0) This averages tied ranks (two items tied for 2nd get 2.5 each, next is 4) Option 2 - Create a helper column with COUNTIF: =COUNTIF($B$2:B2,B2)+RANK(B2,$B$2:$B$100,0)-1 This creates unique sequential ranks even for ties
Understand that gaps in ranking are a feature, not a bug. They accurately represent competitive standings. Only use workarounds if you specifically need consecutive numbering.
Example:
Best Practices and Pro Tips
Always Use Absolute References for the Range
When creating RANK formulas that you'll copy down a column, always use absolute references (with $ signs) for the ref parameter. Use $B$2:$B$100 instead of B2:B100. This ensures that when you copy the formula down, each row compares against the same complete dataset.
Consider RANK.EQ for Future Compatibility
If you're working exclusively in Excel 2010 or later and don't need to share files with users on older versions, consider using RANK.EQ instead of RANK. It functions identically but provides more explicit documentation of how ties are handled.
Combine with COUNTIF for Percentage Rankings
To show what percentile someone falls into (like "top 10%" or "bottom 25%"), combine RANK with COUNTIF and basic arithmetic. Calculate the rank, then divide by the total count to get the percentage position.
Ranking Doesn't Update When You Filter Data
RANK always evaluates against the entire reference range, even if you've applied filters to hide some rows. If you filter a ranked list to show only top 10 performers, their ranks will still reflect their position in the complete dataset, not just the filtered view.
Use Named Ranges for Large or Dynamic Datasets
For datasets that frequently change size or need to be referenced in multiple formulas, define a named range (Formulas > Define Name). Instead of =RANK(B2,$B$2:$B$1000,0), use =RANK(B2,SalesData,0) where 'SalesData' is your named range.
Create Dynamic Top N Lists with RANK
To automatically show only the top 5 (or any number) ranked items, combine RANK with filtering or conditional formatting. Use conditional formatting to highlight rows where RANK <= 5, or use an IF formula in a helper column.
RANK vs LARGE vs PERCENTILE - When to Use Each
**Use RANK when:** You need the position/standing of specific values ("What place did I finish?"). **Use LARGE/SMALL when:** You need the actual value at a specific position ("What was the 3rd highest sale?"). **Use PERCENTILE when:** You need to find values at specific percentage thresholds ("What score represents the 75th percentile?").
Performance Considerations for Large Datasets
RANK can slow down significantly with very large datasets (>50,000 rows) because it must compare each value against the entire range. For big data scenarios, consider using RANK.EQ which has slight performance improvements, or pre-sorting data and using MATCH instead.
Handle Ties Strategically Based on Your Use Case
Different scenarios require different tie-breaking approaches: **Sports/Competition:** Keep ties and gaps (default RANK behavior) - two bronze medals, no 4th place **Academic Honor Roll:** Use RANK.AVG to assign average ranks to ties **Sales Contests:** Create tiebreaker column with secondary metric, combine with primary metric **Fair Distribution:** Use COUNTIF to create unique sequential ranks even for identical values
Need Help with RANK Function in Excel?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
Master the AVERAGE function with practical examples and error solutions. Learn to calculate mean values and analyze data efficiently in Excel and Sheets.
ExcelSheetsMaster the AVERAGEIF function to calculate conditional averages. Learn syntax, examples, and error solutions for Excel and Google Sheets.
ExcelSheetsMaster the BETA.INV function to calculate inverse beta distributions for statistical modeling, risk analysis, and project management in Excel and Google Sheets.
ExcelSheetsMaster the CHISQ.DIST function in Excel for chi-square probability distributions. Learn cumulative and probability density calculations with examples.
ExcelSheets