## What Is ROW Function In Excel?

The

ROW Function in Excelis 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. TheROW Function in Excelis an inbuilt function, so we can enter it as a formula or insert it from theFunction Library,Lookup & Referencegroup.

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

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

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 3
^{rd}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).

##### Table of contents

###### 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:

The argument of the **ROW function **is,

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

### How to Use ROW Excel Function?

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

**Access from the Excel Ribbon.****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.

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**β.

[**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

- Select a target cell or cell range for output.
- Type the formula
**=ROW(.**[Alternatively, type**=R**or**=RO**and double-click the ROW() function from the list of Excel suggestions.] - Enter the row argument, i.e., the cell reference or the cell range.
- Press the β
**Enter**β key.

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.

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

**Select cell F2, enter the formula =ROW() and press the βEnterβ key. The result is shown below.****Next, select cell F3, enter the formula =ROW(A4), and press the βEnterβ key. The result is shown below.**

[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**E**xcel 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**.**Now, select cell F4, enter the formula =ROW($A$4), and press the βEnterβ key. The result is shown below.****Finally, select cell F4, enter the formula =ROW(A5:C6), and press the βEnterβ key. The result is shown below.**

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.

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.

**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 formulain the β*=MOD(INT((ROW()-2)/5)+1,2)***Format values where this formula is true:**β field. - Click the β
**Format**β button.

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

**4: **Click β**OK**β to close the β**New Formatting Rule**β window.

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

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

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.

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

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.

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.

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.

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

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**β.**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.**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****4: **Next, click the **Run Sub/UserForm** option in the menu to execute the commands.**5: **Finally, open the worksheet to view the output with the row numbers displayed in column C.

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**

And the new output will be:

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.

### Recommended Articles

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 –

## Leave a Reply