How to Number Rows in Excel (9 Simple Methods)

In Excel, you may come across repetitive tasks often. To number rows is one of these tasks which can be a time-consuming one. There are several ways of performing this task in a time-saving manner.

Our agenda for today is to show you how to number rows in Excel. Before diving into the session, let’s get to know about the dataset that is the base of our examples.

Here we have listed a few students along with their scores in three courses. Using this dataset, we will show you how to number rows.

Note that this is a basic table to keep things simple. In a practical scenario, you may encounter a much larger and complex dataset.

Practice Workbook

You are welcome to download the practice workbook from the following link.

Numbering Rows in Excel

As we have mentioned there are several for numbering rows. Let’s explore them.

For example, we will add the serial number to each of the rows here.

1. AutoFiller to Number Rows

We can use the Excel AutoFill feature to number rows. First of all, you need to insert a couple of numbers in the first two cells.

We need to insert two rows so that Excel can understand the pattern. Then select both the rows together. After selecting the rows you will see a tiny rectangle box at the bottom right corner of selected cells. This is called Fill Handle.

Double click there or drag it down to the remaining rows.

You will find numbers for the rows.

2. Using Fill Series

Another worth mentioning feature is Fill Series. We can use this feature for numbering the rows.

Insert number at the first cell and select that cell. Now click Series from the Fill option in the Editing section of the Home tab.

You will find a Series dialog box in front of you. From there, select Columns in Series in section. Don’t get confused. This denotes that the series will be filled within a single column.

Set the Step Value and Stop Value as your situation demands (here we have set 1 and 10 for these respectively). Then click OK.

Don’t forget to check Linear in the Type section.

You will find numbers for the rows.

3. Add 1 to Previous Cell to Number Rows

To generate row numbers we can use the addition operation. Again you need to insert a number in the first cell.

Now at the next cell write the following formula

Our Sl. No column is in the column B column of Excel, where the first row is B4.

Adding 1 we will find the next number from the previous number in this cell.

You can manually write the formula, but using AutoFill will be a convenient one.

4. Using ROW Function

We can use several Excel basic functions for numbering the rows. The ROW function can be one of those functions. Check this ROW article to know about the function.

In previous sections, we have inserted at least one number manually. But using this ROW function we don’t need to write any number of our own.

Let’s write the formula.

Since we want to number it from 1, we inserted the first row (can be from any column) within it.

You will find the number. Now for the rest of the rows write the formula, or exercise the AutoFill feature.

Read More: How to Create Rows within a Cell in Excel (3 Methods)

5. Using ROWS Function

There is a ROW family function called ROWS. The ROWS function returns the count of rows in a given reference. To know more about the function, go through the article: ROWS.

Let’s use the function. We need to insert an array reference within ROWS.

This will provide the difference between the cells.

We have provided the first cell number in absolute cell reference so that it remains the same for the next cells.

Write the formula or utilize AutoFill, you will find numbers for the rows.

Similar Readings

• How to Increase Excel Row Limit (Using Data Model)
• Expand or Collapse Rows with Plus Sign in Excel (4 Easy Methods)
• How to Move Rows Down in Excel (6 Ways)
• Resize All Rows in Excel (6 Different Approaches)

6. Using COUNTA Function

We can use the Excel COUNTA function to number the rows. COUNTA counts the cells that are not empty. So this function will help number for the rows that have values. For more information, check this article: COUNTA.

Let’s use the function.

We are checking whether the adjacent cell (Name column) has any value or not.

It numbers the row. If we emptied the cell C4, we would have found 0 from our formula

If we don’t want any numbers for empty cells. We can use a couple of functions to make that happen.

We need to use IF and ISBLANK functions along with COUNTA. To know about the functions feel free to check these articles: IF, ISBLANK.

Let’s see the formula

We have checked whether the C4 cell is empty (blank) or not. For blank, we set an empty string, otherwise trigger COUNTA.

Let’s rewrite the name in cell C4

Our formula numbers the row. Let’s write the formula for the rest of the rows or use the exercise AutoFill.

Read More: How to Highlight Row If Cell Is Not Blank (4 Methods)

7. SUBTOTAL Function to Number Rows

We can use the SUBTOTAL function for numbering rows. SUBTOTAL returns an aggregate result for supplied values. Several functional operations can be performed through this function.

Visit this SUBTOTAL article to know about this function.

Let’s write the formula using this function.

3 denotes the COUNTA operation. So, you can understand this formula performs like the previous section.

Exercise AutoFill for the rest of the rows.

Similar to the earlier section, we can use the IF-ISBLANK function for this formula.

8. AGGREGATE Function to Number Rows

We can use the AGGREGATE function for numbering the rows. Like SUBTOTAL this AGGREGATE function also performs several functional operations.

To learn about the AGGREGATE function visit the article: AGGREGATE.

3 denotes the COUNTA operation. 4 for ignoring nothing. Once we call for COUNTA then the rest of the operation is the same as the operation described in the Using COUNTA Function section.  

Utilize the AutoFill feature for the rest of the rows.

9. Creating Table

Another approach to numbering rows is numbering through tables. Select the entire list and select any of the table formats from the Format as Table option in the Home tab.

Don’t forget to check My table has headers and click OK.

Now in the first row write the following formula

This is the ROW function that will provide the numbers depending on the difference. And you don’t need to drag down the formula for the rest of the cells.

It will number all the rows automatically in one go.

Read More: Excel Alternating Row Color with Conditional Formatting [Video]

Conclusion

That’s all for today. We have listed several methods to number rows in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other methods that we have missed here.

Related Articles

• How to Group Rows in Excel with Expand or Collapse (5 Methods)
• Group Rows by Cell Value in Excel (3 Simple Ways)
• How to Select Row in Excel If Cell Contains Specific Data (4 Ways)
• Highlight Row If Cell Contains Any Text