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.
- 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.
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.
- At the very beginning, move to the B5 cell and enter the expression given below.
- 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.
- To start, go to the B5 cell and type in the following formula
Here, the C5 cell represents the Category of Kitchen.
- 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.
- 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.
- 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.
- 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 in detail.
- 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.
- 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.
- 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.
- 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.
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.
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!
- Auto Serial Number in Excel Based on Another Column
- Autofill in Excel with Repeated Sequential Numbers
- Create a Number Sequence with Text in Excel (5 Methods)
- Auto Generate Serial Number in Excel VBA (4 Ways)
- How to Create a Number Sequence in Excel Based on Criteria
- How to Use Excel Formula to Find Last Row Number with Data (2 Ways)