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 it manually, can you?

Excel will do it for you. The ROW function of Excel returns the cell reference number of any given row. And this guide teaches you how you can use it.

So without further ado, let’s dive right in. 😀

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

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 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 the cell reference?

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. Upon doing so, the function returns the row number of that particular cell.

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

1. Select cell reference C7 on the worksheet.

Selecting the serial number C7.

In the cell, type the formula = ROW() and press enter.

The ROW function returns the row number 7 as given in the argument.

First cell return row number 7.

Similarly, if you try writing = ROW(M9), the formula returns the row number 9 as the row number is already given in the argument.

The first row returns 9th row number in Excel.

It’s as easy as that. 😉

You can also try the same with an array. The Excel row function returns the number or count of cells selected.

Let’s understand this with an example.

Say we apply the function in cell A2, and the ROW 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 returns the row numbers of the selected range of cells. This function is very helpful in complex formulas. And we will see examples of such formulas further in the article.

How to Get Row Number With the ROWS Function

The ROWS function deals with an array of cells and gives the count of the cells selected.

Its syntax is:

=ROWS(range)

An important point here is that the arguments given in this function are not optional. You need to insert a value in range for Excel to count the rows.

A simple example of using the ROWS function to count values can be given as follows.

Say we want a count of the rows in between cells A2 and A7. Enter values in the formula bar as:

=ROWS(A2:A11)

Using range in new cell with ROW function.

The function returns a value of 10, which is equal to the count of rows in that range of cells – since there are ten cells from A2 to A11.

Note that the ROWS function only counts the number of cells 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 functions look the same and perform almost the same operations. So what is the difference between the ROW function and the ROWS function?

Even though it doesn’t seem likely, these two ROW functions are quite different. Let’s see this below.

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

= ROW (D3) will return the row number of the given value, i.e., 3 in this case.

Excel ROW function returns the row number 3

=ROWS(D3) will return the count of the rows used in the formula, i.e., 1 in this case.

Using ROWS function returns the count of the rows.

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

With an array, the ROW function represents the number of rows used in that range. In contrast, the ROWS function returns the count of the rows in the range of cells.

Moreover, you can use the ROW function without any arguments. And the function will return the row number of that cell. However, if you use the ROWS function without argument, Microsoft Excel will display an error prompt.

Examples of ROW And ROWS

Let’s see some common examples of using the functions simultaneously to get a better understanding.

We will use the INDEX function and the MATCH function for the following formula. And HP fans are simply going to love this example. 😀

Our data set looks something like this:

Excel example reference

And the formula we are to apply looks like this:

Following formula uses MATCH function, INDEX and ROW function.

Yes, we know – that’s terribly confusing. We didn’t understand it the first time, either. So we’ll take you through all the prospects of this formula one by one once we see what it does.

In this dataset, we wanted to know the row number of an O.W.L Eleven. So we applied the formula and hit enter. The result was this:

Using the ROW function to get a row number

Let’s break down the formula.

MATCH(E6,B1:B8,0) – the MATCH function returns the cell’s relative position in the range B1:B8, which, in this case, is Eleven.

INDEX(A1:A8, MATCH(E6, B1:B8,0)) – the INDEX function will use the matched value to return the reference from within the given range.

= ROW (INDEX(A1:A8, MATCH(E6,B1:B8,0))) – the ROW function returns the row number from multiple references.

Note that ROW and INDEX both work together to return the reference. Try using the INDEX function separately. You will see Excel returns the numeric value of that named range instead of showing the row number.

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:

Formula for using ROWS function

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

Choosing a random character.

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

That’s it – Now what?

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

Both these functions are very resourceful when you need to filter out data from thousands of rows. And when used together with other important functions of Excel – they are even more resourceful.

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

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

Other resources

If you liked this article on the Excel ROW function, we bet you will love our other articles.

Some of these include Excel Column NumbersMoving Columns, and Inserting Multiple Rows.