How to Make a Numbered List in Excel (8 Methods)

Bullets and numbering in Excel are mostly used to organize data in a worksheet. If you have a big list of entries, numbered lists can help you keep track of them. We can make a numbered list by using the Keyboard shortcut, autoFill option, Flash Fill command, OFFSET, ROW, and CHAR functions, and VBA Macros also. Today, In this article, we’ll learn how we can make a numbered list in Excel effectively with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


8 Suitable Ways to Make a Numbered List in Excel

Let’s say, we have a dataset that contains information about 10 different students. Student names and their identification number are given in columns B and C respectively. We will make a numbered list by using the Keyboard shortcut, autoFill option, Flash Fill command, OFFSET, ROW, and CHAR functions, and VBA Macros also. Here’s an overview of the dataset for today’s task.

make a numbered list in excel


1. Apply the Keyboard Shortcut to Make a Numbered List in Excel

Applying the keyboard shortcut to make a numbered list in Excel, is the easiest way. To do that, please follow the steps below.

Steps:

  • First of all, select a cell to make a numbered list. From our dataset, we select cell D5 for our work.

Apply the Keyboard Shortcut to Make a Numbered List in Excel

  • Hence, press Alt + 0149 simultaneously on your keyboard for a solid bullet or press Alt + 9 simultaneously on your keyboard for a hollow bullet.

Apply the Keyboard Shortcut to Make a Numbered List in Excel

  • While releasing the Alt key a solid bullet will appear in cell D5 and then type 5001.

  • After that, autoFill the keyboard shortcut to the entire column and you will get your desired output in column D which has been given in the below screenshot.

Apply the Keyboard Shortcut to Make a Numbered List in Excel

Read More: How to Make a To Do List in Excel (3 Easy Methods)


2. Perform AutoFill Tool to Make a Numbered List in Excel

The easiest and time-saving way is the autoFill tool to make a numbered list in Excel. Let’s follow the instructions below to learn!

Steps:

  • First, type 5001 and 5002 as the student ID of Bob and John in cells C5 and C6 respectively.

Perform AutoFill Tool to Make a Numbered List in Excel

  • Now, select cells C5 and C6, and place your cursor on the Right-Bottom of the selected cells. An autoFill sign pops up. After that drag the autoFill sign downward.

Perform AutoFill Tool to Make a Numbered List in Excel

  • Hence, you will be able to autoFill the student’s ID in column C which has been given below screenshot.

Read More: How to Make a List within a Cell in Excel (3 Quick Methods)


3. Apply the Custom Format to Make a Numbered List in Excel

We can apply the custom format to make a numbered list in Excel. Let’s follow the instructions below to learn!

Step 1:

  • To make a numbered list, select cells from C5 to C14 first.

Apply the Custom Format to Make a Numbered List in Excel

  • Then, right-click on your mouse, and a window will instantly appear in front of you. From that window, click on the Format Cells option.

Apply the Custom Format to Make a Numbered List in Excel

  • Hence, a Format Cells dialog box pops up. From the Format Cells dialog box, go to,

Number → Custom

  • Further, type “• @” in the Type box and at last press OK.

Step 2:

  • Finally, you will be able to make a numbered list that has been given in the below screenshot.

Apply the Custom Format to Make a Numbered List in Excel

Read More: How to Generate List Based on Criteria in Excel (4 Methods)


4. Use of the Flash Fill Option to Make a Numbered List in Excel

The easiest way is to make a numbered list in Excel by using Flash Fill Command. To make a numbered list by using Flash Fill Command, follow the instructions below.

Steps:

  • First, select cell D5 and manually type Michael’s identification number 5001.

Use of the Flash Fill Option to Make a Numbered List in Excel

  • After that, from Home Tab, go to,

Home → Editing → Fill → Flash Fill

  • Finally, you will be able to make a numbered list by pressing on the Flash Fill option.

Use of the Flash Fill Option to Make a Numbered List in Excel


Similar Readings


