How to Increment Row Numbers in Excel Formula

Excel is a powerful spreadsheet application that allows you to perform various calculations and data analysis. One of the common tasks in Excel is to increment row numbers in a formula, so that the formula can dynamically adjust to the data in different rows. For example, you may want to assign serial numbers to a list of items, or calculate the cumulative sum of a column of numbers.

There are several ways to increment row numbers in Excel formula, depending on your needs and preferences. In this article, I will show you six handy methods that you can use to achieve this task. I will also explain the basic theory behind each method, and provide a detailed example with real data to illustrate how they work.

Method 1: Adding 1 to Increment Row Number in Excel

The simplest and most obvious method to increment row numbers in Excel formula is to add 1 to the previous row number. This method works well if you want to create a simple sequence of numbers that starts from 1 and increases by 1 at each step.

To use this method, follow these steps:

1. Type 1 in the first cell where you want to start the sequence. For example, if you want to create a column of serial numbers, type 1 in the first cell of that column.
2. In the next cell below, enter the formula =previous cell + 1. For example, if you typed 1 in cell A1, enter the formula =A1+1 in cell A2.
3. Use the fill handle tool to copy the formula to the cells below. To do this, select the cell with the formula, hover over the bottom-right corner of the cell until you see a small black cross, then drag it down to fill the cells below. You should see a sequence of numbers that increments by 1 at each row.

Here is an example of using this method to create a column of serial numbers for a list of items:

The formula in cell A2 is =A1+1, and it is copied down to the cells below.

Method 2: Using ROW Function to Increment Row Number in Excel

Another way to increment row numbers in Excel formula is to use the ROW function. The ROW function returns the row number of a cell reference, or the current row number if no reference is given. For example, ROW(A1) returns 1, and ROW() returns the row number of the cell where the formula is entered.

You can use the ROW function to create a sequence of numbers that matches the row numbers of the cells, or modify the result by adding or subtracting a constant. For example, if you want to create a sequence of numbers that starts from 10 and increases by 1 at each row, you can use the formula =ROW()+9.

To use this method, follow these steps:

1. In the first cell where you want to start the sequence, enter the formula =ROW() or =ROW(reference), where reference is the cell reference that you want to match. For example, if you want to create a sequence of numbers that matches the row numbers of column B, enter the formula =ROW(B1) in the first cell of column A.
2. If you want to modify the result by adding or subtracting a constant, append +constant or -constant to the formula. For example, if you want to create a sequence of numbers that starts from 10 and increases by 1 at each row, enter the formula =ROW()+9 in the first cell of column A.
3. Use the fill handle tool to copy the formula to the cells below. You should see a sequence of numbers that increments by 1 at each row, and matches or differs from the row numbers by the constant that you specified.

Here is an example of using this method to create a column of product IDs for a list of items, where the product ID starts from “ID-001” and increases by 1 at each row:

The formula in cell A2 is ="ID-00"&ROW()-1, and it is copied down to the cells below. The formula concatenates the text “ID-00” with the row number minus 1, using the ampersand (&) operator.

Method 3: Using SUBTOTAL Function to Increment Row Number in Excel

A third way to increment row numbers in Excel formula is to use the SUBTOTAL function. The SUBTOTAL function returns the subtotal of a range of cells, based on a specified function number. For example, SUBTOTAL(9,A1:A10) returns the sum of the values in cells A1 to A10.

You can use the SUBTOTAL function to create a sequence of numbers that counts the non-blank cells in a range, and increments by 1 at each row. This method is useful if you want to create a column of serial numbers that ignores blank rows or hidden rows.

To use this method, follow these steps:

1. In the first cell where you want to start the sequence, enter the formula =SUBTOTAL(3,range), where range is the range of cells that you want to count. For example, if you want to create a column of serial numbers for a list of items in column B, enter the formula =SUBTOTAL(3,$B$1:B1) in the first cell of column A.
2. Use the fill handle tool to copy the formula to the cells below. You should see a sequence of numbers that increments by 1 at each row, and counts the non-blank cells in the range.

Here is an example of using this method to create a column of serial numbers for a list of items, where some rows are blank or hidden:

The formula in cell A2 is =SUBTOTAL(3,$B$1:B2), and it is copied down to the cells below. The formula counts the non-blank cells in column B from row 1 to the current row, using the function number 3, which corresponds to the COUNTA function. Note that the range is fixed for the first cell reference ($B$1), and relative for the second cell reference (B2), so that the range expands as the formula is copied down.

Method 4: Using INDIRECT Function to Increment Row Number in Excel

A fourth way to increment row numbers in Excel formula is to use the INDIRECT function. The INDIRECT function returns the value of a cell reference, given as a text string. For example, INDIRECT("A1") returns the value of cell A1.

You can use the INDIRECT function to create a sequence of numbers that references another cell or range, and increments by 1 at each row. This method is useful if you want to create a column of numbers that depends on the values in another column, or if you want to create a dynamic range that adjusts to the data.

To use this method, follow these steps:

1. In the first cell where you want to start the sequence, enter the formula =INDIRECT(text), where text is the text string that represents the cell reference that you want to return. For example, if you want to create a column of numbers that references the values in column B, enter the formula =INDIRECT("B"&ROW()) in the first cell of column A.
2. Use the fill handle tool to copy the formula to the cells below. You should see a sequence of numbers that increments by 1 at each row, and returns the values in the referenced cell or range.

Here is an example of using this method to create a column of numbers that references the values in column B, where column B contains a series of numbers that increases by 2 at each row:

The formula in cell A2 is =INDIRECT("B"&ROW()), and it is copied down to the cells below. The formula concatenates the letter “B” with the row number, using the ampersand (&) operator, and returns the value of the cell reference given by the text string.

Method 5: Using OFFSET Function to Increment Row Number in Excel

A fifth way to increment row numbers in Excel formula is to use the OFFSET function. The OFFSET function returns a reference to a cell or range, given a starting cell reference and a number of rows and columns to offset. For example, OFFSET(A1,1,0) returns a reference to cell A2, which is one row below and zero columns to the right of cell A1.

You can use the OFFSET function to create a sequence of numbers that offsets a cell or range by a certain number of rows and columns, and increments by 1 at each row. This method is useful if you want to create a column of numbers that refers to a different column or sheet, or if you want to create a dynamic range that adjusts to the data.

To use this method, follow these steps:

1. In the first cell where you want to start the sequence, enter the formula =OFFSET(reference,rows,cols), where reference is the starting cell reference, rows is the number of rows to offset, and cols is the number of columns to offset. For example, if you want to create a column of numbers that refers to the values in column C, enter the formula =OFFSET(C1,0,0) in the first cell of column A.
2. If you want to modify the result by adding or subtracting a constant, append +constant or -constant to the formula. For example, if you want to create a column of numbers that refers to the values in column C, but subtracts 5 from each value, enter the formula =OFFSET(C1,0,0)-5 in the first cell of column A.
3. Use the fill handle tool to copy the formula to the cells below. You should see a sequence of numbers that increments by 1 at each row, and returns the values in the offset cell or range.

Here is an example of using this method to create a column of numbers that refers to the values in column C, where column C contains a series of numbers that increases by 3 at each row:

The formula in cell A2 is =OFFSET(C1,1,0), and it is copied down to the cells below. The formula returns the value of the cell that is one row below and zero columns to the right of cell C1, which is cell C2.

Method 6: Using ROWS Function to Increment Row Number in Excel

A sixth and final way to increment row numbers in Excel formula is to use the ROWS function. The ROWS function returns the number of rows in a range of cells. For example, ROWS(A1:A10) returns 10, which is the number of rows in the range A1 to A10.

You can use the ROWS function to create a sequence of numbers that counts the number of rows in a range, and increments by 1 at each row. This method is similar to the SUBTOTAL method, but it does not ignore blank or hidden rows.

To use this method, follow these steps:

1. In the first cell where you want to start the sequence, enter the formula =ROWS(range), where range is the range of cells that you want to count. For example, if you want to create a column of serial numbers for a list of items in column B, enter the formula =ROWS($A$1:A1) in the first cell of column A.
2. If you want to modify the result by adding or subtracting a constant, append +constant or -constant to the formula. For example, if you want to create a column of serial numbers that starts from 100 and increases by 1 at each row, enter the formula =ROWS($A$1:A1)+99 in the first cell of column A.
3. Use the fill handle tool to copy the formula to the cells below. You should see a sequence of numbers that increments by 1 at each row, and counts the number of rows in the range.

Here is an example of using this method to create a column of serial numbers for a list of items, where some rows are blank or hidden:

The formula in cell A2 is =ROWS($A$1:A2)+99, and it is copied down to the cells below. The formula counts the number of rows in column A from row 1 to the current row, and adds 99 to the result.