Excel’s RANK.EQ function assigns each sales figure a position from highest to lowest without rearranging the worksheet rows. It is useful for sales analysts and Excel users who need to compare performance while keeping names, regions, or other details in their existing order. Alan Murray (Computergaga) demonstrates the ranking task in a YouTube Short.
RANK.EQ returns the position of a number within a list. For a descending sales ranking, the largest amount receives rank 1; lower amounts receive progressively larger rank numbers. The formula can be copied down a Rank column to calculate a position for each salesperson.
For example, if sales are in B2:B10, enter in the first rank cell. The omitted third argument uses descending order, so the highest sales value ranks first. You can also make that choice explicit with .
This approach suits anyone maintaining a worksheet with numeric sales values and a corresponding rank column. Start with the sales data in a range and place the formula in a neighboring column. The dollar signs in make the reference absolute, keeping the full comparison range fixed when the formula is copied down.
For a recurring report, the research describes converting the data range into an Excel Table with Ctrl+T. A structured-reference version is . Excel can extend a calculated column when a new table row is added.
Ranking and sorting serve different purposes. Ranking preserves the current row order; sorting by Sales and choosing Largest to Smallest rearranges the data so the highest figure appears first.
RANK.EQ gives equal sales values the same rank. That can leave gaps: if two entries tie at rank 2, the next value receives rank 4. If tied values should share an averaged position, use RANK.AVG instead.
Choose sorting when the report itself should appear in sales order, rather than adding a rank beside the original records. For regular reporting, check that the compared range includes all the sales values; a fixed range needs to be updated if data is added outside it. Read more about Excel features and tips.
Use `=RANK.EQ(B2,$B$2:$B$10)` and copy it down the rank column. The omitted order argument ranks the highest value first.
Equal values receive the same rank, which can leave gaps in later ranks. Use RANK.AVG if tied values should receive their average position.
No. RANK.EQ adds a position for each value while preserving row order. Use Largest to Smallest sorting to rearrange the rows.