When you work with a dataset where you need to insert more data but the numbering is not changing accordingly. Now, it is very time-consuming to use a manual process to number them from the beginning. In Microsoft Excel, you can easily create auto numbering after row insert. In this article, we will show how to create auto numbering in Excel after row insert. I hope you find this article very informative and gain some valuable knowledge regarding the auto numbering process.

## Auto Numbering in Excel After Row Insert: 5 Suitable Examples

To show auto numbering in Excel after row insert, we have found five different methods through which youâ€™ll have a clear idea of the auto numbering process. While solving the issue, we use several Excel functions. All of these are fairly helpful in our day-to-day use. Finally, if you insert any row, it will show the auto numbering along with the change.

### 1. Using ROW Function

Our first method is based on using** the ROW function**. We basically create some numbers using the **ROW** function. Â After that, we convert that dataset into a table. Then, if we insert a new row, it will show the auto-numbering sequence. To understand the method clearly, you need to follow the steps.

**Steps**

- First, we need to convert our dataset into a table.
- To do this, we select the range of cells
**B4**to**F12**.

- Then, go to the
**Insert**tab in the ribbon. - From the
**Tables**group, select the**TableÂ**option.

- Then, the
**Create Table**dialog box will appear. - Click on
**OK**.

- As a result, we will get the following table. See the screenshot.

- Then, select cell
**B5**.

- Write down the following formula in the formula box. Here, we subtract 4 because only the
**ROW**function will give us the existing row. By subtracting 4, we can get the first number.

`=ROW()-4`

- Press
**Enter**and it will automatically fill all the blank cells in**Product No.**

- Then, if we insert a new row, it will automatically change the numbering. See the screenshot.

### 2. Applying COUNTA Function

Our second method is to show the auto numbering in Excel after row insert by using **the COUNTA function**. In this method, we create numbering using the **COUNTA** function after that we convert the dataset into a table. Then, if you insert a new row, it will show the required auto number.Â To understand the method clearly, you need to follow the steps.

**Steps**

- First, select cell
**B5**.

- Then, write down the following formula.

`=COUNTA($C$5:C5)`

- Press
**Enter**to apply the formula.

- Then, drag the
**Fill Handle**icon down the column.

- After that, we need to convert the dataset into a table.
- To do this, we select the range of cells
**B4**to**F12**.

- Then, go to the
**Insert**tab in the ribbon. - From the
**Tables**group, select the**TableÂ**option.

- Then, the
**Create Table**dialog box will appear. - Click on
**OK**.

- As a result, we will get the following table. See the screenshot.

- Then, if we insert a new row, it will automatically show the auto-numbering sequence. See the screenshot.

**Read More: **How to Add Automatic Serial Number with Formula in Excel

### 3. Adding Number

Our fourth method is basically adding numbers in a cell. In this method, we enter the first number in a cell. Then, use this cell reference and add 1 for other cells. After that, we convert this into a table. Then, if you insert any row, it will show the auto-numbering sequence. To understand the method clearly, you need to follow the steps.

**Steps**

- First, put value
**1**in cell**B5**.

- Then, select cell
**B6**.

- Then, write down the following formula.

`=B5+1`

- Press
**Enter**to apply the formula.

- Then, drag the
**Fill Handle**icon down the column.

- After that, we need to convert the dataset into a table.
- To do this, we select the range of cells
**B4**to**F12**.

- Then, go to the
**Insert**tab in the ribbon. - From the
**Tables**group, select the**TableÂ**option.

- Then, the
**Create Table**dialog box will appear. - Click on
**OK**.

- As a result, we will get the following table. See the screenshot.

- Then, if we insert a new row, it will automatically show the auto-numbering sequence. See the screenshot.

**Read More: **Automatically Number Rows in Excel

### 4. Using Excel Table Formula

In our fifth method, we convert our dataset into a table. Then, use that table reference in a formula. Finally, If we insert any row, it will automatically show an auto-numbering sequence. Let’s follow the instructions below to create a number sequence in Excel without dragging.

**Steps**Â

- First, we need to convert our dataset into a table.
- To do this, we select the range of cells
**B4**to**F12**.

- Then, go to the
**Insert**tab in the ribbon. - From the
**Tables**group, select the**TableÂ**option.

- Then, the
**Create Table**dialog box will appear. - Click on
**OK**.

- As a result, we will get the following table. See the screenshot.

- Then, select cell
**B5**.

- Write down the following formula in the formula box. Here, we create the table whose name is
**Table6**.

`=ROW()-ROW(Table6[#All])`

- Press
**Enter**and It will automatically fill all the blank cells in**Product No.**

- Then, if we insert a new row, it will automatically change the numbering. See the screenshot.

### 5. Implementing OFFSET Function

Our final method is based on using **the OFFSET function**. In this method, we created the numbering using the **OFFSET **function. After that, we convert that dataset into a table. Then, if you insert a new row, it will show auto numbering in the table. To understand the method properly, you need to follow the steps properly.

**Steps**

- First, put value
**1**in cell**B5**.

- Then, select cell
**B6**. - Then, write down the following formula.

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

- Press
**Enter**to apply the formula.

- Then, drag the
**Fill Handle**icon down the column.

- After that, we need to convert the dataset into a table.
- To do this, we select the range of cells
**B4**to**F12**.

- Then, go to the
**Insert**tab in the ribbon. - From the
**Tables**group, select the**TableÂ**chart.

- Then, the
**Create Table**dialog box will appear. - Click on
**OK**.

- As a result, we will get the following table. See the screenshot.

- Then, if we insert a new row, it will automatically show the auto-numbering sequence. See the screenshot.

## Things to Remember

- When you need to use a cell reference in your formula, we have to convert the dataset into a table after using the formula. Otherwise, you may face some difficulties.
- In the case of the
**OFFSET**function, you need to put 1 value in the first cell after that you can apply the formula.

**Download Practice Workbook**

Download the practice workbook below.

## Conclusion

We have shown five different methods to create auto numbering in Excel after row insert. All of these methods are really easy to use and fairly easy to understand. We create these auto numbering after row inserts by using several Excel functions. I hope we cover all the possible areas of these topics. If you have any further questions, feel free to ask in the comment box.

**Related Articles**

- How to Auto Generate Number Sequence in Excel
- How to Autofill in Excel with Repeated Sequential Numbers
- 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**