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. Today, In this article, we’ll learn how we can make a numbered list in Excel effectively with appropriate illustrations.
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. Here’s an overview of the dataset for today’s task.
1. Applying 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.
- Hence, press Alt + 0149 simultaneously on your keyboard for a solid bullet or press Alt + 9 simultaneously on your keyboard for a hollow bullet.
- 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.
Read More: How to Make a Bulleted List in Excel
2. Performing 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.
- 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.
- 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. Applying 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.
- 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.
- 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 last press OK.
Step 2:
- Finally, you will be able to make a numbered list that has been given in the below screenshot.
Read More: How to Generate List Based on Criteria in Excel
4. Using 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.
- 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.
Read More: How to Make Alphabetical List in Excel
5. Inserting 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.
- 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.
- 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, 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.
Read More: How to Create List from Range in Excel
6. Applying 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.
- After selecting cell D5, type the following formula in the Formula Bar,
=ROW()- The ROW function will return the row number.
- 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.
7. Applying 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.
- Further, type the CHAR function in the Formula Bar. The CHAR function is,
=CHAR(49)
- Hence, press Enter on your keyboard, and you will get 1 as the return of the CHAR function.
- Now, manually type the CHAR function’s argument 50 to 57, and you will get outputs 2 to 9 in cells D6 to D13 respectively that have been given in the below screenshot.
Read More: How to Make a To Do List in Excel
8. Running 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
- After clicking on the Visual Basic menu, a window named Microsoft Visual Basic Applications will appear in front of you.
- From the Microsoft Visual Basic Applications window, go to,
Insert → Module
- 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.
Things to Remember
👉 while working with the Flash Fill option, manually type a cell value then apply the Flash Fill option.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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
- How to Make a Price List in Excel
- Create a Unique List in Excel Based on Criteria
- How to Make a Comma Separated List in Excel
- How to Create a Contact List in Excel
<< Go Back to Make List in Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
