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:
=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.
| A | B | |
|---|---|---|
| 1 | Data | Formula |
| 2 | Apple | =ROW(A2) |
| 3 | Banana | =ROW(A3) |
| 4 | Cherry | =ROW(A4) |
Result
| A | B | |
|---|---|---|
| 1 | Data | Row |
| 2 | Apple | 2 |
| 3 | Banana | 3 |
| 4 | Cherry | 4 |
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.
| A | B | |
|---|---|---|
| 1 | Data | Formula |
| 2 | Apple | =ROW(A2:A4) |
| 3 | Banana | =ROW(A2:A4) |
| 4 | Cherry | =ROW(A2:A4) |
Result
| A | B | |
|---|---|---|
| 1 | Data | Row |
| 2 | Apple | 2 |
| 3 | Banana | 3 |
| 4 | Cherry | 4 |
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.
| A | B | |
|---|---|---|
| 1 | Data | Formula |
| 2 | Apple | =ROW() |
| 3 | Banana | =ROW() |
| 4 | Cherry | =ROW() |
Result
| A | B | |
|---|---|---|
| 1 | Data | Row |
| 2 | Apple | 2 |
| 3 | Banana | 3 |
| 4 | Cherry | 4 |
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.
| A | B | |
|---|---|---|
| 1 | Data | Formula |
| 2 | Apple | =IF(A2=”Cherry”, ROW(A2), “Not Cherry”) |
| 3 | Banana | =IF(A3=”Cherry”, ROW(A3), “Not Cherry”) |
| 4 | Cherry | =IF(A4=”Cherry”, ROW(A4), “Not Cherry”) |
Result
| A | B | |
|---|---|---|
| 1 | Data | Result |
| 2 | Apple | Not Cherry |
| 3 | Banana | Not Cherry |
| 4 | Cherry | 4 |
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.
| A | B | |
|---|---|---|
| 1 | Data | Formula |
| 2 | 10 | =SUM($A$2:A2) |
| 3 | 20 | =SUM($A$2:A3) |
| 4 | 30 | =SUM($A$2:A4) |
Result
| A | B | |
|---|---|---|
| 1 | Data | Sum |
| 2 | 10 | 10 |
| 3 | 20 | 30 |
| 4 | 30 | 60 |
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.
| A | B | |
|---|---|---|
| 1 | Data | Formula |
| 2 | Apple | =VLOOKUP(“Apple”, $A$2:$B$4, ROW(A2), FALSE) |
| 3 | Banana | =VLOOKUP(“Banana”, $A$2:$B$4, ROW(A3), FALSE) |
| 4 | Cherry | =VLOOKUP(“Cherry”, $A$2:$B$4, ROW(A4), FALSE) |
Result
| A | B | |
|---|---|---|
| 1 | Data | Result |
| 2 | Apple | Apple |
| 3 | Banana | Banana |
| 4 | Cherry | Cherry |
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.
| A | B | |
|---|---|---|
| 1 | Data | Formula |
| 2 | Apple | =COUNTIF($A$2:A2, “Apple”) |
| 3 | Banana | =COUNTIF($A$2:A3, “Apple”) |
| 4 | Apple | =COUNTIF($A$2:A4, “Apple”) |
Result
| A | B | |
|---|---|---|
| 1 | Data | Count |
| 2 | Apple | 1 |
| 3 | Banana | 1 |
| 4 | Apple | 2 |
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.
| A | B | |
|---|---|---|
| 1 | Data | Formula |
| 2 | 10 | =SUM(INDIRECT(“A2:A”&ROW(A2))) |
| 3 | 20 | =SUM(INDIRECT(“A2:A”&ROW(A3))) |
| 4 | 30 | =SUM(INDIRECT(“A2:A”&ROW(A4))) |
Result
| A | B | |
|---|---|---|
| 1 | Data | Sum |
| 2 | 10 | 10 |
| 3 | 20 | 30 |
| 4 | 30 | 60 |
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.
| A | B | |
|---|---|---|
| 1 | Data | Formula |
| 2 | Apple | =INDEX($A$2:$A$4, ROW(A2)) |
| 3 | Banana | =INDEX($A$2:$A$4, ROW(A3)) |
| 4 | Cherry | =INDEX($A$2:$A$4, ROW(A4)) |
Result
| A | B | |
|---|---|---|
| 1 | Data | Result |
| 2 | Apple | Apple |
| 3 | Banana | Banana |
| 4 | Cherry | Cherry |
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.
| A | B | |
|---|---|---|
| 1 | Data | Formula |
| 2 | Apple | =MATCH(“Banana”, A:A, 0) |
| 3 | Banana | =MATCH(“Banana”, A:A, 0) |
| 4 | Cherry | =MATCH(“Banana”, A:A, 0) |
Result
| A | B | |
|---|---|---|
| 1 | Data | Matched Row |
| 2 | Apple | 3 |
| 3 | Banana | 3 |
| 4 | Cherry | 3 |
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.