Excel Rank Formula

Steve from Phoenix is looking for a way of ranking his sales representatives by revenue for a local company.

Excel's Rank formula works perfectly for this. Excel's Rank formula works perfectly for this.

To rank values, follow these steps:

Using an example where Column A contains "Sales Reps" names and Column B contains the "Revenue," we'll place the Rank formula in Column C.

Place the cursor in Cell C2 and type the following formula: =RANK(B2,$B$2:$B$16,0) B2 is the cell that will be ranked. $B$2:$B$16 is the cell range being ranked.

NOTE: To ensure the formula copies correctly down the rows, either type the Dollar Signs or press the F4 key. This makes the formula an absolute cell reference, meaning the numbers will not change as you copy the formula to other locations.

The "0" is the ranking order. Zero "0" will rank in descending order,

One "1" will rank in ascending order.

Copy the formula down each row to include rows 2 through 11. (or to whatever data range you are ranking) Voila! All revenue amounts are now ranked.

HINT: The "Rank" formula gives all values that are equal the same rank. So, if you have three values that match exactly, it will rank them all as "3," for example. There would then be no Rank 4 or Rank 5. The next rank would be "6." This is similar to rankings in sports. If you prefer, you may also use Conditional Formatting.

Excel offers nice formatting for adding a visual effect for ranking numbers.