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

**Table of Contents**Expand

## 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:

`=E5+1`

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

`=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`

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

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

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.

`=OFFSET(E5,-1,0,,)+1`

### 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:

`=ROW()-ROW(Table2[#Headers])`

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

## 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**