The Excel ROW Function Explained: How To Find a Row Number Easily

Ever had to use a row number in a formula or function in Excel? Yeah, it’s right there to the left of the sheet – no big deal.

But what if you need the same for multiple formulas? You can’t enter each of them manually, can you? Well, the good news is that you don’t even need to do it manually. Instead, Excel will do it for you✌

The ROW function of Excel returns the row number for any given cell. And this guide teaches you all that you need to know about the ROW function. So without further ado, let’s dive right in.

Also, download our free sample workbook here to tag along with the guide.

Table of Contents

Get the row number from the ROW function

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 left side of the window. Like this:

A single cell selected in the fourth column

That’s easy, right? But what if you need to add the row number in a formula? How do you get the row number for any given cell?

That’s where we use the ROW function. It is a built-in function in Excel and requires only one argument.

So how do we use it? Let’s see that right below👇

The formula of the Excel ROW function is pretty simple.

=ROW(argument)

You can even execute it with no arguments even. Upon doing so, the function returns the row number of the active cell.

For instance, let’s say we apply the ROW function in cell C7.

  1. Select cell C7 on the worksheet.
Selecting the serial number C7.
  1. In the selected cell, type the ROW function as follows:

= ROW() 

  1. Hit Enter.
First cell return row number 7.

The ROW function returns 7. This is because Cell C7 (the active cell) lies in Row 7.

  1. Similarly, if you try writing:

= ROW(M9)

Here’s what happens:

The first row returns 9th row number in Excel.

Excel returns the number 9 as the referred cell (Cell M9) lies in Row 9. It’s as easy as that😉

You can also try the same with an array. The Excel row function returns the row number for each of the cells in the defined array.

Let’s understand this with an example.

Say we apply the function in cell A2, and the ROW function takes an argument of range D3:D9.

Selecting the range serial numbers.

The result will be something like this:

The ROW formula shows third row as start of range.

The ROW function returns the row numbers of the selected range of cells in the form of an array.

This function proves super helpful when nested in complex formulas.

How to get the row number with the ROWS function

The ROWS function deals with an array of cells and gives the count of the rows in the range.

Its syntax reads as follows:

=ROWS (range)

An important point to note here is that the arguments given in the ROWS function are not optional.

You must specify a range of cells for Excel to count the rows using the ROWS function.

Kasper Langmann, Microsoft Office Specialist

So, let’s see the ROWS function in action🔮

Say we want a count of the rows in between cells A2 and A11. To do so:

  1. Write the ROWS function as follows:

=ROWS(A2:A11)

Using range in new cell with ROW function.

The function returns 10, which is equal to the count of rows between Cell A2 to A11.

Note that the ROWS function only counts the number of cells (or rows) in a dataset, not the values in the cells.

The ROWS function would work even if the data set contains empty cells🤩

Kasper Langmann, Microsoft Office Specialist

The ROW vs. ROWS Function

You must be thinking – both the ROW and the ROWS function look the same and almost sound the same too. So what is the difference between the ROW function and the ROWS function?

Well, both the ROW functions are quite different from each other. Here’s how:

If we use a single argument, say D3, with both functions, the results will be different with each of them.

  1. The ROW function will return the row number of Cell D3 as shown below:
Excel ROW function returns the row number 3

Whereas, the ROWS function will return the count of the rows between the specified cells.

As D3 is a single cell, there’s only one row to it.

Using ROWS function returns the count of the rows.

The difference between the two functions becomes pretty clear from the above example. And it doesn’t stop here.

  1. With a range of cells defined, the ROW function returns the row number for each cell of that range. In contrast to the above, the ROWS function returns the count of the rows in the defined range of cells.
  2. Moreover, you can use the ROW function without any arguments. And the function will simply return the row number of the active cell. However, if you try omitting the argument for the ROWS function, Excel will return an error prompt🚩

Examples of ROW And ROWS

Let’s see some common examples of using the ROW and ROWS functions with other functions to better understand how they work.

How about using these functions together with the INDEX and the MATCH function? And by the way, if you are a Harry Potter fan, you are simply going to love the following example 💕

Excel example reference

In this dataset, we want to know the row number of the O.W.L Eleven.

And the formula we are to apply looks like this:

= ROW (INDEX (A1:A8, MATCH (E6, B1:B8,0)))

Following formula uses MATCH function, INDEX and ROW function.

Yes, we know – that’s terribly confusing. So we’ll take you through all the parts of this formula one by one:

  • MATCH(E6,B1:B8,0) – the MATCH function returns the cell’s (E6) relative position in the range B1:B8, which, in this case, is 3.
  • INDEX(A1:A8, MATCH(E6, B1:B8,0)) – the INDEX function will return the cell value (Hermoine Granger) that comes on Number 3 (the matched value) within the given range.
  • = ROW(INDEX(A1:A8, MATCH(E6,B1:B8,0))) – the ROW function returns the row number for the cell returned by the INDEX function.

All done! Press Enter and here are the results 💯

Using the ROW function to get a row number

Excel finds the row number where the O.W.L Eleven lies and it’s Row Number 4.

Note that ROW and INDEX both work together to return the reference.

Try using the INDEX function separately. You will see Excel returns the cell value that lies on that position number instead of showing the row number.

Kasper Langmann, Microsoft Office Specialist

That’s how you can use the ROW function together with other functions. Now let’s see an example with the ROWS function.

We will use the same data set. But this time, we want a random result instead of a particular row number.

For this, we will use the INDEX function and the RANDBETWEEN function.

The formula looks something like this:

= INDEX(A1:A8, RANDBETWEEN (1, ROWS(A1:A8)))

Formula for using ROWS function
  • ROWS(A1:A8) – the ROWS function returns the number of cells between rows 1 to 8. The answer to this is 8.
  • RANDBETWEEN (1, ROWS(A1:A8)) – the RANDBETWEEN function will return a random number between 1 and 8 (the value returned by the ROWS function).
  • INDEX (A1:A8, RANDBETWEEN (1, ROWS (A1:A8)) – the INDEX function will return the value from range A1:A8 that lies on the position number returned by RANDBETWEEN.

Upon applying it, Excel returns a random result from the vertical array.

Choosing a random character.

Note that Ronald Weasley falls on number 2 in the list above. This means the RANDBETWEEN function returned the number 2.

And the INDEX function returned the value that falls in the second position in the range A2:A8.

See? That’s how easy it is to use the ROW and ROWS functions 🚀

That’s it – Now what?

In this article, we learned the use of the ROW and ROWS functions. We also saw how we could use these functions in formulas and in combination with other functions.

Both these functions are very helpful in filtering out data from thousands of rows. And when used together with other important functions of Excel – they can prove even more useful🥂

The three most important functions of Excel are the VLOOKUP, SUMIF, and the IF functions.

Enroll in my free 30-minute Email course today to learn these functions (and more!)

Other resources

Learn more about the INDEX and the MATCH function that can be super useful when used together with the ROW/ROWS functions.

If you liked this article on the Excel ROW function, we bet you will love to know about Excel Column Numbers too.