Excel Ordinal Number Format: Show 1st, 2nd, and 3rd

How-to basics · Updated November 19, 2026

Excel does not have a built-in number format that automatically changes a number to an ordinal such as 1st, 2nd, or 3rd. For a dynamic ordinal, put this formula beside a nonnegative whole number in A2: =IF(A2="","",A2&IF(AND(MOD(A2,100)>=11,MOD(A2,100)<=13),"th",IF(MOD(A2,10)=1,"st",IF(MOD(A2,10)=2,"nd",IF(MOD(A2,10)=3,"rd","th"))))). It returns text, including the correct suffix for exceptions such as 11th, 12th, and 13th.

Does Excel have an ordinal number format?

No standard Excel number-format code converts every number to its correct ordinal form. A number format can change how a stored value looks without changing the value itself, but a custom format such as 0"th" appends the same letters to every displayed number. It would show 1th, 2th, and 3th, so it is not a general solution.

That distinction matters because the phrase “ordinal number format” can refer to two different things: a display-only custom format or a formula that produces an ordinal label. Use a custom format when every value should have the same suffix. Use a formula when suffixes must vary according to the number. Excel’s TEXT function does not provide a special ordinal format code that handles the suffix rules automatically.

Formula for 1st, 2nd, 3rd, and other ordinals

Assume A2 contains a nonnegative whole number, such as 1, 2, 3, or 21. Enter this formula in B2, then copy it down for the other rows:

=IF(A2="","",A2&IF(AND(MOD(A2,100)>=11,MOD(A2,100)<=13),"th",IF(MOD(A2,10)=1,"st",IF(MOD(A2,10)=2,"nd",IF(MOD(A2,10)=3,"rd","th")))))

The formula combines the original number with a suffix. For example, it returns 1st for 1, 2nd for 2, 3rd for 3, 4th for 4, 21st for 21, and 24th for 24. It returns a blank when A2 is blank, rather than displaying a stray suffix. For zero, it returns 0th; decide whether that is appropriate for your data, since zero often does not represent an ordinal position.

The formula assumes numeric whole-number input. A decimal such as 2.5 is not an ordinal position, and negative values are outside the intended use. If your input may contain decimal values or text, validate or clean that data first instead of relying on the suffix formula to interpret it.

Why the suffix formula works

Most positive ordinals are determined by the last digit: numbers ending in 1 take “st,” those ending in 2 take “nd,” and those ending in 3 take “rd.” Other endings take “th.” The formula uses MOD(A2,10) to test the final digit. For instance, the remainder when 24 is divided by 10 is 4, so the formula selects “th.”

The last digit rule has an important exception. Numbers ending in 11, 12, or 13 use “th,” not “st,” “nd,” or “rd.” The formula checks the last two digits first with MOD(A2,100). That is why it correctly returns 11th, 112th, and 213th, even though those numbers end in 1, 2, and 3. After that exception check, it applies the usual last-digit rule.

The result is text because the number and suffix are joined together. That is useful for labels, but it differs from a number format, which keeps the underlying cell value numeric. Keep that difference in mind if the ordinal will be used in calculations or sorted with other values.

Enter and fill the formula in Excel

On Excel for Microsoft 365 for Windows, place the source numbers in column A and use column B for the display labels. A typical setup is:

  1. Type the first numeric value in A2 and the next values below it.
  2. Select B2, enter the formula, and press Enter.
  3. Select B2 again and drag the fill handle at the lower-right corner of the cell down the column. Excel adjusts A2 to A3, A4, and so on.
  4. Check values ending in 1, 2, and 3, as well as 11, 12, and 13, to confirm the results suit your list.

For a formatted Excel table, entering the formula in the first data cell of the new column usually fills the formula down the table column automatically. Give the column a clear heading such as “Ordinal.” On Mac, the formula itself works the same way; the ribbon layout can differ, and the fill handle is also available. The formula uses ordinary functions supported in Microsoft 365, so it does not require a newer dynamic-array function.

When a custom number format is enough

A custom format is useful when a fixed suffix is all you need and you want the cell to remain numeric. For example, the format 0"th" displays 4 as 4th while the stored value remains 4. It will also display 1 as 1th, however, so it should only be used for data where “th” is always correct. It cannot calculate the appropriate ending based on the last digits.

