The Excel ROW Function Explained:
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!

Get your FREE exercise file

Learning to use ROW and ROWs is easier when you have a spreadsheet that needs them.

So we made one for you!

Download it below for free and follow along with the rest of the post.

Download the FREE Exercise File

Download exercise file

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.

highlighted-row

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

Kasper Langmann, Co-founder of Spreadsheeto

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)

row-cell

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!

Kasper Langmann, Co-founder of Spreadsheeto

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.

Kasper Langmann, Co-founder of Spreadsheeto

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

row-index-match

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

Kasper Langmann, Co-founder of Spreadsheeto

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:

row-index-match-result

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.

Kasper Langmann, Co-founder of Spreadsheeto

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

random-rows

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:

random-app-name

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

Kasper Langmann, Co-founder of Spreadsheeto

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!