How to Write 1st 2nd 3rd in Excel Fast

As someone who works with Excel regularly, I know how important it is to format data properly. Whether I’m creating a ranking list, tracking sales performance, or organizing event placements, using ordinal numbers like 1st, 2nd, and 3rd makes my spreadsheets clearer and more professional. Unfortunately, Excel doesn’t have a built-in function for this. However, I’ll show you how to write 1st 2nd 3rd in Excel using formulas, custom formatting, and VBA.

Key Takeaways:

• Ordinal numbers enhance clarity and professionalism in Excel, making ranked or sequential data easier to interpret.
• Excel lacks a built-in ordinal function, but you can use formulas, AutoCorrect, or superscript formatting to add them.
• A custom formula using TEXT and MOD functions correctly assigns suffixes like “st,” “nd,” “rd,” and “th.”
• VBA macros automate ordinal number formatting, saving time and ensuring accuracy in large datasets.
• Using templates, AutoCorrect, or macros helps maintain consistency across multiple Excel reports.

 

Why Writing 1st, 2nd, and 3rd in Excel Matters

Enhancing Data Presentation

Adding ordinals like 1st, 2nd, and 3rd in Excel improves both clarity and visual appeal. These small yet effective details help differentiate ranked or sequential data, making spreadsheets easier to read and interpret. Proper formatting not only enhances the overall aesthetics of your Excel sheets but also ensures that key information stands out. This is especially useful when dealing with large datasets, where clear distinctions can improve scanning speed and comprehension.

Improving Professional Reports

Using ordinals in Excel tables enhances the organization of professional reports by making rankings and sequential data more intuitive. This approach adds structure and precision, ensuring that your reports effectively communicate insights. A well-formatted dataset reflects attention to detail, reinforcing professionalism and credibility in business communications. Automating ordinal formatting also saves time and ensures consistency across multiple reports, ultimately boosting efficiency and presentation quality.

 

How to Write 1st 2nd 3rd in Excel

Method 1: Using Superscript for Ordinals

Sometimes, I want a more polished look where the ordinal suffix appears as a superscript, just like in formal documents. While Excel doesn’t automatically format text as superscript in a formula, I can apply it manually. Follow these steps:

STEP 1: Select the cell where I’ve typed an ordinal (e.g., “1st”).

STEP 2: Highlight just the “st”, “nd”, or “rd” portion.

STEP 3: Right-click and choose Format Cells.

STEP 4: Under the Font tab and check the Superscript box.

STEP 5: Click OK and press Enter, and the text will appear in superscript.

STEP 6: Follow the same steps for other rows.

This method is great for creating well-formatted Excel reports, but it’s a bit tedious if I need to format a lot of numbers.

Method 2: Using AutoCorrect for Quick Formatting

If I frequently type ordinal numbers and want Excel to automatically convert them to a formatted version, I can use AutoCorrect.

STEP 1: Go to File and click on Options.

STEP 2: Click on “Proofing”. Under “Proofing” click on AutoCorrect Options.

STEP 3: Under the AutoCorrect tab, type “1st” in the “Replace” box.

STEP 4: In the “With” box, enter “1ˢᵗ” (copy and paste superscript text).

STEP 5: Click OK.

Now, whenever I type “1st” in Excel, it will automatically be replaced with the superscript version! Repeat for the same process for 2nd, 3rd, etc.

Method 3: Using a Formula to Automatically Add Ordinal Suffixes

The simplest way I found to add ordinals in Excel is by using a formula. Excel doesn’t have a built-in function for this, but I can create one using the TEXT function combined with logic to determine the correct suffix.

Here’s the formula I use:

=TEXT(A1,”0″)&IF(AND(MOD(A1,10)=1,MOD(A1,100)<>11),”st”,IF(AND(MOD(A1,10)=2,MOD(A1,100)<>12),”nd”,IF(AND(MOD(A1,10)=3,MOD(A1,100)<>13),”rd”,”th”)))

• The TEXT(A1,"0") part ensures the number remains a number.
• The MOD(A1,10) function checks the last digit of the number.
• The MOD(A1,100) function ensures that exceptions (like 11th, 12th, 13th) are handled correctly.
• The IF statements apply the correct suffix: “st” for 1, “nd” for 2, “rd” for 3, and “th” for all other cases..

STEP 1: Enter the number without any ordinal indicator (e.g., 1, 2, 3).

STEP 2: Enter the formula =TEXT(A2,"0")&IF(AND(MOD(A2,10)=1,MOD(A2,100)<>11),"st",IF(AND(MOD(A2,10)=2,MOD(A2,100)<>12),"nd",IF(AND(MOD(A2,10)=3,MOD(A2,100)<>13),"rd","th"))) in a new column. This formula will automatically generate the correct ordinal suffix based on the number.

STEP 3: Copy the formula down the column if you need to apply it to a series of numbers across rows.

By following these steps, you can easily input and format ordinal numbers in your Excel files, ensuring clarity and accuracy in your worksheets.

 

Advanced Techniques with Macros

Automating Ordinal Entry with Macros

Automating ordinal entry using macros in Excel is an efficient technique for repetitive tasks. Macros can be programmed to automatically append the correct suffix—such as “st,” “nd,” or “rd”—to numerical entries, saving time and reducing errors. Follow the steps below to create a macro:

STEP 1: Press Alt + F11 to open the VBA Editor.

STEP 2: Click Insert > Module.

STEP 3: Copy and paste the VBA code below.

Function OrdinalNumber(n As Integer) As String
Select Case n Mod 100
Case 11, 12, 13
OrdinalNumber = n & "th"
Case Else
Select Case n Mod 10
Case 1: OrdinalNumber = n & "st"
Case 2: OrdinalNumber = n & "nd"
Case 3: OrdinalNumber = n & "rd"
Case Else: OrdinalNumber = n & "th"
End Select
End Select
End Function

STEP 4: Close the VBA Editor.

STEP 5: In Excel, use the function like this:

=OrdinalNumber(A2)

This will return results like “1st”, “2nd”, “3rd”, etc.

 

FAQs

How to write 1st 2nd 3rd in Excel?

In Excel, write ordinals like 1st 2nd 3rd using superscript formatting. Select the number, access “Format Cells,” choose the “Font” tab, and check the “Superscript” box to format the suffix. Alternatively, use custom AutoCorrect entries or macros to automate ordinal additions for efficiency.

Can I use these methods in earlier versions of Excel?

Yes, these methods for writing ordinals using superscript, custom formats, and macros can be used in earlier Excel versions, including Excel 2010 and later. Some minor interface differences may exist, but the core functionality remains the same across these versions.

Are there any shortcuts to toggle superscripts quickly?

Yes, you can customize your Quick Access Toolbar in Excel to include superscript and assign hotkeys for quick toggling. Once added, you can use shortcuts like Alt + 5 or Option + 5 on Mac to apply superscript formatting, streamlining the process for repetitive tasks.

How do I ensure consistency in formatting across my spreadsheets?

To ensure consistency in formatting across your spreadsheets, utilize Excel’s cell style features, which can be applied universally to maintain a uniform appearance. Create templates with predefined formats, including ordinals, and use them for new projects. Additionally, embedding macros ensures repeated tasks like formatting ordinals are consistent throughout.

What are superscripts and subscripts in Excel?

Superscripts and subscripts in Excel are text formatting options where superscripts are characters raised above the baseline, often used for ordinals or mathematical powers, while subscripts appear below the baseline, typically for chemical formulas. These formats enhance readability and clarity in scientific, technical, or professional data presentations.