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

Get FREE Advanced Excel Exercises with Solutions!

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.

overview of excel 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.

insert 1, 2 to make 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.

using Fill handle to number rows


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.

select series from editing panel

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

Rows are numbered using fill series

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

keep stop value empty

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:
=E5+1
  • Drag the formula down with the Fill Handle to get the row numbers of other rows.

using cell reference to get row number


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:

=ROW (reference)
  • Insert the following formula on cell E5 and use the fill handle to get the row number of all the cells.
=ROW(B5)-4

utilizing ROW function

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.
=IF(ISBLANK(B5),"",COUNTA($B$5:B5))

row number using COUNTA function

Formula Explanation:

ISBLANK(B5)
Checks whether cell B5 is blank. If cell B5 is blank, it evaluates to TRUE otherwise, it evaluates to FALSE.

COUNTA($B$5:B5)
Counts the non-blank cells in the range from B5 to the cell in the current row.

IF(ISBLANK(B5),””,COUNTA($B$5:B5))
The IF function checks whether the cell B5 is blank. If the cell is indeed blank, then the formula returns an empty cell indicated by “”. If cell B5 is not blank, then it returns the count as done by the COUNTA function.


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:
=SUBTOTAL(3,$B$5:B5)

applying SUBTOTAL function

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.

SUBTOTAL function updates row number automatically


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.
=OFFSET(E5,-1,0,,)+1

using OFFSET function


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.

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

select range where to apply table

  • After the table is inserted, type the following formula in the first cell of the Row Number column:
=ROW()-ROW(Table2[#Headers])

using table to number rows

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:

=ROWS(array)
  • The following formula will return the total rows in the range B5:D14 on cell E5:
=ROWS(B5:D14)

ROWS function to count row numbers


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


<< Go Back to Rows in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Niaz
Maruf Niaz

Md. Maruf Niaz, BSc, Textile Engineering, Bangladesh University of Textiles, has worked with the ExcelDemy project for 11 months. He works as an Excel and VBA Content Developer who provides easy solutions to Excel-related problems and regularly writes fantastic content articles. He has published almost 20 articles in ExcelDemy. As an Excel & VBA Content Developer for ExcelDemy, he offers solutions to complex issues. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo