Rank Customers by Spend in Excel and SQL With Ties Handled

"Our top five customers" is one of the most requested lists in any business, and the first time two customers tie for fifth place, someone asks which one gets left out. Ranking is a place where Excel and SQL are close in features and different in defaults, and where the tie rule is a business decision disguised as a function name. This article covers the three tie rules, the Excel and SQL tools for each, how to rank per group and the traps in preparing the data. It is one of the worked pairs in moving between Excel and SQL and uses the same ideas as finding the second highest value in Excel and in SQL and running totals with window functions.

Sample data

Order lines, as a table orders(order_id, customer, region, amount):

Totals per customer: Acme 500, Bolt 500, Cobalt 250, Delta 250, Echo 100. There are two ties, at 500 and at 250.

Total first, then rank

The first mistake is ranking order lines. Acme has two lines, 300 and 200, and a rank over lines would give them separate ranks. Aggregate to one row per customer, then rank.

In Excel, produce a customer list and totals:

F2:  =SORT(UNIQUE(Orders[customer]))
G2:  =SUMIFS(Orders[amount], Orders[customer], F2#)

or with a pivot table. In SQL:

SELECT customer, sum(amount) AS spend
FROM orders
GROUP BY customer;

Everything that follows ranks this summary.

Three tie rules

Take the totals 500, 500, 250, 250, 100.

Sports tables use the first. "How many distinct spend levels are above this customer?" is the second. A numbered list for display uses the third, with a tiebreaker.

Excel: RANK.EQ and RANK.AVG

RANK.EQ(number, ref, [order]) gives the competition rank.

=RANK.EQ(G2, $G$2:$G$6, 0)

The 0 ranks descending, with the largest spend first. For 500, 500, 250, 250, 100 it returns 1, 1, 3, 3, 5. RANK.AVG gives tied values the average of their ranks: 1.5, 1.5, 3.5, 3.5, 5, which suits statistics and not leaderboards. The older RANK is the same as RANK.EQ and is kept for compatibility. These functions ignore text and blanks in the reference range.

A dense rank has no built-in function. A formula that counts distinct larger values does it:

=SUMPRODUCT(($G$2:$G$6>G2)/COUNTIF($G$2:$G$6,$G$2:$G$6))+1

Each larger value contributes 1 divided by how many times it appears, so each distinct larger value adds exactly 1. For Cobalt's 250 the larger values are 500 twice, adding 1/2 + 1/2 = 1, so the rank is 2. In Microsoft 365 a clearer version uses UNIQUE:

=COUNT(UNIQUE(FILTER($G$2:$G$6, $G$2:$G$6>G2)))+1

For a unique rank with a tiebreaker, add a second criterion: =COUNTIFS($G$2:$G$6, ">"&G2) + COUNTIFS($G$2:G2, G2). The second term counts how many equal values appear at or above this row, so the first of two equals gets the lower number. That depends on row order, so sort by a stable key (the customer name) first.

Excel: a sorted top N

Sorting is often better than ranking for display:

=TAKE(SORTBY(F2:G6, G2:G6, -1, F2:F6, 1), 3)

SORTBY orders by spend descending, then by customer name ascending as the tiebreaker, and TAKE keeps the first three rows. The tiebreaker makes the order deterministic. In the sample, the top three are Acme (500), Bolt (500) and Cobalt (250), and Delta is left out despite the tie at 250. Say so, or return all rows with spend >= the third value, which here is four rows.

SQL: three functions for three rules

SELECT customer, spend,
       rank()       OVER (ORDER BY spend DESC) AS rank_gap,
       dense_rank() OVER (ORDER BY spend DESC) AS rank_dense,
       row_number() OVER (ORDER BY spend DESC, customer) AS rank_unique
FROM (SELECT customer, sum(amount) AS spend FROM orders GROUP BY customer) t
ORDER BY spend DESC, customer;

For the sample:

rank() matches RANK.EQ. dense_rank() has no Excel function. row_number() needs the tiebreaker customer in its ORDER BY to be repeatable; without it, Acme and Bolt may swap between runs.

Top N and the tie at the boundary

"Top three" with a tie at third place has no single right answer. The options:

• Exactly three rows with a tiebreaker (row_number() <= 3). Simple; arbitrary or rule-based about who is cut.

• Everyone at or above the third place spend (rank() <= 3). Returns four rows in the sample: Acme, Bolt, Cobalt, Delta, since rank 3 is shared. This is fairest and variable in length.

• Top three distinct spend levels (dense_rank() <= 3). Returns all five here.

SELECT customer, spend
FROM (SELECT customer, sum(amount) AS spend,
             rank() OVER (ORDER BY sum(amount) DESC) AS r
      FROM orders GROUP BY customer) t
WHERE r <= 3;

Put the rule in the report's notes: "Customers tied for third are all shown." PostgreSQL also has FETCH FIRST 3 ROWS WITH TIES, which does the same as rank() <= 3 in one clause: ORDER BY spend DESC FETCH FIRST 3 ROWS WITH TIES.

Ranks within a group

To rank customers within each region, partition:

SELECT region, customer, spend,
       rank() OVER (PARTITION BY region ORDER BY spend DESC) AS rank_in_region
FROM (SELECT region, customer, sum(amount) AS spend FROM orders GROUP BY region, customer) t;

For the sample, North ranks Acme 1, Cobalt 2, Echo 3, and South ranks Bolt 1, Delta 2. A customer who orders in two regions appears in each, with a separate rank, which is why the grouping includes region. In Excel the equivalent is a rank with a condition:

=COUNTIFS($B$2:$B$6, B2, $G$2:$G$6, ">"&G2) + 1

It counts customers in the same region with higher spend, plus one.

Pitfalls

Ranking before aggregating. Covered above; it ranks lines.

NULL and blank spend. In SQL, ORDER BY spend DESC puts NULL first in PostgreSQL, so a customer with no total can rank first. Use NULLS LAST or filter. In Excel, RANK.EQ skips blanks.

Floating point. Spend computed from decimals can differ in the last digits, breaking a tie that should exist. Round to cents before ranking.

Currency. Rank in one currency. Mixed currencies give a ranking of face values.

Time window. Rank customers for a stated period, and state it. A ranking that mixes 12 months of one customer with 3 of another is a count of tenure.

Checking

Work the sample by hand: 500, 500, 250, 250, 100 and the three rules. Compare the sheet and the query row by row. Check that the number of customers matches the count of distinct customers in the source, and that the total of the spend column equals the total of the orders (1,600 for the sample). A mismatch there means a customer spelled two ways or a filtered row.

Aggregation choices affect the ranking in subtle ways. Rank on net spend or gross? A customer with a large refund can fall from first to fifth. Include or exclude canceled orders? Rank within a period or over all time? Each choice is a business definition, and each belongs in the query's comment and in the report's notes, because the same customer list produced under two definitions will not match, and someone will notice. A short sentence under the table, "Spend is net of refunds, January to March 2026, ties share a rank", answers most of the questions before they are asked (designing a report query before touching Excel covers writing these rules into the SQL).

When a rank is the wrong summary

A rank loses the size of the gap: first by 1,000 and first by 1 look the same. Show the spend beside it. A rank is also unstable: small changes reshuffle the middle of the list, so a movement of three places may mean nothing. For long lists, use bands (top 10%, next 20%) from ntile or percent_rank, which are less sensitive to individual shifts. And ranking people in an incentive scheme needs a stated tie rule in writing before the numbers come in. The PostgreSQL manual lists the window functions, and Microsoft documents RANK.EQ.