Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

Basic Examples Using ROW Function

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

Basic Examples Using ROW Function

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

Basic Examples Using ROW Function

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

Creating Dynamic Arrays Utilizing ROW Function

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.

Highlighting Alternate Rows Applying ROW Function

  • 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

Highlighting Alternate Rows Applying ROW Function

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

Using ROW Function to Show Groups of Rows


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.

Combining ROW with INDIRECT Function


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)

Use of ROW with INDEX MATCH Formula

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.

Use of ROW with INDEX MATCH Formula


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)

Utilizing a Combination of ROW with HLOOKUP Functions


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.

Getting Row Number Using VBA in Excel

  • 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

Getting Row Number Using VBA in Excel

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

Getting Row Number Using VBA in Excel

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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo