ROW Function In Excel

What Is ROW Function In Excel?

The ROW Function in Excel is used to find the row number of a cell reference or a cell range, and not its value. In a large dataset the function quickly finds the row index. The ROW Function in Excel is an inbuilt function, so we can enter it as a formula or insert it from the Function Library, Lookup & Reference group.

For example, the following table contains a requirements list for what row number should get displayed in each cell in the cell range B2:B4.

ROW Function In Excel

We will perform the required tasks in cell range B2:B4, using ROW function.

ROW Function In Excel - row number

The output is shown above.

  • In the first case, display the row number of cell B2, the function does not need any argument.
  • Next, display the row number of cell B50. [B50 is the argument].
  • In the 3rd scenario, when the argument is a cell range, the ROW function in Excel is the row number of the topmost row in the range (Refer to row 4 in the above table).
Key Takeaways
  • The ROW function in Excel gives the row number of a specific cell reference. Thus, the function is useful when we have to find a row number in a massive dataset or perform calculations requiring a row number.
  • The ROW function accepts one argument, i.e., reference, which is also optional.
  • One of the arguments, i.e., reference, in the ROW function can be an empty value, cell reference, or reference to a cell range.
  • In some advanced scenarios, we can use the ROW function along with other Excel functions such as MOD, INT, INDEX, MATCH, and HLOOKUP.

ROW() Excel Formula

The syntax of the ROW formula in Excel is:

ROW Function in Excel Syntax

The argument of the ROW function is,

  • reference: The cell or cell range to find the row number. It is an optional argument.

How to Use ROW Excel Function?

We can enter the Row Excel formula in 2 ways, namely:

  1. Access from the Excel Ribbon.
  2. Use in the worksheet manually.

Method #1 – Access from the Excel ribbon

Choose an empty cell for the output πŸ‘ͺ select the β€œFormulas” tab πŸ‘ͺ go to the β€œFunction Library” πŸ‘ͺ click the β€œLookup & Reference” drop-down πŸ‘ͺ select the β€œRow” function, as shown below.

How to use ROW function in Excel - Method1

The Row Excel formula is inserted in the selected cell, and the β€œFunction Arguments” window appears instantly. Enter the excel cell reference or cell range, and click β€œOK”.

How to use - Method1-1

[Note: If we enter the ROW() formula without providing the reference argument, we will get the cells row number where the formula is entered, as output.]

Method #2 – Use in the worksheet manually

  1. Select a target cell or cell range for output.
  2. Type the formula =ROW(. [Alternatively, type =R or =RO and double-click the ROW() function from the list of Excel suggestions.]
  3. Enter the row argument, i.e., the cell reference or the cell range.
  4. Press the β€œEnter” key.
How to use ROW function in Excel - Method2

Let us take a basic example to perform the required tasks using ROW in Excel.

In the following table, the data is,

  • Columns A, B, and C contain a list of fruits and their order details.
  • Column E contains the tasks to be performed.
ROW Function use

The steps to perform the tasks using ROW function in Excel are,

  1. Select cell F2, enter the formula =ROW() and press the β€œEnter” key. The result is shown below.


    ROW Function use - Step1

  2. Next, select cell F3, enter the formula =ROW(A4), and press the β€œEnter” key. The result is shown below.


    ROW Function use - Step2

    [Alternatively, select cell F3 πŸ‘ͺ select the β€œFormulas” tab πŸ‘ͺ go to the β€œFunction Library” πŸ‘ͺ click the β€œLookup & Reference” drop-down πŸ‘ͺ select the β€œRow” function, to open the β€œFunction Arguments” window, as shown below.



    The Row Excel formula is inserted in the selected cell, and the β€œFunction Arguments” window appears instantly. Now, enter A4 in the β€œReference” field.



    Click β€œOK”. We get row number 4 as the output in cell F3.

  3. Now, select cell F4, enter the formula =ROW($A$4), and press the β€œEnter” key. The result is shown below.


    ROW Function use - Step2

  4. Finally, select cell F4, enter the formula =ROW(A5:C6), and press the β€œEnter” key. The result is shown below.


    ROW Function use - Step4

In the above example we see various scenarios of the reference argument in the ROW function.

Examples

Let us understand how to insert ROW function in Excel with some advanced scenarios.

Example #1

We will insert ROW function to highlight a set of rows along with the MOD() and INT() functions for the scores of five students in different subjects.

In the following table, the data is,

  • Columns A, B, and C contain the Student Names, Subjects, and Scores, respectively.
ROW Function in Excel - Example 1

The steps to use the ROW function in Excel with MOD and INT functions to show the alternating subjects’ rows in a different color are as follows:

1: Select cell range A2:C21 πŸ‘ͺ go to the β€œHome” tab πŸ‘ͺ go to the β€œStyles” group πŸ‘ͺ click the β€œConditional Formatting” drop-down πŸ‘ͺ select the β€œNew Rule” option, as shown below.

Example1 - Step1

2: The β€œNew Formatting Rule” window pops up.

  • Go to the β€œSelect a Rule Type:” group πŸ‘ͺ choose the β€œUse a formula to determine which cells to format” option.
  • Go to the β€œEdit the Rule Description:” πŸ‘ͺ type the formula =MOD(INT((ROW()-2)/5)+1,2) in the β€œFormat values where this formula is true:” field.
  • Click the β€œFormat” button.
Example1 - Step2

3: The β€œFormat Cells” window pops up. Go to the β€œFill” tab πŸ‘ͺ go to the β€œBackground Color” group πŸ‘ͺ choose the desired color, here, Green πŸ‘ͺ click β€œOK”. The β€œFormat Cells” window closes.

Example1 - Step3

4: Click β€œOK” to close the β€œNew Formatting Rule” window.

Example1 - Step4

The output is shown below with the alternating subjects’ rows shaded in Green.

Example1 - Step4a

Example #2

Let us insert ROW function with the HLOOKUP() function to display the population of a specific state.

The following table contains US states, capitals, and population details.

ROW Function in Excel - Example 2

The procedure to display the population of Connecticut, using the ROW function and HLOOKUP() are follows:

Select the target cell B7, enter the formula =HLOOKUP(B6,B1:K3,ROW(G3),0) and press the β€œEnter” key. The output is shown below.

Example2-1

In the above example, the ROW function formula returns row number 3 as the output. So, the HLOOKUP() becomes: =HLOOKUP(B6,B1:K3,3,0)

Example 2-2

The function then performs a horizontal lookup to return Connecticut’s population as β€˜3605597’, as shown above.

Example #3

We will use the ROW function in Excel with INDEX() function and MATCH() functions to find the row number with the details of a specific employee.

In the following table, the data is,

  • Columns A, B, and C contain the Employee Names, Designation, and Performance Rating, respectively.
ROW Function in Excel - Example 3

The procedure to determine the row number for the employee, Greg Hogan, are as follows:

Select cell F2, enter the formula=ROW(INDEX(A2:C21,MATCH(E2,A2:A21,0),1)), and press the β€œEnter” key. The output is shown below.

Example 3-1
Example 3-2

In the above example, MATCH() returns the value 8, as Greg Hogan is the eighth name on the employee list. Then the INDEX() returns the absolute cell reference $A$9, based on the array A2:C21, row 8, and column 1. And finally, the ROW() displays the row number 9, based on the cell reference, $A$9.

Thus, we can refer to row number 9 to get Greg Hogan’s record.

Important Things to Note

  • The argument in the ROW function in Excel cannot be multiple references.
  • A ROW() function, without the argument value or any cell reference, will return the row number of the cell where we entered the ROW function.
  • If the supplied argument is not a proper cell reference, such as a reference to a cell or cell range, we get the #NAME? error.
  • If the argument of the ROW() function is a cell range, the function will return the topmost row in the particular cell range.

Frequently Asked Questions (FAQs)

1. Where is the ROW function in Excel?

The ROW function in Excel is in the Formulas tab. Click Formulas πŸ‘ͺ Lookup & Reference πŸ‘ͺ ROW to access it.

ROW Function in Excel FAQ1

2. Why is the ROW function not working in Excel?

The ROW function in Excel might not work due to the following reasons:
● The specified cell or cell range is invalid.
● Multiple references are provided as the argument to the ROW function.
● We provided an argument that is not a proper cell reference.

3. How to execute the ROW function in Excel VBA?

We can execute the ROW function in Excel VBA, using the below steps as shown in the following example.
The following table shows a list of products and their prices.

ROW Function in Excel FAQ3

The steps to find the row number of each product using the ROW function and the Excel VBA are:
1: In the worksheet with the above table, press the shortcut keys Alt + F11 to open the β€œVBA Editor”.

FAQ3 - Step1

2: Click on the required VBAProject in the menu on the left (as depicted in the image above) and select Insert πŸ‘ͺ Module to open a new module window.

FAQ3 - Step2

3: Once the Module 1 window opens, type the VBA code, shown in the below image, to display the required row numbers in the target cells C2:C6.

Sub Excel_ROW_Fn()
Dim rn As Worksheet
Set rn = Worksheets(β€œROW_FAQ”)
rn.Range(β€œC2”) = rn.Range(β€œA2”).Row
rn.Range(β€œC3”) = rn.Range(β€œA3”).Row
rn.Range(β€œC4”) = rn.Range(β€œA4”).Row
rn.Range(β€œC5”) = rn.Range(β€œA5”).Row
rn.Range(β€œC6”) = rn.Range(β€œA6”).Row
End Sub

FAQ3 - Step3

4: Next, click the Run Sub/UserForm option in the menu to execute the commands.

FAQ3 - Step4

5: Finally, open the worksheet to view the output with the row numbers displayed in column C.

FAQ3 - Step5

Likewise, suppose we have to show the row number of a cell range, say A2:C6, in the target cell C6. Then the VBA code will be as shown in the image below:

Sub Excel_ROW_Fn()
Dim rn As Worksheet
Set rn = Worksheets(β€œROW_FAQ”)
rn.Range(β€œC2”) = rn.Range(β€œA2”).Row
rn.Range(β€œC3”) = rn.Range(β€œA3”).Row
rn.Range(β€œC4”) = rn.Range(β€œA4”).Row
rn.Range(β€œC5”) = rn.Range(β€œA5”).Row
rn.Range(β€œC6”) = rn.Range(β€œA2:C6”).Row
End Sub

FAQ3-1

And the new output will be:

FAQ3-2

Cell C6 shows row number 2, the topmost row in the cell range, A2:C6.

4. What is the difference between the ROW and ROWSfunctionsn in Excel?

The difference between the ROW and ROWS functions in Excel is that the ROW() function gives the row number of the specified cell or cell range reference. On the other hand, the ROWS() function determines the number of rows in the specified reference as a number.

Download Template

This article must help understand the ROW Function in Excel, with its formula and examples. We can download the template here to use it instantly.

This has been a guide to ROW Function in Excel. Here we use formula with MOD, INT, HLOOKUP, INDEX, MATCH, formatting, examples & downloadable excel template.Β You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published.