Friday, August 01, 2008 – Permalink –

Rank Formatting

Highlight the best

Use Conditional formatting to highlight the rank of items in a list.
Select the range. Go to Format>Conditional Formatting....

Change the first box to "Formula Is".

Enter the following formulas. (Click Add to set the 2nd and 3rd Condition.)

=RANK($A2,$A$2:$A$13)=3
=RANK($A2,$A$2:$A$13)=2
=RANK($A2,$A$2:$A$13)=1



(Notice the three way tie for third.)

Does a tie for first or third make sense?

If you want a unique rank, try a formula like:

=RANK(A2,$A$2:$A$13)+COUNTIF($A$2:A2,A2)-1

This will rank the numbers in the order they appear in the list.

For a detailed discussion of ranking see:

Chip Pearson:
Ranking Data In Lists
(There is a workbook you can download)



See all Topics
excel

Labels: Formats, Formulas