Tuesday, June 09, 2015 – 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, General, Reference, Tips, Tutorials