5. Insert the OFFSET Function to Make a Numbered List in Excel

Now, we will use the OFFSET function to make a numbered list in Excel. This is the easiest and most time-saving way to make a numbered list. Let’s follow the steps below.

Step 1:

  • First, select cell D5.

Insert the OFFSET Function to Make a Numbered List in Excel

  • Now, type the OFFSET function in the Formula Bar. The OFFSET function is,
=OFFSET(D5,-1,1)+1
  • Here D5 is the cell reference from where it starts off moving.
  • -1 refers to the number of rows it moves downward
  • 1 refers to the number of columns it moves rightward.
  • And +1 is the number series that starts from 1.

Insert the OFFSET Function to Make a Numbered List in Excel

  • Further, press Enter on your keyboard and you will be able to get the return of the OFFSET function and the return is 1.

Step 2:

  • Hence, to autoFill the OFFSET function by using the autoFill handle, and, finally, you will get your desired output that has been given in the below screenshot.


6. Use of the ROW Function to Make a Numbered List in Excel

You can use the ROW function to make a numbered list in Excel. To make a numbered list in Excel by using the ROW function, follow the steps below to learn!

Steps:

  • First of all, select an empty cell where we will type the ROW function, from our data we’ll select cell D5.

Use of the ROW Function to Make a Numbered List in Excel

  • After selecting cell D5, type the following formula in the Formula Bar,
=ROW()
  • The ROW function will return the row number.

Use of the ROW Function to Make a Numbered List in Excel

  • Now, press Enter on your keyboard and you will be able to get the return of the ROW function and the return is 5.

  • After that, place your cursor on the Bottom-Right side of cell D5 and an autoFill sign pops us. Now, drag the autoFill sign downward.

  • While completing the above process, you will be able to make a numbered list which has been given in the screenshot.

Use of the ROW Function to Make a Numbered List in Excel


7. Apply the CHAR Function to Make a Numbered List in Excel

In Excel, the CHAR function is a built-in function. CHAR means CHARACTER. The CHAR function can return only text characters. Please follow the instructions below by applying the CHAR function to make a numbered list.

Steps:

  • To make a numbered list by applying the CHAR function, select cell D5 first.

Apply the CHAR Function to Make a Numbered List in Excel

  • Further, type the CHAR function in the Formula Bar. The CHAR function is,
=CHAR(49)

Apply the CHAR Function to Make a Numbered List in Excel

  • Hence, press Enter on your keyboard, and you will get 1 as the return of the CHAR function.

Apply the CHAR Function to Make a Numbered List in Excel

  • Now, manually type the CHAR function’s argument 50 to 57, and you will get output 2 to 9 in cells D6 to D13 respectively that have been given in the below screenshot.


8. Run a VBA Code to Make a Numbered List in Excel

In this method, we will apply a VBA Macros code to make a numbered list. Let’s follow the steps below.

Step1:

  • First of all, from your Developer Tab, go to

Developer → Visual Basic

Run a VBA Code to Make a Numbered List in Excel

  • After clicking on the Visual Basic menu, a window named Microsoft Visual Basic Applications will appear in front of you.

Run a VBA Code to Make a Numbered List in Excel

  • From the Microsoft Visual Basic Applications window, go to,

Insert → Module

Run a VBA Code to Make a Numbered List in Excel

  • A new module pops up. Now, type the below VBA Code in the window. We have provided the code here, you can just copy-paste the code and use it in your worksheet.
Sub Make_Numbered_List()
For i = 1 To 10
Range("B" & i) = i
Next
End Sub

Step 2:

  • After inserting the code, we need to run the code to get the positive integer value. For that, go to,

Run → Run Sub/UserForm

  • Hence, go back to the worksheet and you will be able to make a numbered list.

Run a VBA Code to Make a Numbered List in Excel


Things to Remember

👉 while working with the Flash Fill option, manually type a cell value then apply the Flash Fill option.


Conclusion

I hope all of the suitable methods mentioned above to make a numbered list will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo