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**

- Output:
**=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**

- Output:

- 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**

- Output:
**TEXT(0,”-00-“)—->**turns into the text format of**0**.**TEXT(ROW(A1)-1,”-00-“)—->**simplifies to- Output: ‘
**-00-**’

- Output: ‘
**B5&TEXT(ROW(A1)-1,”-00-“)&C5—->**results into**“Lynn” & “-00-” & “25 Years”—->**returns the string.- Output:
**Lynn-00-25 Years**

- Output:

- Use the AutoFill tool in order to apply the same formula to the entire column.

**Download Practice Workbook**

You can download the workbook used for these methods from the download link below.

## Related Articles

- Auto Numbering in Excel After Row Insert
- How to Number Columns in Excel Automatically
- Auto Serial Number in Excel Based on Another Column
- Auto Generate Invoice Number in Excel
- Auto Generate Serial Number in Excel VBA

**<< Go Back to Serial Number in Excel | Numbering in Excel | Learn Excel**