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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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 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.
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.
