Excel: Hidden Trick to Rank Values

Key insights

• Dense Rank: Excel has no built-in DENSE_RANK, but you can recreate dense ranking like SQL by using UNIQUE() with LARGE() or SORT() to list distinct values and assign consecutive ranks without gaps.
  
• RANK.EQ + COUNTIF: Combine RANK.EQ with COUNTIF to prevent skipped numbers for ties. Add a running count of duplicates to the rank so tied values become sequential rather than leaving gaps.
  
• Top-N reports: Build dynamic top-N lists by linking ranking formulas to filters or conditional formulas. The report updates automatically and can include all items tied at a top position.
  
• Multiple-criteria ranking: Rank by more than one field (for example, region then sales) using helper columns, array formulas, or functions like SUMPRODUCT, INDEX and MATCH to break ties and order results accurately.
  
• Power Query & PivotTables: For large or complex datasets, use Power Query to sort and add index columns or use PivotTables to sort and group—both simplify ranking without heavy formula work.
  
• Compatibility & benefits: Basic RANK exists in all Excel versions; RANK.EQ and RANK.AVG (Excel 2010+) clarify tie behavior. These tricks produce continuous ranks, support fair comparisons, and improve automated reports and dashboards.
  

Overview of the Video

This article reports on a YouTube video by Chandoo that demonstrates a neat Excel workaround for creating a DENSE RANK-style ranking. The presenter explains how to combine modern Excel functions to get continuous ranks without the gaps that standard ranking functions produce. In short, Chandoo shows how using UNIQUE() together with either LARGE or SORT yields dense ranks similar to SQL implementations. Consequently, the technique is especially useful when you need ordered positions that do not skip numbers after ties.

The Core Technique Explained

Chandoo begins by noting that Excel does not include a built-in DENSE RANK function, so the video focuses on constructing one with available tools. First, he uses UNIQUE() to extract distinct values from a list, and then applies SORT or LARGE to order those unique values. By mapping the original values to the position of each value in the sorted unique list, you obtain a dense ranking where tied values share the same rank and ranks remain consecutive.

The presenter also contrasts this approach with the classic formula that pairs RANK.EQ and COUNTIF, explaining that the newer dynamic array functions simplify formulas and reduce the need for helper columns. However, he emphasizes that the choice depends on your Excel version and whether you prefer a single-cell dynamic solution or more explicit helper steps for readability. As a result, viewers can pick the method that fits their workbook complexity and team practices.

Handling Ties and Tradeoffs

While dense ranking avoids skipped numbers, Chandoo highlights tradeoffs you should consider when choosing an approach. For example, using dynamic array functions like UNIQUE() and SORT yields concise formulas but requires a modern Excel release such as Microsoft 365 or Excel 2021 and later. Conversely, the RANK.EQ plus COUNTIF approach works in older versions but can become cumbersome on large sets and harder to maintain over time.

He also discusses performance implications: array-based solutions can be faster and cleaner on moderate datasets, yet they may slow complex workbooks if applied repeatedly across many ranges. Therefore, balancing clarity, compatibility, and speed is key when adopting any ranking method.

Alternatives and Practical Challenges

Beyond formulas, Chandoo points out that tools like PivotTables and Power Query can produce ranks too, often with better scalability for large or transformed datasets. However, those tools shift the workflow away from live formulas and into refreshable reports or ETL-style steps, which may not suit every user who needs immediate, cell-level calculations. Moreover, Power Query and PivotTable methods require extra steps and sometimes a different mindset for those used to in-sheet formulas.

Practical Tips and Takeaways

In closing, the video gives practical tips for implementation and testing, advising users to validate results when data changes and to document chosen methods for team clarity. Chandoo’s examples illustrate how to handle duplicates, build top-N reports, and combine criteria when needed, while warning about version limits and maintainability. Overall, the techniques he shows give analysts flexible options: choose concise dynamic formulas where supported, use classic formulas for compatibility, or move to PivotTables and Power Query for larger datasets and clearer ETL control.

Keywords

Excel ranking values trick, Excel rank formula tutorial, Rank values in Excel, RANK.EQ tutorial Excel, Rank without duplicates Excel, Advanced Excel ranking tip, Excel ranking with ties, Dynamic ranking Excel formula