How to Use ROW Function in Excel?

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

• Argument Description

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

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

• 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

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

• 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:

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

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

• 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

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

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

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?

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