Home

How to auto sort rows by a ranked column in Excel?

Hi, I was trying to auto sort rows by a ranked column in Excel. I’m aware that it is done by using macro and vba coding, but that is how far I went with my research. Wonder if you could please assist with a tute. Cheers, Gus

GusWescher Explorer Asked on 26th July 2015 in Excel for Windows.

Share
Comment(0)

Add Comment

2 Answer(s)

Votes
Oldest

We often come across well presented Excel reports which include tables that show sorted results. Most of the times these tables were sorted manually in Excel, using the Data > Sort command or using the Sort & Filter button on the Excel Ribbon.

However, I feel, reports would be much easier to maintain and update if formulas could sort the data automatically (not VBA / Macros as you mentioned)

There’s a simple way to sort data in reports automatically using Excel Formulas. I’m using RANK, INDEX & MATCH Functions below. Have a look:

Columns A, B & E show the actual data (manually entered). Enter the following formulas for the cells shown (Column C, F & G), and copy/drag the formulas down their columns as needed:
C5=RANK(B5,$B$5:$B$10,0)

F5 =INDEX($A$5:$A$10,MATCH(E5,$C$5:$C$10,0))

G5 =INDEX($B$5:$B$10,MATCH(E5,$C$5:$C$10,0))

The formula in Column C ranks the scores, where the highest score is ranked 1 and the lowest score in the table is ranked 6.

The formula in Column F pulls the NAME of the person with the sorted RANK in Column E matched with the RANK in Column C.

The formula in Column G pulls the SCORE of the person with the sorted RANK in Column E matched with the RANK in Column C.

——————————————————————————–

Now, there might be a situation where the scores in 2 or more rows are the same, consequently Excel assigns an equal rank to all these rows and the following rank is skipped. For example – In our data, say, Amit and Uyen have the same score 71. They both are assigned Rank 4 by the Excel formula and Rank 5 is skipped. In such an event, The Sorted Data will show a #N/A error against Rank 5 (See Row 9 in ‘Sorted Data’). How do we deal with this?

This problem has a quick fix. Just insert a new column for an Adjusted Score and then rank those adjusted values. Here are the formulas for the cells shown:

C5 =B5+0.0000001*ROW()

D5 =RANK(C5,$C$5:$C$10,0)

Column C adds a tiny amount to each Score, an amount based on the number of each row in the table. This ensures each value in ‘ADJ SCORE’ is unique, which in turn makes sure the RANK values in Column D to be unique. And this gives us our automatically sorted values in Columns F:H (Sorted Data).