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

 

Dataset Overview

We have a dataset that contains information about 10 different students. Student names and their identification numbers are given in columns B and C respectively.

make a numbered list in excel


Method 1 – Applying the Keyboard Shortcut to Make a Numbered List in Excel

Scenario: We want to create a numbered list in Excel using a keyboard shortcut.

Objective: Create a list of student identification numbers.

Steps:

  • Select a cell (e.g., D5) where you want to start the numbered list.

Apply the Keyboard Shortcut to Make a Numbered List in Excel

  • Press Alt + 0149 simultaneously on your keyboard to insert a solid bullet (or press Alt + 9 for a hollow bullet).

Apply the Keyboard Shortcut to Make a Numbered List in Excel

  • While holding the Alt key, insert 5001.
  • Release the Alt key. The solid bullet will appear in cell D5, followed by the number 5001.

  • Autofill the keyboard shortcut down the entire column to generate the desired output.

Apply the Keyboard Shortcut to Make a Numbered List in Excel


Method 2 -Using the AutoFill Tool to Make a Numbered List in Excel

Scenario: We want to create a numbered list using the AutoFill tool.

Objective: Generate student identification numbers for Bob and John.

Steps:

  • Insert 5001 and 5002 as the student IDs for Bob and John in cells C5 and C6, respectively.

Perform AutoFill Tool to Make a Numbered List in Excel

  • Select cells C5 and C6.
  • Place your cursor on the bottom-right corner of the selected cells until the AutoFill sign appears.

Perform AutoFill Tool to Make a Numbered List in Excel

  • Drag the AutoFill handle downward to fill in the student IDs for the remaining students in column C.

Read More: How to Make a List within a Cell in Excel


Method 3 – Applying the Custom Formatting to Make a Numbered List in Excel

Scenario: We want to create a custom numbered list format.

Objective: Apply a custom format to display student identification numbers.

Steps:

  • Select cells from C5 to C14.

Apply the Custom Format to Make a Numbered List in Excel

  • Right-click and choose Format Cells.

Apply the Custom Format to Make a Numbered List in Excel

  • In the Format Cells dialog box, go to the Number tab.
  • Select Custom from the Category list.
  • In the Type box, enter “ @” (a bullet followed by the actual value).
  • Click OK.

  • The cells will display the custom numbered list format as shown in the screenshot.

Apply the Custom Format to Make a Numbered List in Excel

Read More: How to Generate List Based on Criteria in Excel


Method 4 – Using the Flash Fill Option to Make a Numbered List in Excel

Scenario: We want to create a numbered list using the Flash Fill command.

Objective: Automatically generate student identification numbers.

Steps:

  • Manually insert Michael’s identification number (5001) in cell D5.

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

  • From the Home tab, go to Editing, select Fill and click on Flash Fill.

  • The Flash Fill option will automatically generate the remaining numbered list based on the pattern you’ve set.

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


Method 5 – Inserting the OFFSET Function to Make a Numbered List in Excel

Scenario: We want to create a numbered list using the OFFSET function.

Objective: Generate a series of numbers starting from 1.

Steps:

  • Select cell D5.

Insert the OFFSET Function to Make a Numbered List in Excel

=OFFSET(D5,-1,1)+1
  • D5 is the starting cell reference.
  • -1 indicates moving one row upward.
  • 1 indicates moving one column to the right.
  • +1 ensures the numbering starts from 1.

Insert the OFFSET Function to Make a Numbered List in Excel

  • Press Enter.

  • AutoFill the OFFSET function down the column to create the desired numbered list.

Read More: How to Create List from Range in Excel


Method 6 – Applying the ROW Function to Make a Numbered List in Excel

Scenario: We want to create a numbered list using the ROW function.

Objective: Generate a series of numbers starting from 1.

Steps:

  • Select an empty cell (e.g., D5) where you want to create the list.

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

  • In the Formula Bar, insert
=ROW()
  • The ROW function will return the row number.

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

  • Press Enter to get the row number (which is 5 in this case).

  • Drag the AutoFill handle downward to fill in the remaining numbers.

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


Method 7 – Applying the CHAR Function to Make a Numbered List in Excel

Scenario: We want to create a numbered list using the CHAR function.

Objective: Generate a series of numbers from 1 to 9.

Steps:

  • Select cell D5.

Apply the CHAR Function to Make a Numbered List in Excel

  • In the Formula Bar, insert:
=CHAR(49)

(which represents the character “1”).

Apply the CHAR Function to Make a Numbered List in Excel

  • Press Enter to get the number 1.

Apply the CHAR Function to Make a Numbered List in Excel

  • Manually insert the CHAR function’s arguments from 50 to 57 to get numbers 2 to 9 in cells D6 to D13.

Read More: How to Make a To Do List in Excel


Method 8 – Running a VBA Code to Make a Numbered List in Excel

Scenario: We want to create a numbered list using a VBA macro.

Objective: Generate a series of numbers from 1 to 10.

Steps:

  • Open the Visual Basic for Applications (VBA) editor by going to Developer and selecting Visual Basic.

Run a VBA Code to Make a Numbered List in Excel

Run a VBA Code to Make a Numbered List in Excel

  • Insert a new module (go to Insert and click on Module).

Run a VBA Code to Make a Numbered List in Excel

  • Copy and paste the following VBA code into the module:
Sub Make_Numbered_List()
For i = 1 To 10
Range("B" & i) = i
Next
End Sub

  • Run the code by going to Run and selecting Run Sub/UserForm.

  • The numbers 1 to 10 will appear in column B.

Run a VBA Code to Make a Numbered List in Excel


Things to Remember

Remember to manually insert a cell value before applying the Flash Fill option.


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Make List in Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo