# ROW Function in Excel

### Part 1: Introduce

💡 Definition

The ROW function is a built-in Excel function categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel.

💡 Purpose

The ROW function’s main Purpose is to return a reference’s row number.

💡 Syntax & Arguments

The syntax of the ROW function is:

syntax
```=ROW(reference) ```

Here, the argument is:

• reference – (optional) The cell or range of cells you want the row number. If this argument is omitted, the ROW function will assume the authority to be the cell in which the ROW function appears.

💡 Return Value

The ROW function returns a number representing the given reference’s row number.

💡 Remarks

If the reference argument is a range of cells, the ROW function will return the row numbers of all cells within the field. If the reference argument is omitted, the ROW function returns the row number of the cell which the function resides in.

### Part 2: Examples

Let’s look at some examples of how to use the ROW function.

Example 1

💼 Purpose: To return the row number of a single cell.

AB
1DataFormula
2Apple=ROW(A2)
3Banana=ROW(A3)
4Cherry=ROW(A4)

Result

AB
1DataRow
2Apple2
3Banana3
4Cherry4

The formula in column B uses the ROW function to return the row number of each cell in column A.

Example 2

💼 Purpose: To return the row numbers of a range of cells.

AB
1DataFormula
2Apple=ROW(A2:A4)
3Banana=ROW(A2:A4)
4Cherry=ROW(A2:A4)

Result

AB
1DataRow
2Apple2
3Banana3
4Cherry4

The formula in column B uses the ROW function to return the row numbers of the cells in the range A2:A4.

Example 3

💼 Purpose: To return the row number of the cell in which the ROW function appears when the reference argument is omitted.

AB
1DataFormula
2Apple=ROW()
3Banana=ROW()
4Cherry=ROW()

Result

AB
1DataRow
2Apple2
3Banana3
4Cherry4

The formula in column B uses the ROW function without a reference argument. Therefore, it returns the row number of the cell in which the function resides.

Example 4: ROW with IF function

💼 Purpose: Identify rows that have “Cherry” as data.

AB
1DataFormula
2Apple=IF(A2=”Cherry”, ROW(A2), “Not Cherry”)
3Banana=IF(A3=”Cherry”, ROW(A3), “Not Cherry”)
4Cherry=IF(A4=”Cherry”, ROW(A4), “Not Cherry”)

Result

AB
1DataResult
2AppleNot Cherry
3BananaNot Cherry
4Cherry4

The formula checks if the cell in column A contains “Cherry”; if yes, it returns the row number. Otherwise, it replaces “Not Cherry”.

Example 5: ROW with the SUM function

💼 Purpose: Use the ROW function to create a series of numbers for the SUM function dynamically.

AB
1DataFormula
210=SUM(\$A\$2:A2)
320=SUM(\$A\$2:A3)
430=SUM(\$A\$2:A4)

Result

AB
1DataSum
21010
32030
43060

The formula uses the ROW function to dynamically increase the range of the SUM function dynamically, therefore providing a running total.

Example 6: ROW with VLOOKUP function

💼 Purpose: Use the ROW function to identify the row number for VLOOKUP to return data.

AB
1DataFormula
2Apple=VLOOKUP(“Apple”, \$A\$2:\$B\$4, ROW(A2), FALSE)
3Banana=VLOOKUP(“Banana”, \$A\$2:\$B\$4, ROW(A3), FALSE)
4Cherry=VLOOKUP(“Cherry”, \$A\$2:\$B\$4, ROW(A4), FALSE)

Result

AB
1DataResult
2AppleApple
3BananaBanana
4CherryCherry

The formula uses the ROW function to return the row and column index numbers in the VLOOKUP function.

Example 7: ROW with COUNTIF function

💼 Purpose: Use the ROW function to dynamically count the occurrences of “Apple” in the data.

AB
1DataFormula
2Apple=COUNTIF(\$A\$2:A2, “Apple”)
3Banana=COUNTIF(\$A\$2:A3, “Apple”)
4Apple=COUNTIF(\$A\$2:A4, “Apple”)

Result

AB
1DataCount
2Apple1
3Banana1
4Apple2

The formula uses the ROW function to dynamically increase the range of the COUNTIF function dynamically, therefore providing a running total of “Apple” occurrences.

Example 8: ROW with INDIRECT function

💼 Purpose: Use the ROW function to create a dynamic range reference for the SUM function.

AB
1DataFormula
210=SUM(INDIRECT(“A2:A”&ROW(A2)))
320=SUM(INDIRECT(“A2:A”&ROW(A3)))
430=SUM(INDIRECT(“A2:A”&ROW(A4)))

Result

AB
1DataSum
21010
32030
43060

The formula uses the ROW function to create a string that refers to a range in the worksheet. The INDIRECT function is then used to convert this string to a range reference that can be used in the SUM function.

Example 9: ROW with INDEX function

💼 Purpose: Use the ROW function to return the value of a cell at a specified row.

AB
1DataFormula
2Apple=INDEX(\$A\$2:\$A\$4, ROW(A2))
3Banana=INDEX(\$A\$2:\$A\$4, ROW(A3))
4Cherry=INDEX(\$A\$2:\$A\$4, ROW(A4))

Result

AB
1DataResult
2AppleApple
3BananaBanana
4CherryCherry

The formula uses the ROW function to return the row number, used as the row number argument in the INDEX function.

Example 10: ROW with the MATCH function

💼 Purpose: Use the ROW function to return the row number of a specific value in the data.

AB
1DataFormula
2Apple=MATCH(“Banana”, A:A, 0)
3Banana=MATCH(“Banana”, A:A, 0)
4Cherry=MATCH(“Banana”, A:A, 0)

Result

AB
1DataMatched Row
2Apple3
3Banana3
4Cherry3

The formula uses the ROW and MATCH functions to return the row number where “Banana” is found in column A.

### Part 3: Tips and Tricks

💡 Always remember that the ROW function will return the row number of a cell or a range of cells. It can be convenient when combined with other functions for dynamic references.

💡 Be aware that if you insert or delete rows, the return value of the ROW function will update to reflect the new position. This is important when you want the function to refer to a fixed position regardless of changes in the worksheet. In this case, you might use the absolute reference (\$A\$2 instead of A2, for example).

💡 The ROW function in Excel can also be used in array formulas, which enables it to return an array of results rather than a single result. This can be pretty powerful in more advanced Excel applications.