Compare List of Numbers With Excel RANK Function

Which student got the top score? If you have test results, or another list of numbers in Excel, you could sort them to see which scores are at the top. But if you want to leave the list in alphabetical order, you can use the RANK function to show the rank for each score.

Video: Excel RANK function

In this video, I show how to make a RANK formula in Excel, to compare numbers in the same list.

Which student did the best? Which golfer won the tournament? See how to rank the results.

The written steps are below the video.

Student Scores

In this example, I’ve got a list with 10 student names, and each person’s score on the latest test.

I’d like to see where each student ranked, but I’d also like to keep the names in alphabetical order.

Add a RANK Formula

To see the student rankings, without sorting the list, I can add a new column, with a RANK formula.

In cell C1, I typed the heading, Rank.
My data is in a named Excel table, which automatically expanded, to include the new column.
In cell C2, I started the RANK formula: =RANK(
Next, I clicked on cell B2, which has the first student’s score
Excel added a structured table reference to that cell, [@Scores]
- In the video, the data is not in a table, so Excel used a normal reference to the cell: B2
Next, I typed a comma, to start the second argument in the formula

Compare to List of Numbers

For the second argument, tell Excel which list of numbers to use, for the rankings.

I clicked at the top of the Scores column, to select all the numbers.
In the formula, Excel added a structured table reference to that column, [Scores]
- In the video, the data is not in a table, so selected cells B2:B11, then pressed the F4 key, to create an absolute reference – $B$2:$B$11

Finish the RANK Formula

Then, to complete the formula, I typed a closing bracket, and pressed the Enter key.

In the Excel table, the formula automatically filled down, to the last row in the table.
In the video, I copied the formula down, from B2 down to B11.

With the Ranks listed in column C, it’s easier to compare the test results.

Al had the top score, 46
Flo ranked 10th, with a score of 20

It’s easier for our brains to compare the numbers 1 to 10, than it is to compare the 2-digit scores in column B!

Get the Sample File

For more RANK formula examples, and to get the sample Excel file, go to the RANK formula in Excel, page on my Contextures site.

And if you need to break ranking ties, there are formulas on my Contextures website page, Excel RANK With Ties, and a sample file to download.

Video: Show Rank in Pivot Table

If you’re summarizing data in an Excel pivot table, you can use a built-in feature, to show the ranks for any column of numbers. This video shows the steps, and there are written steps on my Excel Pivot Table blog.