Often, serializing a lot of elements manually in Excel is boring. Fortunately, it is pretty easy to auto-generate number sequences in Excel. To get rid of this monotonous issue, I will show you some easy and handy ways to auto-generate a number sequence in Excel. For the purpose of demonstration, I’m using Microsoft Office 365. No worries if you have another version.

**Watch Video – Auto Generate Number Sequence in Excel**

First things first, let’s get to know about the worksheet, which is the basis of our examples today. In this sheet, we have a table of 5 people with their ages. Using this, you will see how to auto generate numbers for each of them. Generally, you see the serial number that belongs to the left of any particular element. But as we intend to look at different ways of generating numbers, we are keeping the methods on the right side. For the purpose of demonstration, I have used the following sample dataset.

**Table of Contents**hide

## 1. Applying Basic Addition Method for Excel Auto Generate Number Sequence

In this serialization method, we will increment the previous row number by 1. So, we will add 1 to the previous row number to our current cell.

**Steps:**

- Firstly, select the cell
**D5**and put the value as 1. - Secondly, write the following formula on cell
**D6**.

`=D5+1`

- Thirdly, press the
**Enter**button and use the**AutoFill**tool to apply the formula to the rest of the column.

- Finally, you will get your desired sequence.

**Read More: ****AutoFill Formula to Last Row with Excel VBA (5 Examples)**

## 2. Using Excel AutoFill Method to Auto Generate Number Sequence

If you want to use the **AutoFill** method, you have two options. However, it includes the **Fill Handle** and **Fill Series**. Hence, go through the below parts in order to apply them to our data.

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

In general, you have to insert a value into a row and select it as an active cell. Then you will see a small circular box at the bottom right to complete the operation.

**Steps:**

- Initially, select the active cell
**D5**and put 1. - Similarly, select cell
**D6**and put 2. - After that, select the cells, and you will see that
**Filler**icon at the bottom right.

- Lastly, drag it down in order to complete the operation.

**Read More: ****[Fixed!] AutoFill Formula Is Not Working in Excel Table (3 Solutions)**

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

For the Fill Series method, you don’t need to insert two values as previously. Simply Insert the value into one row, and the rest of the part will be completed automatically.

**Steps:**

- At first, select cell
**D5**and input 1 as the value. - Then, go to the
**Editing**command from the**Home**tab and select**Series**from the**Fill**option.

- Here, set
**Columns**under**Series**and**Step value:**1 and**Stop value:**5 from the**Series****Dialog Box**. - Next, press
**ok**.

- Finally, you will find the sequence.

**Read More: How to Auto Number Cells in Excel (10 Methods)**

## 3. Inserting Excel ROW Function to Auto Generate Number Sequence

Usually, **The ROW function** returns the row number of the cell you are presently in (active cell). So, select an empty row from the first in order to get 1 anywhere in the dataset.

**Steps:**

- Firstly, write the following formula in cell
**D5**.

`=ROW(A1)`

- Finally, use the
**AutoFill**tool to the entire column to get the final result.

**Read More: ****Drag Number Increase Not Working in Excel (A Solution with Easy Steps)**

## 4. Applying Excel COUNTA Function for Auto Generate Number Sequence

Furthermore, another method worth mentioning here is **the COUNTA function**. However, the **COUNTA** function counts the number of cells that are not empty within a range. Moreover, this method will help you serialize the rows that are not empty.

**Steps:**

- Initially, write the following formula in cell
**D5**.

`=COUNTA($B$5:B5)`

- Lastly, hit the
**Enter**key and utilize the**AutoFill**tool to get the result.

**Read More: ****Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows**

## 5. Using OFFSET Function in Excel for Getting Number Sequence Automatically

Generally, **the OFFSET function** returns the reference of a cell or a range of cells. Here, you have to insert three parameters within an **OFFSET** function. However, go through the steps below to complete the task.

**Steps:**

- At first, 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 errors.

- Again, select cell
**D6**and insert the formula below.

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

