RANK.EQ Formula in Excel: Rank Your Data Accurately
=RANK.EQ(number, ref, [order])The RANK.EQ function is a powerful statistical tool in Excel that determines the rank of a specific number within a dataset. Whether you're analyzing sales performance, student grades, or competitive standings, RANK.EQ provides a straightforward method to identify where a value falls within your range of data. This formula is essential for anyone working with comparative analysis, performance metrics, or any scenario where ranking data matters. Rank.EQ stands for "Rank Equal" and handles tied values by assigning them the same rank, with the next rank adjusted accordingly. Unlike RANK.AVG which averages ranks for ties, RANK.EQ offers deterministic ranking that's ideal for leaderboards, performance reviews, and statistical analysis. Available since Excel 2010, this function has become the standard approach for ranking tasks across all modern Excel versions, including Excel 365. Understanding RANK.EQ is crucial for data analysts, business professionals, and anyone managing competitive or comparative datasets. The formula's flexibility with ascending and descending order options makes it adaptable to various business scenarios and analytical requirements.
Syntax & Parameters
The RANK.EQ function uses the syntax =RANK.EQ(number, ref, [order]), where each parameter plays a specific role in determining your ranking result. The first parameter, 'number', is the required value you want to rank within your dataset. This can be a cell reference like B5 or a direct numeric value. The second parameter, 'ref', is your reference range containing all values against which the ranking occurs—typically a column or row of data like B$2:B$100 with absolute references to prevent shifts during copying. The optional 'order' parameter controls ranking direction: enter 0 (or omit it) for descending order, where the largest value receives rank 1, or enter 1 for ascending order, where the smallest value receives rank 1. This flexibility adapts to your analytical needs—use descending for sales figures or scores where higher is better, and ascending for times or costs where lower is better. When values are tied, RANK.EQ assigns identical ranks to all tied values, then skips the next sequential rank number. For example, if two values tie for rank 2, the next rank becomes 4, not 3. Always use absolute references for your range ($B$2:$B$100) when copying formulas down to maintain consistent comparison against the entire dataset.
numberreforderPractical Examples
Sales Performance Ranking
=RANK.EQ(B2,$B$2:$B$13,0)This formula ranks the sales value in B2 against all sales figures in B2:B13 using descending order (0), so the highest sales value receives rank 1. The absolute references ($B$2:$B$13) ensure that when copied down, each row compares against the complete sales range.
Race Completion Times
=RANK.EQ(C3,$C$3:$C$22,1)Using ascending order (1), this formula ranks completion times so the fastest time (lowest value) receives rank 1. This is essential for time-based competitions where lower values represent better performance.
Student Grade Rankings
=RANK.EQ(D5,$D$5:$D$34,0)With descending order (0), highest scores rank first. This common educational use case helps identify honor roll students and those requiring intervention. The formula automatically handles tied scores by assigning identical ranks.
Key Takeaways
- RANK.EQ determines a value's position within a dataset using descending (0) or ascending (1) order, with ties receiving identical ranks
- Always use absolute references ($B$2:$B$100) for the range parameter to maintain consistent comparison when copying formulas
- RANK.EQ differs from RANK.AVG by assigning identical ranks to tied values, then skipping the next rank number
- Combine RANK.EQ with IF, IFERROR, or COUNTIFS to handle edge cases, errors, and conditional ranking requirements
- RANK.EQ is available in Excel 2010 and later, including 365, and offers superior functionality to the legacy RANK function
Pro Tips
Use mixed references for flexible ranking: =RANK.EQ(B2,$B$2:$B$100,0) locks the range but allows the number parameter to change. This enables copying right and down while maintaining correct comparisons.
Impact : Reduces formula editing time and prevents ranking errors when scaling across multiple dimensions
Combine RANK.EQ with COUNTIF to create percentile rankings: =RANK.EQ(B2,$B$2:$B$100,0)/COUNTA($B$2:$B$100). This shows what percentage of values rank below your number.
Impact : Transforms absolute rankings into relative performance metrics more meaningful for stakeholder communication
For dynamic ranking that updates when data changes, ensure your range includes buffer rows: use $B$2:$B$1000 even if currently only 100 rows contain data. New entries automatically rank correctly.
Impact : Eliminates need to update formulas when datasets grow, maintaining formula integrity in growing datasets
Use RANK.EQ with conditional formatting to create color-coded performance dashboards: apply green to rank 1-5, yellow to 6-10, red to 11+. This provides instant visual feedback on performance tiers.
Impact : Transforms raw ranking data into actionable visual intelligence for executive dashboards and reports
Useful Combinations
Conditional Ranking with IF
=IF(B2="","",RANK.EQ(B2,$B$2:$B$100,0))This combination prevents RANK.EQ from processing empty cells, which would return #N/A. The IF statement checks if B2 is blank and returns empty string if true, otherwise performs the ranking. Essential for datasets with incomplete rows.
Ranking with Error Handling
=IFERROR(RANK.EQ(B2,$B$2:$B$100,0),"Not in range")Wrapping RANK.EQ in IFERROR catches #N/A errors when a value doesn't exist in the range and displays a user-friendly message instead. This improves data quality reporting and prevents formula errors from disrupting dashboards.
Top 10 Filter with Rank
=IF(RANK.EQ(B2,$B$2:$B$100,0)<=10,B2,"")This combination identifies and displays only the top 10 ranked values, returning empty cells for all others. Perfect for creating filtered leaderboards or highlighting top performers in conditional formatting rules.
Common Errors
Cause: The reference range was deleted, moved, or the worksheet containing it was removed. This occurs when the $B$2:$B$100 range no longer exists or the file structure changed.
Solution: Verify the reference range still exists and contains data. Update the formula with the correct range. Use Ctrl+H to find and replace broken references across multiple cells if needed.
Cause: The 'number' parameter contains text instead of a numeric value, or the 'order' parameter is set to something other than 0 or 1 (like 2 or TRUE/FALSE).
Solution: Ensure the number parameter references a cell with numeric data. Verify order is either 0, 1, or omitted. Use VALUE() function to convert text numbers: =RANK.EQ(VALUE(B2),$C$2:$C$100,0)
Cause: The number being ranked doesn't exist in the reference range, or the range is empty. This creates an undefined ranking situation.
Solution: Verify the value in your 'number' parameter actually exists within the 'ref' range. Check for hidden rows or filtered data that might exclude the value. Use IFERROR to handle missing values gracefully: =IFERROR(RANK.EQ(B2,$B$2:$B$100,0),"Not found")
Troubleshooting Checklist
- 1.Verify the 'number' parameter references a cell containing numeric data, not text or formulas returning errors
- 2.Confirm the 'ref' range uses absolute references ($B$2:$B$100) and includes all data rows needed for accurate ranking
- 3.Check that the 'order' parameter is either 0 (descending), 1 (ascending), or omitted—not other values like TRUE/FALSE
- 4.Ensure no error values (#DIV/0!, #VALUE!) exist within the reference range that would cause RANK.EQ to fail
- 5.Verify the reference range doesn't contain blank cells that should be excluded; use IFERROR to handle edge cases
- 6.Test the formula on a sample row first, then copy down to verify results match expected ranking logic
Edge Cases
All values in the range are identical
Behavior: RANK.EQ assigns rank 1 to all values, as they all tie for the top position
This is expected behavior; use COUNTIF to identify duplicate values if this is problematic for your analysis
The number parameter contains a decimal value not exactly matching any range value
Behavior: RANK.EQ still ranks the number correctly against the range based on its position in the sorted order, even if it doesn't exist in the range
Solution: Use MATCH to verify if the exact value exists, or COUNTIF to find approximate matches
This is useful for ranking calculated values or external data points against your dataset
The reference range contains only one cell (B2:B2)
Behavior: RANK.EQ returns 1 if the number equals that cell value, or #N/A if it doesn't
Solution: Ensure your range contains multiple values for meaningful ranking; single-cell ranges don't produce useful results
This edge case typically indicates a formula error; verify range selection during formula creation
Limitations
- •RANK.EQ cannot rank text values or mixed data types; it only works with numeric data. Text entries return #VALUE! errors
- •The function doesn't provide context about what each rank means; you must manually interpret whether rank 1 is best or worst based on your order parameter
- •RANK.EQ doesn't support ranking across multiple criteria simultaneously; use COUNTIFS for complex multi-criteria ranking scenarios
- •When datasets contain many tied values, RANK.EQ's ranking gaps (skipping numbers after ties) can create confusing rank distributions that don't sum to expected values
Alternatives
Compatibility
✓ Excel
Since 2010
=RANK.EQ(number, ref, [order]) — Identical syntax across Excel 2010, 2013, 2016, 2019, and 365✓Google Sheets
=RANK(number, ref, [order]) — Google Sheets uses RANK instead of RANK.EQ but provides identical functionalityGoogle Sheets doesn't distinguish between RANK.EQ and RANK.AVG; use RANK for all ranking needs
✓LibreOffice
=RANK(number, ref, [order]) — LibreOffice Calc uses RANK function with identical parameters and behavior