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

Use the dataset containing the **List of Items** from **B4:C13** in the table below. The dataset shows **Category **and **Item** names.

We have used *Microsoft Excel 365*. Other versions can be used.

**Method 1 – Adding 1 to Increment the Row Number in Excel**

** Steps**:

- Enter
**1**in**B5**and press**ENTER**. - Go to
**B6**and enter the formula below.

`=B5+1`

**B5** refers to **Serial Number 1**.

- Copy the formula across the cells you want to fill.

The table below showcases the result.

**Method 2 – Using the ROW Function**

** Steps**:

- Enter the following in
**B5:**

`="ID-00"&ROW()-4`

**Formula Breakdown:**

- “
**ID-00**” represents a string of text. - Since no arguments are given in the
**ROW**function, it returns the current row number- 5. - Subtract
**4**from**5**to return the result:**1**. - Text and value are concatenated using
**Ampersand**(**&**).

- Use the
**Fill Handle**to drag the formula across the cells you want to fill.

The result should be the following:

**Method 3 – Increasing the Row Number Using the SUBTOTAL Function in the Formula**

** Steps**:

- Go to
**B5**and enter the following formula:

`=SUBTOTAL(3,$C$5:C5)`

**C5 **represents the **Category **of** Kitchen**.

**Formula Breakdown:**

**3**refers to theargument which relates to*function_num***the COUNTA function**.**COUNTA**function counts all cells that are not blank.**$C$5:C5**indicates theargument (the range of cells that we want to count).*ref1***Absolute Cell Reference**is used for the first cell reference (**$C$5**) only. Last cell reference (**C5**) changes according to the row containing the formula.

*Note:** Make sure to provide an Absolute Cell Reference for C5 using F4 on your keyboard.*

- Use the
**Fill Handle**to drag the formula across the cells you want to fill.

The result should be the following:

**Read More: **Subtotal Formula in Excel for Serial Number

**Method 4 – Using the Table Option to Increment the Row Number**

** Steps**:

- Select any cell within the dataset.
**B5**here. - In the
**Insert**tab >> click**Table**.

In the **Create Table** dialog box:

**B4:D13**range is selected automatically.- Check
**My table has headers**. - Click
**OK**.

- Enter the following in
**B5**.

`=ROW()-ROW(Table1[#Headers])`

**Formula Breakdown:**

**Table1[#Headers]**is theargument of the second*reference***ROW**function which returns the row number of the table header.- The first
**ROW**function still returns the row number of the current cell. - Subtracting the row number of the table header from the current cell returns the
**Serial Number**.

- Press
**ENTER**and the*Serial Number*column will automatically fill.

**Method 5 – Applying the COUNTA Function to Increase the Row Number**

** Steps**:

- Select
**B5**cell. - Enter the following:

`=IF(ISBLANK(C5),"",COUNTA($C$5:C5))`

**C5 **refers to the **Category **of** Kitchen**.

**Formula Breakdown:**

**ISBLANK(C5)**is theargument that checks if*logical_test***C5**is blank.-
**“”**is the**value_if_true**argument which returns blank if the logical test is**TRUE**. **COUNTA($C$5:C5)**is used as the**value_if_false**argument which is returned if the logical test is**FALSE**.- The
**COUNTA**function counts all non-blank cells in the given range.

The result should be the following:

**Method 6 – Increment a Specific Number of Rows with the ROW and OFFSET Functions**

** Steps**:

- Go to
**G4**and enter the following:

`=OFFSET($D$4,(ROW()-3)*4,0)`

**D4** 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. The function looks up**Column D**.**Output → $1,900**

- Copy the formula to obtain the
**Sub Total for Office**.

- Repeat the process for
*Sub Total for Living Room.*

**The SUM function**is used to add the values in the**G4:G6**range and return the**Grand Total**.

The result should be the following:

**Practice Section**

Practise using the table below:

**Download Practice Workbook**

You can download the practice workbook from the link below.

**Related Articles **

- How to Perform Numbering in One Cell in Excel
- How to Add Numbers 1 2 3 in Excel
- How to Create a Number Sequence with Text in Excel
- How to Create a Number Sequence in Excel Based on Criteria
- How to Use Number with Filter in Excel

**<< Go Back to Serial Number in Excel | Numbering in Excel | Learn Excel**