- At the end, apply the
**AutoFill**tool, and the output will be as below.

**Read More: ****How to AutoFill Ascending Numbers in Excel (5 Quick Ways)**

**Similar Readings**

**How to Show Grand Total in Pivot Table (3 Easy Methods)****AutoFill Cell Based on Another Cell in Excel (5 Easy Methods)****Use Format Function in Excel (with Suitable Examples)****Auto-Populate Cells Based On Another Cell in Excel****How to Show Percentage in Legend in Excel Pie Chart (with Easy Steps)**

## 6. Inserting Excel SEQUENCE Function to Auto Generate Number

Furthermore, you can generate a number sequence using a function called **SEQUENCE**. Using **the SEQUENCE function**, you have to provide only one parameter, rows.

**Steps:**

- At first, write down the following formula in cell
**D5**.

`=SEQUENCE(5)`

- Lastly, it will provide the final result.

**Read More: ****AutoFill Not Incrementing in Excel? (3 Solutions)**

## 7. Creating Repeated Number Sequence Automatically in Excel

In this part, I will show you how to automatically **create a repeating number sequence**. Usually, it will repeat the number after a particular value. Here, I have used **the IF function**. However, you can follow the steps below to easily complete the task.

**Steps:**

- At first, click on cell
**D5**and put the value as 1. - After that, enter the following formula on cell
**D6**and press**Enter**.

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

- Finally, apply the
**AutoFill**tool to get the final sequence.

**Read More: ****Applications of Excel Fill Series (12 Easy Examples)**

## 8. Generating Auto Number Sequence in Excel Ignoring Blank Cells

Often, you may have to work with a number of sequences with blank cells, and you don’t want to provide a number sequence into the blank cells. Here, I have combined **the IF function** and **the COUNTA function**. However, follow the steps below to create a number sequence ignoring blank cells. For the purpose of demonstration, I have slightly changed the dataset.

**Steps:**

- Initially, 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 noting for empty cell.- Output:
**1**

- Output:

- After that, hit the
**Enter**key and utilize the**AutoFill**tool to the whole column.

- Finally, the output will appear in the following image.

**Read More: How to Add Sequence Number by Group in Excel (2 Ways)**

## 9. Using Excel Named Range Tool to Auto Generate Number Sequence

Last but not the least, you can use a relative reference named range in order to auto-generate number sequence. For this reason, you have to define a particular name as the function. However, go through the following steps.

**Steps:**

- At first, go to the
**Formulas**tab and select the command**Define Name**.

- Secondly, write
**UP**as the**Name**and insert the following formula into**Refers to**.

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

- Thirdly, press
**OK**.

- Again, select cell
**D5**and enter the formula below.

`=SUM(UP,1 )`

- Finally, you will receive the desired output.

**Read More: ****12 Automatic Ways of Numbering in Excel**

## How to Auto Generate Number Sequence with Text in Excel

Moreover, you can use Excel’s **TEXT **and **ROW** functions to create a **number sequence **with text. Let’s go through the description below to complete the task.

**Steps:**

- First, 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 serial with texts.- Output:
**Lynn-00-25 Years**

- Output:

- Lastly, use the AutoFill tool in order to
**apply the same formula to the entire column**.

**Read More:** **How to Combine Text and Numbers in Excel and Keep Formatting**

**Download Practice Workbook**

You can download the workbook used for the demonstration from the download link below.

## Conclusion

These are all the steps you can follow** to auto-generate number sequences in Excel.** Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit **ExcelDemy.com**.

## Related Articles

**Use CODE Function in Excel (5 Examples)****How to Autofill Numbers in Excel Without Dragging (5 Methods)****Use CHAR Function in Excel (6 Suitable Examples)****How to Increment Month by 1 in Excel (8 Handy Ways)****Use TEXT Function to Format Codes in Excel****How to Create Automatic Rolling Months in Excel (3 Quick Ways)****Use FIXED Function in Excel (6 Suitable Examples)**