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.
How to Increment Row Number in Excel Formula: 6 Handy Ways
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.
=B5+1
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.
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.
="ID-00"&ROW()-4
Formula Breakdown:
- In this formula, the “ID-00” represents a string of text.
- Now, since no arguments are given in the ROW function so it returns the current row number of 5.
- Next, we subtract 4 from 5 to return the result 1.
- Lastly, the text and the value are concatenated using the Ampersand (&) operator.
- Finally, copy the formula to complete the table as shown in the picture below.
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
=SUBTOTAL(3,$C$5:C5)
Here, the C5 cell represents the Category of Kitchen.
Formula Breakdown:
- In this expression, 3 refers to the function_num argument which points to the COUNTA function.
- Now, the COUNTA function counts all the cells that are not blank.
- Next, the $C$5:C5 indicates the ref1 argument which is the range of cells that we want to count.
- Moreover, we used Absolute Cell Reference for the first cell reference ($C$5) only. So, the last cell reference (C5) changes according to the row where the formula is copied.
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
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. You can also add auto-numbering in Excel after inserting a Row into a table.
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.
=ROW()-ROW(Table1[#Headers])
Formula Breakdown:
- Here, Table1[#Headers] refers to the reference argument of the second ROW function which returns the row number of the table header.
- However, the first ROW function returns the row number of the current cell.
- Lastly, subtracting the row number of the table header from the current cell returns the Serial Number.
- In turn, press the ENTER key and the Serial Number column will fill up automatically.
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 to increase the number of rows automatically in Excel.
Steps:
- Firstly, select the B5 cell in the dataset
- Next, enter the following expression given below.
=IF(ISBLANK(C5),"",COUNTA($C$5:C5))
In the above formula, the C5 cell points to the Category of Kitchen.
Formula Breakdown:
- In the formula for Serial Number, the ISBLANK(C5) expression is the logical_test argument.
- This expression checks if the C5 cell is blank.
- Secondly, “” is the value_if_true argument which returns blank if the logical test is TRUE.
- Lastly, COUNTA($C$5:C5) serves as the value_if_false argument which is returned if the logical test is FALSE.
- Additionally, the COUNTA function counts all the non-blank cells in the given range.
Eventually, the results should look like the image shown below.
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.
=OFFSET($D$4,(ROW()-3)*4,0)
Here, the D4 cell represents the column heading for Sales.
Formula Breakdown:
- ROW() → returns the serial number of the row in cell G4.
- Output → 4
- ROW()-3 → becomes
- 4-3 → 1
- (ROW()-3)*4 → becomes
- 1*4 → 4
- OFFSET($D$4,(ROW()-3)*4,0) → becomes
- OFFSET($D$4,4,0) → The OFFSET function returns a range of cells from the specified rows and columns. Where 4 is the rows argument which tells the function how many rows to move from the initial reference $D$4 and 0 represents the columns argument which specifies the column from starting point. In this case, the function looks up in the same Column D.
- Output → $1,900
- 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.
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.
=SEQUENCE(COUNTA(C5:C13))
Here, the C5:C13 array indicate the Category column.
Formula Breakdown:
- COUNTA(C5:C13) → counts the number of cells in a range that are not empty. Here, the C5:C13 is the value1 argument that refers to the Category column.
- Output → 9
- SEQUENCE(COUNTA(C5:C13)) → becomes
- SEQUENCE(9) → returns a sequence of numbers. Here, 9 is the rows argument.
- Output → {1;2;3;4;5;6;7;8;9}
📃 Note: The SEQUENCE function is available on the newer Microsoft 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 with formula, 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.
Read More: How to Create a Number Sequence in Excel Without Dragging
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.
Download Practice Workbook
You can download the practice workbook from the link below.
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!
