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.

**Table of Contents**hide

**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

__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

__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

__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 theargument which points to*function_num***the COUNTA function**. - Now, the
**COUNTA**function counts all the cells that are not blank. - Next, the
**$C$5:C5**indicates theargument which is the range of cells that we want to count.*ref1* - 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**

__Method-4__: Using Table Option

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 theargument of the second*reference***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

__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 theargument.*logical_test* - 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

__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) →**returns a range of cells from the specified rows and columns. Where**The OFFSET function****4**is theargument which tells the function how many rows to move from the initial reference*rows***$D$4**and**0**represents theargument which specifies the column from starting point. In this case, the function looks up in the same*columns***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 theargument that refers to the**value1****Category**column.**Output → 9**

**SEQUENCE(COUNTA(C5:C13)) →**becomes**SEQUENCE(9) → returns a sequence of numbers. Here,****9**is theargument.*rows***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!