Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

For example I have a table with a caption and some numerical data, such as:

Mary | Sue | Mike
 100 | 20  | 30 

I want to generate a ranking, i.e.

1. Mary
2. Mike
3. Sue

Is there a way to do it automatically? Automatically as in not using "Data > Sort" manually each time the numbers at hand are recalculated.

I know about the RANK function, that's the inverse of what I want (RANK gives you the data's rank, I want the rank's data).

share|improve this question
up vote 1 down vote accepted

ExampleYes, there is a way to do it automatically, It uses RANK, twice (once to sort and a second time to deal with duplicate values)

The example shows 2 ways of doing it without macros;

sheet1 uses RANK, COUNTIF and VLOOKUP (recommended) sheet2 uses HLOOKUP, VLOOKUP, COUNTIF, MAX and MIN

share|improve this answer
This appears incomplete; please properly link your example. – nc4pk Jul 11 '13 at 22:15
I've added the example. It shows 2 ways of auto-sorting text – Errol Goetsch Jul 15 '13 at 18:36

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.