In desktop Excel for Windows, select the cells, open the Format Cells dialog from Home > Number using the small dialog launcher, choose the Number tab and Custom category, and enter the format code in the Type box. The suffix is enclosed in quotation marks so Excel treats it as literal text. On Mac, select the cells and open Format Cells, commonly with Command+1, then choose Custom and enter the code. Menu details can vary slightly by release.

Custom formats affect display, not the cell’s stored number. If you refer to a cell formatted as 4th in a calculation, Excel still uses 4. In contrast, the formula method returns text such as “4th.” Do not apply a fixed “st” format and expect it to behave like the formula; number formats do not evaluate suffix rules based on a number’s last digit.

Keep ordinal labels separate from numeric data

Keeping the source number in one column and its ordinal label in another is usually the clearest arrangement. The original values remain available for calculations, sorting, charting, and later edits, while the adjacent column provides reader-friendly wording. The formula’s text output may sort alphabetically rather than numerically: for example, text labels can sort in an order that does not match the underlying numerical sequence.

If you need to sort the records by position, sort by the original numeric column, not by the ordinal text column. Likewise, use the source numbers for formulas such as totals or comparisons. If you replace the numbers with ordinal text, Excel no longer treats those entries as ordinary numeric values for those tasks.

For a report that only needs a display label, the formula column can be copied and pasted as values after checking it. That freezes the labels as text and removes their links to the source cells. Keep a copy of the numeric source data if it will be needed later, and remember that pasted labels will not update when the original numbers change.

Ordinals in dates, rankings, and lists

Ordinal wording is common in dates (“the 1st of May”), placements (“3rd place”), and numbered instructions (“the 2nd step”), but these are different data contexts. A date in Excel is stored as a date serial number and displayed using a date format. Applying the ordinal formula to a whole date serial would produce an ordinal for that serial value, not a formatted day-of-month phrase. For date wording, build and test a date-specific text formula or use a presentation method designed for the date, while keeping the actual date value available for calculations.

Rankings also need care. A rank can contain ties, and the ordinal suffix formula only changes the spelling of an integer; it does not calculate someone’s rank. First determine the rank using an appropriate ranking method, then use the suffix formula if the result is a nonnegative whole number. Think through whether tied results should share a rank or use a different display rule before labeling a final report.

For numbered lists, Excel’s formula creates a cell value; it does not automatically turn worksheet rows into a Word-style numbered list. If the positions are simply consecutive, you can enter the numbers in a source column and generate labels beside them. If rows are filtered, deleted, or rearranged, decide whether the numbers should remain fixed identifiers or recalculate to reflect the new visible order.

Common errors and how to avoid them

  • 11st, 12nd, or 13rd: The formula must check the last two digits before applying the last-digit rule. The supplied formula handles these exceptions.
  • Every number ends in “th”: A custom format such as 0"th" is static. It does not produce varying suffixes.
  • A formula displays an error: Check whether the source contains text that looks like a number, a decimal, or an error value. The formula is intended for nonnegative numeric integers or blanks.
  • Labels sort in an unexpected order: Formula results are text. Sort by the original numeric values if numeric order is required.
  • Zero appears as 0th: That is what the formula specifies. If zero should be blank or a custom label, adjust the formula’s zero handling to match the meaning of your data.

Also check that the formula has been filled down far enough and that each row points to its own source cell. A copied formula should refer to A3 on the next row, not keep referring to A2. If you use a custom number format, inspect the actual stored value in the formula bar; the displayed suffix does not mean the cell contains text.

Frequently Asked Questions

Can Excel automatically display 1st, 2nd, and 3rd?
No built-in custom number format handles all ordinal suffixes dynamically. Use a formula to generate the varying suffix, or use a static custom format only when the same ending is correct for every value.

Can I use the formula in Excel for the web?
Yes. The formula uses standard Excel functions and works in Excel for the web. The desktop steps for creating a custom number format may not be available in the same way in the browser, so use desktop Excel if you need to create or edit a custom format.

Does the formula keep the result numeric?
No. Joining the number and suffix makes a text result. Keep the original number in its own cell for calculations, numeric sorting, and charting.

How do I make 1st into “1st place”?
Add the words as part of the label, either by editing the formula to append a space and “place” or by using a separate display column. The result will still be text, and the underlying numeric value should be retained separately.

Will this work for decimal numbers?
It is designed for whole-number positions. Decimals do not have the usual ordinal suffix convention, so decide how to round or otherwise interpret them before creating an ordinal label.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.