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.

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 the function_num argument which relates to the COUNTA function.
COUNTA function counts all cells that are not blank.
$C$5:C5 indicates the ref1 argument (the range of cells that we want to count).
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:

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 the reference argument of the second 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 the logical_test argument that checks if 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