How to Auto Generate Number Sequence in Excel (9 Examples)

In this sheet, we have a table of 5 people with their ages. Let’s auto-generate numbers for each of them. Generally, the serial numbers or IDs go to the left of the data. But as we intend to look at different ways of generating numbers, we will put them on the right side.

Method 1 – Applying Basic Addition Method for Excel Auto Generate Number Sequence

Steps:

Select the cell D5 and put the value as 1.
Write the following formula on cell D6:

=D5+1

Press Enter and use the AutoFill tool to apply the formula to the rest of the column.

You will get your desired sequence.

Method 2 – Using Excel AutoFill Method to Auto Generate Number Sequence

If you want to use the AutoFill method, you have two options.

Option 2.1 Applying Excel Fill Handle Tool to Generate Number Sequence Automatically

Steps:

Select cell D5 and put 1.
Go to cell D6 and put 2.
Select both cells, and you will see that Filler icon at the bottom right.

Drag it down in order to complete the operation.

Option 2.2 Using Fill Series to Auto Generate Number Sequence in Excel

Steps:

Select cell D5 and input 1 as the value.
Go to the Editing command or toolbar from the Home tab and select Series from the Fill option.

Set Columns under Series in and input Step value: 1 and Stop value: 5.
Press OK.

Excel will fill in the sequence.

Method 3 – Inserting Excel ROW Function to Auto Generate Number Sequence

Steps:

Write the following formula in cell D5.

=ROW(A1)

Use the AutoFill tool to the entire column to get the final result.

Method 4 – Applying Excel COUNTA Function for Auto Generate Number Sequence

Steps:

Write the following formula in cell D5.

=COUNTA($B$5:B5)

Hit the Enter key and utilize the AutoFill tool to get the final result.

Method 5 – Using OFFSET Function in Excel for Getting Number Sequence Automatically

Steps:

Insert the following formula in cell D5.

=OFFSET(E5,-1,0)+1

Here, you have to select an empty cell for the first value. Otherwise, it will show an error.

Select cell D6 and insert the formula below.

=OFFSET(D6,-1,0)+1

Apply the AutoFill tool, and the output will look as below.

Method 6 – Inserting Excel SEQUENCE Function to Auto Generate Number

Steps:

Write down the following formula in cell D5.

=SEQUENCE(5)

It will provide the final result.

Method 7 – Creating Repeated Number Sequence Automatically in Excel

Steps:

Click on cell D5 and put the value as 1.
Enter the following formula on cell D6 and press Enter.

=IF(D5=3,1,D5+1)

Apply the AutoFill tool to get the final sequence.

Method 8 – Generating Auto Number Sequence in Excel Ignoring Blank Cells

Let’s put some blank rows into the table to demonstrate.

Steps:

Write down the following formula in cell D5.

=IF(C5<>"",COUNTA($C$5:C5),"")

Formula Breakdown

The formula uses the IF and COUNTA functions and returns the serial number. Here’s how this formula works:

COUNTA($C$5:C5)—-> counts the cell C5.
- Output:1
=IF(C5<>””,COUNTA($C$5:C5),””)—-> checks the empty cell and returns the serial number as 1 for non-empty cell and nothing for empty cell.
- Output:1

Hit the Enter key and utilize the AutoFill tool to the whole column.

Here’s how the result should look.

Method 9 – Using Excel Named Range Tool to Auto Generate Number Sequence

Steps:

Go to the Formulas tab and select Define Name.

Write UP as the Name and insert the following formula into Refers to:

=INDIRECT("R[-1]C",FALSE )

Press OK.

Select cell D5 and enter the formula below.

=SUM(UP,1 )

Hit Enter and fill the rest of the cells with the Fill Handle tool.

Bonus – How to Auto Generate Number Sequence with Text in Excel

Steps:

Type the following formula in cell D5.

=B5&TEXT(ROW(A1)-1,"-00-")&C5

Formula Breakdown

The formula uses the TEXT and ROW functions and returns the username. Here’s how this formula works:

ROW(A1)—-> returns the row number of cell A1, which is 1.
ROW(A1)-1—-> becomes
- Output:0
TEXT(0,”-00-“)—-> turns into the text format of 0.
TEXT(ROW(A1)-1,”-00-“)—-> simplifies to
- Output: ‘-00-’
B5&TEXT(ROW(A1)-1,”-00-“)&C5—-> results into
“Lynn” & “-00-” & “25 Years”—-> returns the string.
- Output: Lynn-00-25 Years