**The Excel ROW Function Explained:**

How To Find a Row Number Easily

How To Find a Row Number Easily

*Written by co-founder Kasper Langmann, Microsoft Office Specialist.*

Most of the time, if you need a row number or count, you can get it with a click or two.

But when you want to use that information in a formula, you need a function.

And that’s where the functions ROW and ROWS get into the picture 🙂

**ROW**gives you the row number of a specific cell.**ROWS**gives you the number of rows in a range.

Let’s take a look at how these functions work and what you can do with them!

**Getting a row number with ROW**

Getting a row number is easy—just find the cell you’re interested in, click on it, and look at the highlighted row number on the side of the window.

Sometimes, however, you need to use that information in a function. That’s where ROW comes in.

The syntax of the ROW function is simple:

**The syntax of the ROW function**

**=ROW([cell])**

This function can be run with no arguments. If you do this, the function returns the row number of the cell that houses the function.

If you include the optional **cell** argument, it returns the row number of the specified cell.

Let’s take a look at how that works. Open up the example workbook, and click into cell E3.

We’ll start with a very simple formula. Type this into E3:

**=ROW()**

When you hit enter, you’ll see that you get a value of 3, because a ROW function with no parameter returns the row of its own cell.

We’ll try something more interesting with this formula:

**=ROW(A13)**

Hit **Enter**, and you’ll get 13 back—which you probably expected, as the row number is right in the cell reference.

Don’t worry, it gets more interesting than that.

Keep reading to see how this information could be useful!

**Finding the number of rows in a range**

Unlike the ROW function, ROWS gives you information about a range of cells. In this case, it’s the number of rows in a selection. Here’s the syntax:

**The syntax of the ROWS function**

**=ROWS(range)**

Note that this function doesn’t have any optional arguments. You need to provide the **range** argument, which tells Excel where to count rows.

Here’s a simple example.

Click into any cell and type this formula:

**=ROWS(H6:H12)**

Hit **Enter**, and you’ll get a value of 7 back. That’s the number of rows in our selection, H6:H12.

Note that **these cells are empty**. ROWS counts the rows in your selection, not just those that have values in them.

If you use the ROWS function on a selection of text from the first column, you’ll find that it does the same thing.

**Practical examples of ROW and ROWS**

Let’s take a look at how you might put these functions to work.

First, we’ll use ROW to get the row of a particular cell in a list.

Click into a new cell, and type the following formula:

**=ROW(INDEX(Apps, MATCH(“Asoka”, Apps, 0)))**

That’s a lot of information, so we’ll break it down.

(Also, before we get started, note that the named range “Apps” contains A2:A51.)

In the center, we have **MATCH(“Asoka”, Apps, 0)**.

This returns the relative position of the cell containing the word “Asoka” in the range “Apps” (which is A2:A51).

Outside of this, we have the INDEX function. This takes the number returned by MATCH and **returns a cell reference**. That’s important. If you run INDEX without ROW, you’ll see the value of the cell returned. But it actually returns the reference, which we can use with ROW.

Finally, ROW takes the cell reference returned as gives us the row number.

Hit **Enter** to see the result, then we’ll talk about why this is a useful formula:

As you can see, the formula returned 7—which is the row containing the app named “Asoka.”

To see why this is useful, try running **MATCH(“Asoka”, Apps, 0)**.

**It returns a 6**, which is **not **the row number.

Because MATCH returns relative positions, it looks only at the named range “Apps.” “Asoka” is in the sixth cell down in the range.

That’s not much of a difference in this case, but if your range is a few dozen cells down from the top of your spreadsheet, you can get more absolute information with the ROW function.

Now let’s take a look at the ROWS function.

Excel can generate random numbers, but it isn’t quite as easy to get a random cell from a range. We can use ROWS to get a random cell with ease, though.

Here’s the formula we’ll use:

**=INDEX(Apps, RANDBETWEEN(1, ROWS(Apps)))**

INDEX will give us a cell from the named range “Apps”—but we want it to return a random one. So for the row number, we’ll use a RANDBETWEEN function:

**RANDBETWEEN(1, ROWS(Apps))**

That formula returns a random number between one and the number of rows in the Apps range. That random number is then given to INDEX:

If you don’t have a named range, you can easily replace “Apps” with a range of cells.

Combining ROW and ROWS with other functions is where you’ll really see their power.

One of the common places you’ll see these functions used is in returning multiple matches for a single search. It’s a rather complex operation, but very useful. We recommend checking out this video from Doug H to see how it works:

**The power of ROW and ROWS**

ROW and ROWS don’t seem very powerful at first glance. But when you combine them with other functions, they can do some very cool things.

Our examples here show a couple ways to take advantage of these functions, but we’re sure you can think of many more!