How to Get Excel Row Number? (8 Practical Examples)

Row numbers act as reference points for conducting calculations in Excel. They help users to identify and locate particular rows in a worksheet. Row numbers serve multiple purposes in Excel. Specific row number is essential for different tasks within Excel.

In this Excel tutorial, we use Microsoft 365 to get row number in Excel. You can use any other version at your disposal.

We will provide you with 8 of the easiest methods you can apply to get row number and use them in your calculations. We will use built-in features like the Fill Handle, Fill Series along with other Excel functions like ROW, SUBTOTAL, OFFSET to get row number.

Download Practice Workbook

How to Get Excel Row Number?

There are multiple methods you can use to get row number in Excel. Here, we will discuss 8 different methods to get Excel row number. Such as:

1. Using Fill Handle Feature
2. Apply Fill Series Feature
3. Using Cell reference
4. Excel built-in functions and so on.

1. Use Fill Handle to Get Number Rows Automatically

We can use the Fill Handle feature that follows a pattern to get the number of rows of a range automatically.

• Insert 1 and 2 in the first two cells of a range to make a series.

• Select both these cells.
• Drag the Fill Handle on the bottom right corner to get the row number automatically. You can double-click on the Plus (+) icon to get the same result.

2. Apply Fill Series Feature for Getting Row Number

The fill series feature of the Home tab is easily applicable for getting Excel row number.

• Select the first cell of a range and insert 1 as the starting row number.
• Go to Home tab > Editing > Fill > Series.

• It will show a popup named Series.
• Select Columns in the Series in section and keep the Type as Linear.
• Specify the stop value if you know how many rows are there and click on OK.

• The rows are now numbered.

• If you do not know the total number of rows, select the range where you want to show row numbers. Then follow the steps shown previously and keep the stop value blank.

This is useful in case of a large dataset and you do not know how many rows are there.

3. Get Row Number Using Cell Reference

Excel’s cell reference makes it quite easy to get row number in various situations.

• Select the first cell of a range that is cell E5 and insert 1 as the starting row number. To get the row number from the 2nd row, use this formula with cell reference:

• Drag the formula down with the Fill Handle to get the row numbers of other rows.

4. Use ROW Function to Get Row Number

The ROW function is a dedicated function in Excel that returns the row number of a particular cell. The syntax of the row function is as follows:

• Insert the following formula on cell E5 and use the fill handle to get the row number of all the cells.

We subtract 4 from the number as our dataset starts from row 5. If it had started from row 2, we would have subtracted 1.

5. Get Row Number Using Combination of IF, ISBLANK & COUNTA Functions and Ignore Blank Rows

We can use the COUNTA function to get the row number. The COUNTA cannot count blank cells. So, if we have a blank cell in a range, the formula counts that cell as the same number as the previous cell. To get rid of this problem, we use it with a combination of the IF and ISBLANK functions. As a result, this formula does not put numbers to blank rows in the dataset.

• Use the following formula on cell E5 and use the fill handle to get the row number of all the cells.

6. Apply SUBTOTAL Function to Get Row Numbers for Filtered Data

You can use the SUBTOTAL function when you want to update row numbers automatically. This is particularly useful when you filter the data. The SUBTOTAL function will keep the row number unchanged when you apply filters.

• We can use the following formula on cell E5:

The number 3 in the SUBTOTAL function indicates that the COUNTA function should be used. The second argument represents the range on which the COUNTA function is applied.

After enabling the filter, filter only the Marketing department data. You will see the dataset will update automatically, put number to rows based on the updated dataset.

7. Use OFFSET Function to Get Row Number Without Column Headers

The OFFSET function is used to get row numbers that is slightly different from the other methods shown above. In this method, we have to use a dataset without a column header for the row numbers. We need the header to be empty in this formula to reference the cell above in the same column. Otherwise, the formula will return an error.

• To get row numbers, we use the following formula on cell E5 and drag the fill handle icon.

8. Use Table to Number Rows Automatically

We can create an Excel table to get row numbers. Tables are very useful as they are dynamic and can update row numbers automatically.

• Select the entire dataset and create a table using the keyboard shortcut Ctrl+T or by going to Insert tab > Table.

• A popup window will appear specifying the range where the table will be created.
• If your dataset has headers, check the appropriate box and click OK.

• After the table is inserted, type the following formula in the first cell of the Row Number column:

This formula added row numbers to all the rows in the table automatically.

How to Use ROWS Function to Get Total Number of Rows in Excel?

The ROWS function can return the total count of rows in a range. It does not return the row number of a certain row. The syntax of the ROWS function is as follows:

• The following formula will return the total rows in the range B5:D14 on cell E5:

What is the Difference Between The ROW and ROWS Functions?

The ROW and ROWS functions seem similar at first but there are some major differences between these two functions.

• The ROW function returns the row number of a particular row whereas the ROWS function returns the count of rows in a particular range.
• The ROW function can be used without any arguments. If we try to use the ROWS function without an argument, the formula will return an error.

What Things You Have To Remember?

• Row numbers always start from 1. This indicates the first row of a worksheet.
• The ROW function can be used without inputting any arguments.
• The cell above the column while using the COUNTA function to get row numbers must be empty.
• The ROWS function returns the count of rows in a range. It will not return the row numbers of the rows in a range.
• Row numbers will not update automatically unless you convert the dataset into a table.

Frequently Asked Questions

1. Which is the 1st row in Excel?

Ans: In Excel, the first row is row 1. It is labeled as 1 and is located at the top of the worksheet.

2. Can I use the ROWS function to number rows in a range?

Ans: No, the ROWS function returns the count of rows in a range. You can use the ROW function instead to number rows in Excel among other methods which we have shown in this article.

3. Is it possible to get the row number of the first row of a range by inputting the range as an argument?

Ans: Yes it is possible. Just insert @ in between = and the formula. Just like this:
=@ROW(B5:D14)

This will return 5 as the row number.

Conclusion

Having the ability to acquire the row number in Excel is an advantageous skill. It can significantly boost your effectiveness when handling data.

In this article, we have shown multiple methods to get Excel row number. We also counted the total number of rows in a range. Whatever approach you choose, be careful to master it thoroughly so that you can use it effectively in your dataset.

Feel free to let us know our thoughts and suggestions regarding the article in the comment box below. Also, visit ExcelDemy for numerous Excel-related articles.

Excel Row Number: Knowledge Hub

• How to Get Row Number of Current Cell
• How to Use Range with Variable Row Number
• Excel Find String in Column and Return Row Number

<< Go Back to Rows in Excel | Learn Excel