Excel rankings with ordinal numbers, joint, equal and more

Here’s a look at two things that you’d expect Excel to handle quickly and easily, but doesn’t.   Showing a ranking of items with equal ranks shown (3^rd equal etc) and ordinal numbers (1^st, 2^nd, 3^rd etc) or ordinal names (first, second, third etc).

All are possible in Excel but strangely not provided by Microsoft directly.

Here’s what we mean.  The ‘Rank’ column at left is easily done in Excel, you can create an order for scores from an exam, race or whatever you like.  But it only shows plain digits.   Excel’s Rank/Rank.EQ function to show order, first, second etc.

The other columns show many other ways to present the same raw number. These are useful when Excel cells are being linked into a Word document or Mail Merge (for example, reports cards or certificates) or just to make your worksheet more readable and professional.

Over a series of Office-Watch.com articles we go through these variations on rankings and how to make them.

Joint or Equal

The examples we’ve seen for handling equal rankings didn’t go far enough.  A proper wording shows the difference between joint (two ranks the same) or equal rankings (three or more equal). Joint vs Equal rankings with Excel’s Rank() and Rank.EQ()

Ordinal Rank – 1st, 2nd, 3^rd etc

The ‘Ordinal Rank’ column, 1^st, 2^nd etc you’d expect to be provided by Microsoft.  Either as an in-built  function or a number format.  After all, Excel has many different number formats already, so ordinal numbers would seem really obvious – but no.  Ordinal Numbers in Excel, 1st, 2nd, 3rd etc.

Combining Ordinal rank and equality

The other columns demonstrate various ways to combine ordinal numbers or words with the joint/equal indication. Many Ordinal RANK() options in Excel with joint, equal rankings, words and more

‘Better Rank’ shows when there’s two or more entries in equal place.  Excel gives you two ways to detect equal ranks, we’ll show you both.

‘Even Better Rank’ differentiates between joint and equal rankings using a custom function.  The function is needed to handle many rows with the same equal ranking.

Ranking Names

‘Rank Names’ puts the ranks into words.  This is especially useful if the Excel cells are being linked into a Word document or Mail Merge (for example, reports cards or certificates).

Olympic Rankings

‘Olympics etc.’ gives you the Gold, Silver, Bronze and fourth place in names with equal/joint

Fun Rank Names

Finally ‘Fun Ranks’ is a bit of fun to show what’s possible. Many Ordinal RANK() options in Excel with joint, equal rankings, words and more

These options aren’t trivial or pointless.  They can make your worksheets more professional.  With Excel as the data source for a Word mail merge or web page display the ordinal or word variations are very useful indeed.

Excel rankings with ordinal numbers, joint, equal and more