Md. Abdul Kader
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.
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
=ROW (reference)Return Values
The row number
Arguments
| Argument | Required or Optional | Value |
| reference | Optional | A reference to a cell or range of cells |
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:
=ROW($B$6)Furthermore, a range of cells e.g. (B13: C13) can be used as reference.
Read More: How to Use ROWS Function in Excel (With 7 Easy Examples)
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:
=ROW(B5:B14)
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.
=MOD(ROW(),2)=0You’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.
=MOD(INT(ROW()-1)/4)+1,2)After doing that the output will be:
Similar Readings
- 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
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:
=ROW(INDIRECT("1:"&COUNTA(D:D)))
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.
=ROW(INDEX(B4:D18,MATCH(F11,B4:B18,0),2))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.
=HLOOKUP(H8,C4:E18,ROW(E8),0)
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
| Common Errors | When they show |
| #N/A | – Occurs when the required value is not found |
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.
