Did you know that you can get Excel to rank a set of values… without rearranging the data?
It’s like sorting data without actually sorting.
Basically what we do is tell Excel to compare all the data in the range of cells we designate and then it numbers them in either ascending or descending order (your choice).
For example, maybe you have sales figures for the last year totaled by week. You want the data to stay sorted chronologically and at the same time you now need to know which week was the best of the year.
Ranking in descending order will leave them chronologically sorted but will mark the highest sales figure as 1.
Anyway, you get the idea.
So, here’s how the formula is set up:
=rank(value location, range of data to compare, code for ascending or descending)
For example – let’s look at ranking the following data in descending order.
I’m first going to decide where the ranks will be placed in the worksheet… in this case column C… as a result my rank formula will be first placed into cell C2.
I will use the following formula
Once that’s in place I’ll copy the formula down through cell C6.
The result is that column C now has a third set of data for my use without altering the original data at all.
That one definitely ranks as useful in my book.