Durjoy Paul
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.
Download Practice Workbook
Download the practice workbook below.
5 Suitable Examples to Create Auto Numbering in Excel After Row Insert
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.
Read More: How to Increment Row Number in Excel Formula (6 Handy Ways)
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 Create a Number Sequence 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: How to Add Numbers 1 2 3 in Excel (2 Suitable Cases)
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. 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 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.
Read More: How to Create a Number Sequence in Excel Without Dragging
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.
Read More: Auto Generate Serial Number in Excel VBA (4 Ways)
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.
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. Don’t forget to visit our Exceldemy page.
