Free Excel workbook
Download the Ranking Practice File
Get the exact workbook from the tutorial and practice along.
No spam. Unsubscribe anytime.
Let’s look at Excel’s RANK.EQ function. How can you rank your values in descending or ascending order and how can you rank duplicate values without skipping numbers in the sequence?
In our example, we have a list of sales managers and their sales.
We want to create a report that displays the top 3 sales managers based on those numbers.
The problem is that several of the sales managers have the same sales value and will result in a 2-way or 3-way tie for the same rank position.
We want to display each of the names that result in a tie.
Ranking is ideal for grouping your data or bringing order to disordered data. It’s when you want to compare one value against a set of values and determine how that single value ranks in respect to the full set of values.
How Does the RANK.EQ Function Work in Excel?
Using our previously examined list of sales managers and sales, we want to create a ranking of the sales in column C, but we do not wish to sort the original data.
It would be easy to sort the list by sales and then create a “helper column” of numbers that count from 1 forward. The problem is that if we were to copy new values into the data set, the “helper column” would no longer be accurate.
Our solution will be to create in column C a formula using the RANK function.
You will notice when we begin typing the word “rank”, the IntelliSense service provides us with several options. Per Microsoft…
- RANK – This function has been replaced with one or more new functions that may provide improved accuracy and whose names better reflect their usage. Although this function is still available for backward compatibility, you should consider using the new functions from now on, because this function may not be available in future versions of Excel.
- EQ – Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. If you were to sort the list, the rank of the number would be its position.
- AVG – Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the average rank is returned.
We will use the RANK.EQ function for our solution.
A few notes about the RANK.EQ function:
- This function works identically to the older RANK function.
- If there are duplicates in your data resulting in 2-way or 3-way ties, each of those in the tied group will receive the same rank value. However, the list will then skip the number of following rank positions relative to the number of items in the prior tied group.
Example: If the number 10 were to appear 3 times in a list, and its rank is position #5, the following item(s) in the list would begin in rank position #8. The number 10 would consume slots #5, #6, and #7, even though they will all be labeled as #5.
Let’s select cell C5 and create the following formula:
=RANK.EQ(B5,$B$5:$B$24)
We will take the rank of the value in cell B5 and compare it against all other values in cells B5 through B24. (Remember to press F4 and convert the B5:B24 reference to absolute $B$5:$B$24 so it will not change when the formula is copied down the table.)
You can add an optional argument to the formula to determine the ascending/descending order of the ranks. If you omit this argument, descending order is the default behavior.
The returned value of 2 indicates that James Smith’s sale of 9,000 is the second highest in the list.
If we replicate the formula down the remainder of the table, we see that William Jones’ sale of 9,750 is the top-ranking sale of the set.
Why Does RANK.EQ Skip Numbers When Duplicates Exist?
Observe that there are three sales reps with a sale value of 9,000; James Smith (row 5), Michael Brown (row 8), and Maria Martin (row 21).
Notice we are also missing the ranks of #3 and #4.
This is the slot-use behavior we mentioned earlier. We will lose any number of slots commensurate with the number of “ties” in the list.
Let’s fix this behavior.
What Is the Difference Between RANK.EQ and RANK.AVG?
Excel has two dedicated rank functions: RANK.EQ and RANK.AVG. Both skip numbers when duplicates exist, but they handle ties differently.
RANK.EQ returns the top position in a tied group. If three values tie for rank 2, all three get rank 2. The next unique value gets rank 5 (positions 2, 3, and 4 are consumed by the tied group).
RANK.AVG returns the average position of the tied group. The same three tied values would get rank 3 each (the average of 2, 3, and 4). The next unique value still gets rank 5.
=RANK.AVG(B5, $B$5:$B$24)Neither function produces dense ranking (rank without skipping) on its own. For that, use the SUMPRODUCT formula in the next section or the XMATCH formula for Microsoft 365.
When to use each:
- RANK.EQ – Traditional competition ranking (Olympic medals, sports leagues). Tied athletes share the same gold medal.
- RANK.AVG – Statistical analysis where average positions matter (non-parametric tests, percentile rankings).
- Dense ranking formulas (below) – Internal reports, dashboards, and any scenario where you need every rank number to appear in sequence.
Note: The older RANK() function still works for backward compatibility but behaves identically to RANK.EQ. Microsoft recommends using RANK.EQ or RANK.AVG in all new workbooks.
How Do You Rank Without Skipping Numbers in Excel?
To ensure that John Johnson (row 6) receives a ranking of #3, we will use a more sophisticated formula, which oddly does not use any of the included RANK functions.
NOTE: We will create this formula and get it working but will hold off on dissecting its logic until later in the tutorial.
Select cell D5 and enter the following formula:
=SUMPRODUCT((B5<=$B$5:$B$24)/COUNTIF($B$5:$B$24,$B$5:$B$24))
Fill the formula down the remainder of the table.
Notice that rows 5, 8, and 21 have a rank of #2 while rows 6 and 10 have a rank of #3; rows 7 and 17 have a rank of #4. We did not skip any rank positions.
How Do You Dense Rank with SORT, UNIQUE, and XMATCH in Modern Excel?
If you’re running Microsoft 365 or Excel 2021+, you can replace the SUMPRODUCT/COUNTIF formula with a single line that’s easier to read and doesn’t require any array math.
The Formula
Select cell F5 and enter:
=XMATCH(B5, SORT(UNIQUE($B$5:$B$24), 1, -1))
Copy the formula down from E5 to E24.
The results match the SUMPRODUCT formula exactly. Same dense ranks, same handling of ties, zero skipped numbers.
How Does This Formula Work?
The formula works in three steps from the inside out.
Step 1: UNIQUE extracts the distinct sales values.
UNIQUE($B$5:$B$24)This takes the 20 sales values in column B and returns only the unique ones. If 9,000 appears three times, UNIQUE returns it once.
Step 2: SORT arranges them in descending order.
SORT(UNIQUE($B$5:$B$24), 1, -1)The 1 means sort by the first (and only) column. The -1 means descending order, so the highest sale appears first.
Step 3: XMATCH finds the position.
=XMATCH(B5, SORT(UNIQUE($B$5:$B$24), 1, -1))XMATCH searches for the current sale (B5 = 9,000) in the sorted unique list and returns its position. Since 9,000 is the second-highest unique value, XMATCH returns 2. That position IS the dense rank.
Every duplicate value finds the same position in the same sorted list, so all three 9,000 entries get rank 2. The next unique value (8,500) sits in position 3, so there’s no gap.
When Should You Use This Instead of SUMPRODUCT?
| SUMPRODUCT / COUNTIF | XMATCH / UNIQUE / SORT | |
|---|---|---|
| Excel version | All versions | Microsoft 365 / Excel 2021+ only |
| Readability | Low (requires Boolean math explanation) | High (reads like plain logic) |
| Performance on large datasets | Slower (evaluates full array per row) | Faster (UNIQUE and SORT calculate once, XMATCH is a simple lookup) |
| Ctrl+Shift+Enter needed | No (SUMPRODUCT handles arrays) | No (dynamic arrays handle it) |
| Works with ascending rank | Yes (change <= to >=) | Yes (change -1 to 1 in SORT) |
- Use SUMPRODUCT/COUNTIF when your files are shared with people on Excel 2019 or earlier.
- Use XMATCH/UNIQUE/SORT when everyone is on Microsoft 365 or Excel 2021+ and you want a formula that’s faster to write, easier to audit, and simpler to explain to colleagues.
Both approaches produce identical dense ranks and work with the TEXTJOIN dashboard in the next section.
Ascending Rank Version
To rank from smallest to largest instead, change the SORT order from -1 to 1:
=XMATCH(B5, SORT(UNIQUE($B$5:$B$24), 1, 1))The smallest unique value gets rank 1. Everything else shifts accordingly.
How Do You Build a Top 3 Report That Shows Tied Names?
Our boss wants a summarized version of the table that displays the Top 3 sales rankings. One of the requirements is that if there is a multi-way tie, all of the sales reps names for that rank must be displayed.
To derive this information, we need to create an array formula. Luckily, it’s a very simple array formula.
On the “Report” sheet, select cell D6 and enter the following formula:
=IF(C6=Data!$D$5:$D$24,Data!$A$5:$A$24,"")
This formula will compare each item in cells D5:D24 and compare it to the rank number in cell C6. If the value in cells D5:D24 matches, return the associated name in cells A5:A24.
When we hit ENTER, we don’t see any answers.
This is because we are returning an array (a list) of answers and we are seeing the first answer in the array.
Our best friend in this situation is the F9 key. Highlight the formula in the Formula Bar and press the F9 key. We will see all the answers returned from the calculation.
Notice that the first answer is “blank”; that’s why there was no visible answer when we hit ENTER.
We see that “William Jones” is the only real answer in the list. What good is the answer if we can’t see the answer in the cell?
REMEMBER: Press the Escape key (ESC) so you do not permanently change your formula.
How Does TEXTJOIN Display Multiple Names for the Same Rank?
We will modify the existing formula in cell D6 and use a relatively new function in Excel called TEXTJOIN.
TEXTJOIN is available in Office 2019 and Office 365.
Modify the formula as follows:
=TEXTJOIN(", ", TRUE,IF(C6=Data!$D$5:$D$24,Data!$A$5:$A$24, ""))
The TEXTJOIN function will take all the answers generated by the IF function and concatenate them together into an unbroken list of names.
The TEXTJOIN function combines the text from multiple cells/ranges and includes a delimiter you specify between each text value that will be combined. We will provide a “comma-space” to act as our delimiter.
The TEXTJOIN function also allows us to determine how to handle blank cells/ranges. Since we only want the items in the array that contain names, and not the blank items, we will use the “TRUE” option to invoke this behavior.
Because this is an array formula, we need to press CTRL-SHIFT-ENTER to commit the formula to the cell. If you have one of the most recent versions of Excel that utilizes Dynamic Arrays, you only need to press ENTER; it is not necessary to press CTRL-SHIFT-ENTER.
This produces the following formula (notice the opening and closing braces):
{=TEXTJOIN(“,”,TRUE,IF(C6=Data!$D$5:$D$24,Data!$A$5:$A$24,””))}
If we copy the formula down to cells D7 and D8 we see the following results.
If it helps further your understanding of what is occurring, select cell D7, highlight the IF portion of the formula and press the F9 key.
We see that for the #2 rank position we have 3 names and many blanks. The TEXTJOIN function has ignored all the empty items and merged all the names, separating them with comma-spaces to make the answer more presentable.
How Do You Verify the Rankings Update Dynamically?
If we return to the table of sales and change the value of cell B5 to 10,000, the rankings will update.
Returning to the Report sheet, we see that “James Smith” occupies the #1 rank while the other items from the original list have shifted down one level in the ranks.
How Does the SUMPRODUCT/COUNTIF Dense Ranking Formula Work?
When you build complex formulas, it’s often a helpful strategy to build the formula in pieces using separate cells or columns. This allows us to focus on solving one problem at a time. Once we have all the problems solved, we can combine all the pieces into a single solution.
It is also helpful to focus on solving the problem for a single item/record in the data and then replicate the result to the remaining items/rows when completed.
Returning to the sheet with our table, select cell F5 and enter the following formula:
=B5<=B5:B24
The result will be “TRUE” because what we are seeing is the first answer in an array of answers. Since the first item compared is B5 to itself, the answer is “TRUE”.
If we highlight the formula and press the F9 key, we see all the TRUE/FALSE responses where the contents of B5 are compared to all the values in cells B5:B24.
Because this is difficult to compare; the TRUE/FALSE responses to the rows in the table, we will perform a little trick to list the TRUE/FALSE responses on the rows next to their respective evaluations.
- Press the ENTER key to preserve the TRUE/FALSE responses in the Formula Bar.
- Highlight cells F5:F24.
- Click in the Formula Bar to reenter edit mode.
- Press CTRL-SHIFT-ENTER.
This will have the effect of cascading the TRUE/FALSE responses down the rows next to their evaluated sales.
The next step to examine is the use of the COUNTIF function. Returning to our “work on one piece at a time” strategy, select cell G5 and enter the following formula.
=COUNTIF(B5:B24,B5:B24)
If we highlight the finished formula and press the F9 key, we see the following list of values.
Using the same trick as before,
- Press the ENTER key to preserve the list of values responses in the Formula Bar.
- Highlight cells G5:G24.
- Click in the Formula Bar to reenter edit mode.
- Press CTRL-SHIFT-ENTER.
What each number is telling us is how many times the current row’s sales occurs in the list. Example: The number 9,000 occurs 3 times in the list, and each time it occurs we encounter a number 3 as the answer for that row.
In the final combined calculation, the results of the COUNTIF function remain the same for each use throughout the table, but the list of TRUE/FALSE responses from the IF will be different for each row in the table.
The next step is to divide each TRUE/FALSE in column F by the value in column G.
Whenever Excel uses a TRUE or FALSE as part of a mathematical operation, TRUE is interpreted as a 1 while False is interpreted as a 0 (zero).
Looking at the first evaluation a TRUE (1) divided by a 3 yields 0.333333.
When we fill the formula down the list, we see the following results.
The sum of all the results is 2.
Finally, why are we using the SUMPRODUCT function? The SUMPRODUCT function is used as a container to store all the intermediate calculations and then add them all together for the result.
A bonus of the SUMPRODUCT is that it does not require the use of a CTRL-SHIFT-ENTER. SUMPRODUCTs can handle arrays natively without any special instructions during execution.
How Do You Assign Unique Ranks to Tied Values?
If you need a list of completely unique ranks, where ties are spread across rank positions (i.e. a 3-way tie for 2nd place would occupy rank positions 2, 3, and 4), click in cell E5 and enter the following formula.
=RANK.EQ(B5, $B$5:$B$24)+COUNTIF($B$5:B5, B5)-1
The result would be as follows:
Frequently Asked Questions
Why does RANK.EQ skip numbers when there are duplicates?
RANK.EQ assigns the same rank to all tied values but then skips the corresponding number of positions. If three values tie for rank 2, the next value gets rank 5 because ranks 2, 3, and 4 are all consumed by the tied group. This is called competition ranking, where tied items share the highest rank in the group.
How do you rank in Excel without skipping numbers?
Use the SUMPRODUCT/COUNTIF formula: =SUMPRODUCT((B5<=$B$5:$B$24)/COUNTIF($B$5:$B$24,$B$5:$B$24)). This creates a dense ranking where tied values share the same rank and the next unique value gets the next consecutive number with no gaps. No Ctrl+Shift+Enter needed. In Microsoft 365 or Excel 2021+, use =XMATCH(B5, SORT(UNIQUE($B$5:$B$24), 1, -1)) for a cleaner alternative.
What is the difference between RANK.EQ and RANK.AVG?
RANK.EQ returns the top position in a tied group. If three values tie for rank 2, all three get rank 2. RANK.AVG returns the average position of the tied group. The same three values would get rank 3 (the average of 2, 3, and 4). Both functions skip subsequent rank numbers. Neither produces dense ranking without a workaround formula.
How do you list all names that share the same rank?
Use TEXTJOIN with an IF array formula: =TEXTJOIN(", ",TRUE,IF(C6=RankRange,NameRange,"")). TEXTJOIN concatenates all matching names separated by commas and ignores blanks. In Excel versions without dynamic arrays, press Ctrl+Shift+Enter. In Microsoft 365 or Excel 2021+, press Enter.
How do you create unique ranks for tied values in Excel?
Use =RANK.EQ(B5,$B$5:$B$24)+COUNTIF($B$5:B5,B5)-1. The growing COUNTIF range ($B$5:B5 instead of $B$5:$B$24) counts how many times the current value has appeared so far. Each duplicate gets an incrementally higher rank. A 3-way tie for rank 2 produces ranks 2, 3, and 4 instead of three identical rank 2s.
Does RANK.EQ work with text values?
No. RANK.EQ, RANK.AVG, and the older RANK function only work with numeric values. If you need to rank text (such as alphabetical ordering), use COUNTIF to count how many text values are less than the current value: =COUNTIF($A$5:$A$24,"<"&A5)+1. This returns an alphabetical rank position.
Download the Practice Workbook
FREE EXCEL WORKBOOK
Get the Ranking Practice File
- All 3 ranking formulas ready to copy into your own data
- Top 3 dashboard built with TEXTJOIN, ready to customize
- Tiebreaker formula for fully unique ranks
Free. Instant access. No spam.
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
