How to Increment Row Number in Excel Formula (6 Handy Ways)

Tired of incrementing row numbers manually in Excel? Then you’re in luck because I’ll show you 6 useful methods of how to increment row number in Excel formula.

Download Practice Workbook

You can download the practice workbook from the link below.

6 Ways to Increment Row Number in Excel Formula 

Incrementing row numbers in Excel is a simple yet time-consuming task and one that is done often. Now, knowing the proper way of doing this saves a lot of time and effort. So, without further delay, let’s see the methods one by one.
Throughout this article, we’ll be using the dataset containing the List of Items shown in the B4:C13 cells below. Here, the dataset shows the Category and the Item names respectively.

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.

Method-1: Adding 1 to Increment Row Number in Excel

Let’s start things off with the easiest and the most obvious method i.e. adding 1 to increase the row number. So, follow these simple steps.

Steps:

• Initially, type 1 in the B5 cell and press ENTER on your keyboard.
• Then, go to the B6 cell and insert the formula given below.

Here, the B5 cell refers to Serial Number 1.

• Next, use the Fill Handle Tool to copy the formula into the cells below.

You should get the results shown in the image below.

Read More: How to Add Numbers 1 2 3 in Excel (2 Suitable Cases)

Method-2: Utilizing ROW Function

What if you want to increment the row number according to the Product ID? Then, the ROW function has you covered. Generally speaking, the ROW function returns the reference of a cell but if no argument is given then it returns the reference of the present cell. So, let’s see it in action.

Steps:

• At the very beginning, move to the B5 cell and enter the expression given below.

• Finally, copy the formula to complete the table as shown in the picture below.

Read More: Auto Numbering in Excel After Row Insert (5 Suitable Examples)

Method-3: Increasing Row Number in Excel Formula Using SUBTOTAL Function

Another way to increment the row numbers in Excel is to use the versatile SUBTOTAL function. The SUBTOTAL function simply returns the total of the given values. So, just follow along.

Steps:

• To start, go to the B5 cell and type in the following formula

Here, the C5 cell represents the Category of Kitchen.

Note: Please make sure to provide an Absolute Cell Reference for the first C5 cell using the F4 key.

• Lastly, use the Fill Handle tool to drag the formula to the cells below.

The results should look like the following screenshot.

Read More: Subtotal Formula in Excel for Serial Number (3 Suitable Examples)

Similar Readings

• How to Find Row Number Using VBA in Excel (4 Macros)
• How to Get Row Number of Current Cell in Excel (4 Quick Ways)
• Find String in Column and Return Row Number in Excel (7 Ways)
• How to Get Row Number from Cell Value in Excel (5 Methods)
• Excel VBA: Return Row Number of Value (5 Suitable Methods)

Method-4: Using Table Option to Increment Row Number

By now you’ve guessed that you can also use Excel Table to increment the row numbers. Allow me to demonstrate the procedure below.

Steps:

• Firstly, select any cell within the dataset. In this case, we chose the B5 cell.
• Then, navigate to the Insert tab >> click on the Table button.

After completing this step, the Create Table dialog box opens up.

• Here, the B4:D13 range of cells is selected automatically.
• Following this, make sure to check the My table has headers option.
• Now, click the OK button.

• Next, insert the following expression in the B5 cell of the table.

• In turn, press the ENTER key and the Serial Number column will fill up automatically.

Read More: How to Create a Number Sequence in Excel Without Dragging

Method-5: Applying COUNTA Function to Increase Row Number

Now, what if our dataset has blank rows in between and we don’t want to number those rows? Then, you’re looking at the right place since we can combine the IF function with the COUNTA function to answer your question. So, let’s see the process in detail.

Steps:

• Firstly, select the B5 cell in the dataset
• Next, enter the following expression given below.

In the above formula, the C5 cell points to the Category of Kitchen.

Eventually, the results should look like the image shown below.

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

Method-6: Increment of Specific Number of Rows with ROW and OFFSET Functions

Our last method will be helpful to you if you need to add up a certain number of rows.
Here, we’ll combine the ROW and OFFSET functions to obtain the Sub Total Sales for each Category. So let’s begin.

Steps:

• Firstly, move to the G4 cell in the dataset and type in the expression given below.

Here, the D4 cell represents the column heading for Sales.

• Similarly, copy the formula for obtaining the Sub Total for Office.

• Likewise, repeat the same process for Sub Total for Living Room.

• Lastly, we use the SUM function to add the values in the G4:G6 range of cells to obtain the Grand Total.

This gives the results shown in the picture below.

Read More: How to Create a Number Sequence with Formula in Excel

How to Add Automatic Serial Number in Excel

For one thing, we can add automatic serial numbers using the COUNTA and SEQUENCE functions where the COUNTA function counts the number of non-blank cells and the SEQUENCE function returns a sequential list of numbers.

Steps:

• To begin with, jump to the B5 cell >> insert the following expression and follow the animated GIF below.

Here, the C5:C13 array indicate the Category column.

📃 Note: The SEQUENCE function is available on the newer Microsoft Excel 365 version, if you’re using an older version of Excel, then please check for compatibility.

Admittedly, we’ve skipped other relevant examples of adding automatic serial numbers in Excel, which you may explore if you wish.

How to Increment Row Number without Dragging in Excel

Last but not least, if there are lots of rows then to increment row number in Excel with formula can become a hassle. Instead, the next method shows a nifty feature of Excel. Therefore, just follow along.

Steps:

• First and foremost, enter 1 in the B5 cell >> proceed to the Fill drop-down and choose the Series option >> then follow the steps in the GIF below.

• Likewise, we can also apply this technique for incrementing across Columns.

Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

Conclusion

This article provides quick and easy answers to how to increment row number in Excel with formula. Make sure to download the practice files. Hope you found it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are happy to answer your queries. Keep learning and keep growing!