Shakil Ahmed
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
=B4+1Our 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.
=ROW(B1) 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.
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.
=ROWS($B$4:B4) 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.
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.
=COUNTA($C$4:C4) 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
=IF(ISBLANK(C4),"",COUNTA($C$4:C4))
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.
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.
=SUBTOTAL(3,$C$4:C4)
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.
=AGGREGATE(3,4,$C$4:C4)
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
=ROW()-ROW(Table1[[#Headers],[Sl. No]])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.
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.
