Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Write 1st 2nd 3rd in Excel (3 Easy Ways)

In this article, I will show you how to write 1st, 2nd, and 3rd in Excel. While working on an Excel worksheet, you need to do a massive type of work. Sometimes you may need to rank any dataset ordinally. In this article, I will show you how to write 1st 2nd 3rd in Excel properly. I hope this article will increase your excel skill. However, follow the procedures step-by-step. I have added pictures for your better understanding.


Download Practice Workbook

Please download the workbook to practice yourself.


3 Effective Ways to Write 1st 2nd 3rd in Excel

Here I will use the following dataset. You can see that the dataset has three columns B, C, and D called Student Id, Name, and Position. You can see the position has ordinal numbers already. but they are not correctly formatted. I will show you how to write 1st, 2nd, 3rd in Excel with the correct format. Let’s follow the steps of every procedure.

dataset of how to write 1st 2nd 3rd in excel


1. Formatting Text Values as Superscripts to Write 1st 2nd 3rd in Excel

In this method, I will show you how to format text values as superscripts to write 1st 2nd 3rd in Excel. This is an easy way. Follow the whole procedure step-by-step. However, I have used this dataset where the positions are not formatted correctly. So, let’s follow the following steps to format the positions.

Formatting Text Values as Superscripts to Write 1st 2nd 3rd in Excel

Steps:

  • First, select the last portion of the ordinal positions.
  • Then, press Ctrl+1 simultaneously.

Formatting Text Values as Superscripts to Write 1st 2nd 3rd in Excel

  • Consequently, a window will pop up like the picture given below.
  • In that window, select the Superscript option.
  • Then press Ok.

Formatting Text Values as Superscripts to Write 1st 2nd 3rd in Excel

  • As a result, the cardinal position will be formatted correctly.
  • Then, Fill-Handle from the D5 cell to the D10 cell.

Formatting Text Values as Superscripts to Write 1st 2nd 3rd in Excel

  • Consequently, Excel will show the corrected format of the positions.

Formatting Text Values as Superscripts to Write 1st 2nd 3rd in Excel

This is how you can format text values and know how to write 1st 2nd 3rd in Excel.

Read More: How to Superscript Text in Excel Graph (3 Quick Ways)


2. Adding Superscript for Quick Access Toolbar

In this part of this article, I will show you how to write 1st 2nd 3rd in Excel by adding superscripts to the quick access toolbar. I am sure this will help you to enhance your excel knowledge. Follow the steps given below.

Steps:

  • First, select the arrow icon shown in the picture given below.
  • Then, select the More Commands option from the pop-up window.

Formatting Adding Superscripts to Quick Access Toolbar to Write 1st 2nd 3rd in Excel

  • Select the Quick Access Toolbar in the pop-up window.
  • Then, select the Superscript option.
  • After that, click on the Add button.

Formatting Adding Superscripts to Quick Access Toolbar to Write 1st 2nd 3rd in Excel

  • Then, you will find the superscript option on the other side. Press Ok.

Formatting Adding Superscripts to Quick Access Toolbar to Write 1st 2nd 3rd in Excel

  • As a result, you will find the Superscript option in the toolbar.

Formatting Adding Superscripts to Quick Access Toolbar to Write 1st 2nd 3rd in Excel

  • Now, select the last texts of the entry of the D5 cell.
  • Then click on the Superscript option.

Formatting Adding Superscripts to Quick Access Toolbar to Write 1st 2nd 3rd in Excel

  • As a result, Excel will make the superscript of the ordinal position. Then Fill Handle to copy down this command from D5 to D10 cell.

Formatting Adding Superscripts to Quick Access Toolbar to Write 1st 2nd 3rd in Excel

  • Consequently, Excel will show the following results.

Formatting Adding Superscripts to Quick Access Toolbar to Write 1st 2nd 3rd in Excel

This is the process of writing 1st 2nd 3rd in Excel by using the quick excel toolbar.

Read More: [Fixed!] Excel Superscript Not Working (4 Possible Solutions)


3. Applying Superscript by Inserting Equation

In this method, I will apply superscript by inserting equations in Excel. This is also an easy method. This is the last of this article but not the least. You can see that I have deleted all the positions and the position column is blank. Follow the following steps of this procedure. Hopefully, you will enjoy the whole method.

Inserting Equation to Write 1st 2nd 3rd in Excel

Steps:

  • First, select the Insert tab from your toolbar.
  • Then select the Symbols & Equations options respectively.

  • In the equation tab, you will find the Structures Click on it.
  • Then, select the script option and click on the first option.

Inserting Equation to Write 1st 2nd 3rd in Excel

  • You will find the equation writing expression.
  • Then, write down the ordinal numbers.

Inserting Equation to Write 1st 2nd 3rd in Excel

  • As a result, you will find the ordinal rank in the D5.

Inserting Equation to Write 1st 2nd 3rd in Excel

  • Similarly, write down all the positions by following this manually.

This is how you can write ordinal positions in Excel by inserting equations easily.

Read More: How to Add Subscript in Excel Graph (7 Easy Methods)


How to Write 1st 2nd 3rd in Excel Using VBA

You can write the ordinal numbers in Excel using VBA code. Here I will make a user-defined function in Excel using a VBA code to write 1st 2nd 3rd. Hopefully, this process will help you to increase your Excel skill. Follow the following steps.

Steps:

  • First, click the Alt+F11 buttons simultaneously.
  • Then, press the indicated button shown in the next picture and select the Module

  • Then, copy the following code to the module.
Function OrdRank(s_Number As String) As String
Select Case CLng(VBA.Right(s_Number, 1))
Case 1
OrdRank = s_Number & "st"
Case 2
OrdRank = s_Number & "nd"
Case 3
OrdRank = s_Number & "rd"
Case Else
OrdRank = s_Number & "th"
End Select
Select Case VBA.CLng(VBA.Right(s_Number, 2))
Case 11, 12, 13
OrdRank = s_Number & "th"
End Select
End Function

Here, we declared some variables in the VBA code to build a user-defined function called OrdRank and used the Case Statement to apply some conditions.

  • Then, select the E5 cell.

  • Then, write down the following formula in the selected cell.
=OrdRank(D5)

  • After pressing Enter, excel will show the following result. Then, copy down the formula from E5 to E10 cells.

  • As a result, Excel will show all the ordinal ranks of the D

This is how you will write ordinal positions in excel by inserting a VBA code.


Things to Remember

  • You need to bear in mind that the positions added in Method 3 are manual. You cannot fill the following cells by copying them down like fill-handle.

Conclusion

In this article, I have tried to explain how to write 1st, 2nd and 3rd in Excel. I hope you have learned something new from this article. Now, extend your skill by following the steps of these methods. You will find such interesting blogs on our website Exceldemy.com. I hope you have enjoyed the whole tutorial. If you have any queries, feel free to ask me in the comment section. Don’t forget to give us your feedback.


Related Articles

Souptik Roy

Souptik Roy

Hello there. This is Souptik Roy. I graduated from Naval Architecture & Marine Engineering department. I am trying to explore the world of Microsoft Excel and want to increase my analytical power apart from my Engineering degree, as I have a curious mind. this is why I am trying to learn Excel Operations and write articles to help others.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo