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

**Table of Contents**hide

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

### 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)=0`

You’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:

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

## Further Readings

- How to Use ROWS Function in Excel (With 7 Easy Examples)
- How to use COLUMNS Function in Excel (3 Examples)
- How to Use COLUMN Function in Excel (4 Easy Examples)
- How to Use OFFSET Function in Excel (3 Examples)
- 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