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.

Dataset 1

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.

Adding One

How to Increment Row Number in Excel Formula by Adding One

You should get the results shown in the image below.

How to Increment Row Number in Excel Formula by Adding One

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.

="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.

Using ROW Function

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

How to Increment Row Number in Excel Formula Using ROW Function

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

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

How to Increment Row Number in Excel Formula Using SUBTOTAL Function

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

The results should look like the following screenshot.

How to Increment Row Number in Excel Formula Using SUBTOTAL Function

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


Method-4: Using Table Option to Increment Row Number

By now you’ve guessed that you 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.

Using Excel Table

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.

Using Excel Table

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

How to Increment Row Number in Excel Formula Using Excel Table

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

How to Increment Row Number in Excel Formula Using Excel Table

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.

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

Using COUNTA Function

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

How to Increment Row Number in Excel Formula Using COUNTA Function

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.

=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

Using ROW and OFFSET Functions

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

How to Increment Row Number in Excel Formula Using ROW and OFFSET Functions

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

How to Increment Row Number in Excel Formula Using ROW and OFFSET Functions

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

How to Increment Row Number in Excel Formula Using ROW and OFFSET Functions

This gives the results shown in the picture below.

How to Increment Row Number in Excel Formula Using ROW and OFFSET Functions

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


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.

Practice Section


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!


Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo