Excel: rank sales from highest to lowest
Excel

Excel: rank sales from highest to lowest

by HubSite 365 about Alan Murray (Computergaga)

Use RANK.EQ to assign sales positions without rearranging the original rows in an Excel report.

Key insights

  • RANK.EQ assigns rank 1 to the highest value when its order argument is 0 or omitted.
  • Use absolute references for the sales range so it stays fixed when you copy the formula down.
  • Tied values get the same rank with RANK.EQ, and later ranks can have gaps.
  • Sorting changes the order of rows; ranking adds a position while leaving rows in place.

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.

What it does

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 .

Who it is for and what you need

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.

How it works

  1. Identify the cells containing the sales figures, such as B2:B10.
  2. In the first cell of the Rank column, enter .
  3. Copy the formula down for the remaining sales records. Each result ranks that row’s value against the fixed list.

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.

Limitations and alternatives

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.

Sources

Frequently asked questions

How do I rank sales from highest to lowest in Excel?

Use `=RANK.EQ(B2,$B$2:$B$10)` and copy it down the rank column. The omitted order argument ranks the highest value first.

What happens when sales values are tied in RANK.EQ?

Equal values receive the same rank, which can leave gaps in later ranks. Use RANK.AVG if tied values should receive their average position.

Does ranking sales sort the rows in Excel?

No. RANK.EQ adds a position for each value while preserving row order. Use Largest to Smallest sorting to rearrange the rows.