How to Use ROW Function in Excel (With 8 Examples)

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

ROW Function in Excel (Quick View)

Download Excel Workbook

Excel ROW Function: Syntax & Arguments

Firstly, we’ll see the syntax and argument of the function. If you insert the function after entering equal sign (=), you’ll see the following figure.

Summary

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.

Syntax

Return Values

The row number

Arguments

How to Use the ROW Function in Excel (Examples)

Example 1: Basic Examples Using ROW Function

Firstly, we’ll see some basic examples of getting the row number using the ROW function. Such as you can find the row number when you have no reference cell (empty cell) like the B6 in the following figure.

Besides, you can find the row number if a single cell (e.g. B8) is used as a reference.

Moreover, an absolute cell reference can be used in the reference argument while using the ROW function. In such a situation, the row number won’t be changed. The formula will be:

Furthermore, a range of cells e.g. (B13: C13) can be used as reference.

Example 2: Creating Dynamic Arrays Using ROW in Microsoft 365

If you are a Microsoft 365 subscriber and use the ROW function for the range of cells in a row (like B5:B14 in the following figure), you’ll get 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:

Example 3: Highlighting Alternate Rows Using ROW Function

If you guys need to highlight alternate rows, you may utilize the ROW and MOD function using the Conditional Formatting toolbar from the Styles command bar.

Now select the data and open a New Formatting Rule dialog box by clicking Home tab>Conditional Formatting>New Rules.

Then choose the option Use a formula to determine which cells to format, and insert the following formula for the odd number. Lastly, open the format option to specify the highlighting color.

You’ll get the following output.

Example 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. In the earlier way, you have to use the New Formatting Rule dialog box except for the following formula.

After doing that the output will be:

Example 5: 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. The formula is:

Example 6: ROW with INDEX MATCH Formula

Let’s imagine you have a dataset of products with the manufacturer, price, etc. If you want to find the row number for a specific product like the row number for Keyboard.

You may use the INDEX MATCH Formula.

Here, INDEX MATCH returns the cell reference, then the ROW uses the reference to find the row number.

Example 7: ROW with HLOOKUP Function

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.

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

Rather you can utilize the VBA code in Excel which performs the result rapidly and accurately.

Now, let’s see how you can apply the VBA code to calculate the number of minutes.

Firstly, open a module by clicking Developer>Visual Basic>Insert>Module.

Then, copy the following code in your module.

Sub Excel_ROW_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("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

Common Errors While Using the ROW Function

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.

Further Readings

• How to Use ROWS Function in Excel (With 7 Easy Examples)
• How to use COLUMNS Function in Excel (3 Examples)
• How to Use COLUMN Function in Excel (4 Easy Examples)
• How to Use OFFSET Function in Excel (3 Examples)
• How to find text in an Excel range & return cell reference (3 ways)
• Excel Reference Cell in Another Sheet Dynamically
• Offset(…) Function in Excel with Examples