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.

**Table of Contents** hide

Download Practice Workbook

Overview of Excel ROW Function

8 Uses of ROW Function in Excel

1. Basic Examples Using ROW Function

2. Creating Dynamic Arrays Utilizing ROW Function

3. Highlighting Alternate Rows Applying ROW Function

4. Using ROW Function to Show Groups of Rows

5. Combining ROW with INDIRECT Function

6. Use of ROW with INDEX MATCH Formula

7. Utilizing a Combination of ROW and HLOOKUP Functions

8. Getting Row Number Using VBA in Excel

Common Errors While Using the ROW Function

Things to Remember

Conclusion

Related Articles

**Download Practice Workbook**

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

**ROW Function.xlsm**

**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)

**8 Uses of ROW Function in Excel**

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 on the basic use of 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 obviously. - Next, we will see the application of the ROW function while giving 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/range 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.

**Read More:** How to Use COLUMN Function in Excel (4 Ideal Examples)

### 3. Highlighting Alternate Rows Applying 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 on
**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.

**Read More:** How to Use ROWS Function in Excel (with 7 Easy Examples)

### 4. Using 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 un-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 **D **column, our data start with **D4 **and finish in **D18 **and outside this range, everything is a blank cell. Now, we will find out the row number of the existing data on 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

HLOOKUP is a function that 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 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 in your module.

`Sub Excel_ROW_Function()'declare a variableDim ws As WorksheetSet ws = Worksheets("8.VBA ROW")'apply the Excel ROW functionws.Range("D5") = ws.Range("B5").Rowws.Range("D6") = ws.Range("B6").Rowws.Range("D7") = ws.Range("B7").Rowws.Range("D8") = ws.Range("B8:C8").RowEnd Sub`

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

**Read More: **Find Text in Excel Range and Return Cell Reference (3 Ways)

**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 large data set.

**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.

## Related Articles

- How to Use OFFSET Function in Excel (3 Suitable Examples)
- Examples of OFFSET Function in Excel (Formula+VBA )
- How to Reference Cell in Another Sheet Dynamically in Excel