## ROW function explained

**ROW(**reference**)**

Returns the row number of a reference.

You can enter a reference to a single cell or a cell range. The ROW function returns an array of numbers if you enter a reference to a cell range. Remember to enter the formula as an array formula.

The reference argument is optional. Enter =ROW() in a cell and a reference to the cell itself is used. Example, =ROW() in cell F4 returns 4.

The first example demonstrates basic usage. Example 2 and 3 are more advanced and requires some knowledge about MATCH and INDEX function.

### Example 1

A cell reference can be relative or absolute or a combination of relative and absolute.

Absolute and relative references in excel

In cell B2: =ROW(B2) returns 2.

In cell B6: =ROW() returns 6

In cell B11: =ROW($D$10) returns 10 (absolute cell reference).

### Example 2 - Duplicate columns in an array

In cell range B3:B5:

returns {3; 4; 5}

Now you might wonder why the array formula doesn´t return {3, 3; 4, 4; 5, 5}. There are six cells in the cell range, how come only three values are returned?

The answer is that there is no need for multiple **duplicate** columns in the array. Excel simplifies the array down to a single column. But when used with multiple cell ranges in more complicated array formulas, make sure the number of rows match. See this example: Unique distinct values from a cell range

### Example 3 - Row numbering a cell range

The INDEX function returns a value of the cell at the intersection of a particular row and column, in a given range. To be able to work with multiple values from an arbitrary cell range using the INDEX function we must **number each row**. That is exactly what we did in the previous example but the first row in a given range has to be 1 and the second 2 and so on. Why? The INDEX function needs properly numbered cell ranges.

This is where the ROW and MATCH function comes in.

The beauty with this formula is that it row numbers any cell range with any size.

**In this example, the cell range is C3:D5:**

becomes =MATCH({3; 4; 5}, {3; 4; 5}) and returns {1; 2; 3}.

Remember, enter the formula as an array formula.

**So what can you do with row numbered cell ranges?**

**The MATCH function can also be used to find the relative position of a value in a cell range. See these posts:**

- How to extract unique distinct values from a column

- Repeat values
- Fetch data from another table
- Shift Schedule

### Download example file:

### Category: Functions

Comments(14) Filed in category: Excel, Functions, Index

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.Comments(12) Filed in category: Excel, Functions

Comments(12) Filed in category: Excel, Functions

Excel RANDBETWEEN and RAND functions – How to generate random numbers and text

Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function. RAND() function returns a random […]Comments(10) Filed in category: Excel, Functions

Comments(9) Filed in category: Excel, Functions

### Category: Row

### One Response to “ROW function explained”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[…] ROW(reference) Returns the rownumber of a reference […]