When you have a tiny dataset, itâ€™s easy to find the row number, but in the case of a larger dataset, you may have to use the ROW function. In this article, Iâ€™ll describe the ROW function in Excel, starting from the basics to the VBA code, including eight practical examples with proper explanations. So that you may adjust the formula for your uses.

**Overview of Excel ROW Function**

**Description**

**The ROW function **returns the row number for a given reference. The reference may be a cell or cell range. If the reference is not specified (as the argument is optional), the ROW function automatically considers the cell containing the formula as a reference.

**Generic Syntax**

`=ROW (reference)`

**Argument Description**

ARGUMENT | REQUIREMENT | EXPLANATION |
---|---|---|

reference |
Optional | Â A reference to a cell or range of cells |

**Returns**

The row number of the reference cell(s)

## How to Use the ROW Function in Excel: 8 Examples

In this section, we will demonstrate 8 effective examples concerning the usage of the **ROW** function in Excel with appropriate illustrations.

### 1. Basic Examples Using ROW Function

In this first example, we will give multiple illustrations of the basic use of **the ROW function**. To know more, follow the steps below.

**Steps:**

- In the example below, we have used the
**ROW**function without any argument in cell**D6**.

`=ROW()`

- As we can see, it took the
**D6**as its argument by default and returned the row number of**D6**, which is obvious. - Next, we will see the application of the ROW function while giving a single cell as an argument.
- Hence, on
**D8**and**D9**respectively, we write the following two formulas.

`=ROW(B8)`

`=ROW(B9)`

- As a result, we get their row numbers (
**8**and**9**). - This time, we will see the application when we use an
**Absolute Cell**reference**($B$6)**as an argument. On**D11**, we write the following formula

`=ROW($B$6)`

- The result is the same, the row number of
**B6**, which is**6**.

- Now we will see the case when we use multiple cells/ranges of a single row as an argument. In cell
**D13**, we use the following formula.

`=ROW(B13:C13)`

- Consequently, we get the expected row number, which is
**13**.

### 2. Creating Dynamic Arrays Utilizing ROW Function

Microsoft 365 subscribers can create a dynamic array by using the ROW function. To know more, read the following steps carefully.

**Steps:**

- If you are a Microsoft 365 subscriber and use the ROW function for the range of cells in a column (like
**B5:B14**in the following figure), youâ€™ll get not a single row number. - Rather, youâ€™ll get a range of dynamic arrays. Microsoft recommends this feature for convenience in the calculation process rather than using regular arrays.
- The formula is:

`=ROW(B5:B14)`

- You will get the row number from
**5**to**14**in a column as a dynamic array like this figure below.

### 3. Highlighting Alternate Rows Applying the ROW Function

If you guys need to highlight alternate rows, you may utilize the **ROW **and **MOD **functions using the **Conditional Formatting **toolbar from the Styles command bar. To do that, check the following steps.

**Steps:**

- First, select the data.

- Then go to the
**Conditional Formatting**toolbar from the**Styles**command bar and choose**New Rule.**

- Now, click to
**Use a new formula to determine which cells to format.** - On the formula bar, write down the following formula and click
**OK**.

`=MOD(ROW(),2)=0`

- You will see that the even row numbered(6,8,10 etc.) cells have been formatted.

### 4. Using the ROW Function to Show Groups of Rows

Another example is the shade of alternating row groupings. Letâ€™s say, if you want to shade four rows, then four shading rows, then four more shaded rows, etc. You may utilize the **ROW**, **MOD**, and **INT **functions to complete the obtained numerical number. To know more, read the following steps.

**Steps:**

- Select the Cells and follow the similar steps shown in example 3 to go to
**Conditional Formatting**and write down the following equation in the formula bar.

`=MOD(INT((ROW()-1)/4)+1,2)`

- The result will be like this below.

### 5. Combining ROW with INDIRECT Function

If you want to get the row number for a dataset where blank cells are also available, you can use the **ROW **with the combination of **INDIRECT **and **COUNTA **functions. (see the figure below)

As we can see, in the **D **column, our data starts with **D4 **and finishes with **D18, **and outside this range, everything is a blank cell. Now, we will find out the row number of the existing data in column D(**D:D**). To do that, follow the steps below.

**Steps**:

- On cell
**E4**, write down the following formula

`=ROW(INDIRECT("1:"&COUNTA(D:D)))`

- Now, click
**Enter**. You will see a result like this.

### 6. Use of ROW with INDEX MATCH Formula

Letâ€™s imagine you have a dataset of products with the manufacturer, price, etc. Now, you want to find the row number for a specific product. (see the figure below)

We can do that using a combination of the **ROW, INDEX,** & **MATCH **functions. To do that, follow the steps below.

**Steps:**

- On the
**G11**, write the following formula.

`=ROW(INDEX(B4:D18,MATCH(F11,B4:B18,0),2))`

- Now press
**Enter**. Consequently, you will have a result like this.

### 7. Utilizing a Combination of ROW and HLOOKUP Functions

**The HLOOKUP function**Â looks up the data from a cell range like **VLOOKUP **but **HLOOKUP** asks for the number of the row. To acquire the desired data using **HLOOKUP**, please input the row number.

`=HLOOKUP(H8,C4:E18,ROW(E8),0)`

### 8. Getting Row Number By Using VBA in Excel

If you have a larger dataset, it is time-consuming and a little bit boring to get the required result using a formula. Instead, you can utilize the VBA code in Excel which performs the result rapidly and accurately. Suppose we have a data set like this, and we want to find out the row number using the VBA code. (See the figure below).

Now, letâ€™s see how you can apply the VBA code to calculate the number of minutes. Follow the steps below.

**Steps:**

- First, click on the
**Developer**tab and select**Visual Basic**to open the VB window. Alternatively, you can also click**Alt + F11**.

- On the VB window, click on
**Insert > Module.**

- A new module will open up. Now, paste the following VBA code into your module.

```
Sub Excel_ROW_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("8.VBA ROW")
'apply the Excel ROW function
ws.Range("D5") = ws.Range("B5").Row
ws.Range("D6") = ws.Range("B6").Row
ws.Range("D7") = ws.Range("B7").Row
ws.Range("D8") = ws.Range("B8:C8").Row
End Sub
```

- Now, if you run the code, you will see that the row numbers will appear in the
**Row Number column.**

**What Are the Common Errors While Using the ROW Function?**

Common Errors | When they show |
---|---|

#N/A |
Occurs when the required value is not found. |

**Things to Remember**

- While using conditional formatting, be careful of the selection of cells where the formatting will be applied.
- Use VBA code only when you have a large data set.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

This is how you can apply the ROW function to get the row number. Also, you have the opportunity to combine the function with other Excel functions. If you have an interesting and unique method of using the ROW function, please share it in the comments section below. Thanks for being with me.

**<< Go Back to Excel Functions ****|**** Learn Excel**