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

Get FREE Advanced Excel Exercises with Solutions!

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 skills. However, follow the procedures step-by-step. I have added pictures for your better understanding.


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

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, and 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, drag the Fill Handle icon 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.


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 the D5 to the 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


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.


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 skills. 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.

Download Practice Workbook


Back to Learn Excel > Formatting Text > Subscript and Superscript


Conclusion

In this article, I have tried to explain how to write 1st 2nd 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. 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


<< Go Back to Subscript and Superscript | Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Souptik Roy
Souptik Roy

Souptik Roy, a BSc graduate in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, dedicated 1.5 years to the ExcelDemy project. During this time, he authored 50+ articles and reviewed 20+ for ExcelDemy. Presently, he is a designer and content developer at YouHaveGotThisMath and Brainor, sister concerns of ExcelDemy. His educational content spans science, mathematics, and grammar. Roy's interests include developing creative ideas, visualizing concepts with tools like Adobe Illustrator, and problem-solving within Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo