# Auto Numbering in Excel After Row Insert (5 Suitable Examples)

### Example 1 – Using the ROW Function

Steps

• Select the range of cells B4:F12.

• Go to the Insert tab in the ribbon.
• From the Tables group, select the TableÂ option.

• The Create Table dialog box will appear.
• Click on OK.

• We will get the following table.

• Select cell B5.

• Use the following formula in the formula box.
`=ROW()-4`

Here, we subtract 4 because the ROW function will give us the existing row. By subtracting 4, we can get the first number.

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

• If we insert a new row, it will automatically change the numbering.

### Example 2 – Applying the COUNTA Function

Steps

• Select cell B5.

• Use the following formula.
`=COUNTA(\$C\$5:C5)`

• Press Enter to apply the formula.

• Drag the Fill Handle icon down the column.

• Select the range of cells B4:F12.

• Go to the Insert tab in the ribbon.
• From the Tables group, select the TableÂ option.

• The Create Table dialog box will appear.
• Click on OK.

• We will get the following table.

• If we insert a new row, it will automatically update the auto-numbering sequence.

Steps

• Put the value 1 in cell B5.

• Select cell B6.

• Use the following formula.
`=B5+1`

• Press Enter to apply the formula.

• Drag the Fill Handle icon down the column.

• Select the dataset.

• 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.

• Here’s the resulting table.

• If we insert a new row, it will automatically update the auto-numbering sequence.

Read More: Automatically Number Rows in Excel

### Example 4 – Using an Excel Table Formula

StepsÂ

• Select the dataset.

• Go to the Insert tab in the ribbon.
• From the Tables group, select the TableÂ option.

• The Create Table dialog box will appear.
• Click on OK.

• We will get the following table.

• Select cell B5.

• Use the following formula in the formula box.
`=ROW()-ROW(Table6[#All])`

Change the table name from Table6 to the name of your table (listed in the ribbon).

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

• If we insert a new row, Excel will automatically change the numbering.

### Example 5 – Implementing the OFFSET Function

Steps

• Put value 1 in cell B5.

• Select cell B6.
• Insert the following formula:
`=OFFSET(B6,-1,0)+1`

• Press Enter to apply the formula.

• Drag the Fill Handle icon down the column.

• Select the dataset.

• Go to the Insert tab in the ribbon.
• From the Tables group, select the TableÂ chart.

• The Create Table dialog box will appear.
• Click on OK.

• We will get the table.

• If we insert a new row, Excel will automatically update the auto-numbering sequence.

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF