Screenshots: Add ordinals in Excel

Pro tip: Add an ordinal indicator to a value in Excel

Adding an ordinal indicator - st, nd, rd, and th - uses a suffix to denote the value's position within a series. For example, 1 becomes 1st, 2 becomes 2nd, 3 becomes 3rd, and so on. In Excel, you can use a complex formula to create a new string or you can apply several conditional formatting rules to display the indicator with the value.

Excel 2003 users must use the formula solution. If you want to apply the conditional formatting technique, you must have Excel 2007 or later.

The rules

Knowing the rules and their precedence is imperative. Trying to apply ordinals without knowing the following rules will just make you sad:

  • Values ending in 0 always get th.
  • Values ending in 1 get st unless the value is 11 or a value that ends with 11.
  • Values ending in 2 get nd unless the value is 12 or a value that ends with 12.
  • Values ending in 3 get rd unless the value is 13 or a value that ends with 13.
  • Everything else gets th.

Getting the rules applied in the correct order is the key. The values 11, 12, and 13 certainly throw a monkey wrench into the works, but Excel can handle it.

By Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.