The best way to number rows in Excel depends on the type of data set you to have. For example, you could have a continuous data set that begins with row 1 or a dataset that begins with a different row. Alternatively, you may have a dataset with a few blank rows and only want to number the rows that are filled.
You can use any of the methods that work with your dataset.
Fill Handle
identifies a pattern from a few filled cells and can be quickly used to fill the entire column.
Assume you have the following dataset:
Here’s how to quickly number the rows with the fill handle:
- Enter 1 in cell A2 and 2 in cell A3.
- Choose both cells (A2 and A3).
- Take note of the small square at the bottom-right corner of the selection.
- When you move your cursor over this square, you’ll notice that the cursor changes to a plus sign.
- When you double-click on the fill handle square (while the cursor is in the plus icon form), it will automatically fill all the cells until the dataset is finished.
- It’s worth noting that Fill Handle automatically detects the pattern and fills the remaining cells with it. The pattern, in this case, was that the numbers were being incremented by one.
- If there is a blank row in the dataset, the fill handle will only work until the last non-blank row.
Also, if there is no data in the adjacent column, double-clicking the fill handle will not work. However, you can place the cursor on the fill handle, hold down the right mouse button, and drag it down. It will fill the cells covered by the dragging of the cursor.
Making Use of Fill Series
While Fill Handle is a quick way to number rows in Excel, Fill Series allows you to have much more control over how the numbers are entered.
Assume you have the following dataset:
The following are the steps for using Fill Series to number rows in Excel:
- In cell A2, enter 1.
- Navigate to the Home tab.
- Click the Fill drop-down in the Editing Group.
- Select ‘Series…’ from the drop-down menu.
- Select ‘Columns’ in the ‘Series in’ options in the ‘Series’ dialog box.
- Set the Stop value. In this case, we can enter 26 because we have 26 records. Fill Series will not work if no value is entered.
- Click the OK button.
This will instantly number the rows from one to twenty-six.
When you begin by entering row numbers, ‘Fill Series’ can be useful. Unlike Fill Handle, it does not require that the adjacent columns be already filled.
Fill Series will work even if there is nothing on the worksheet.
Please keep in mind that if there are blank rows in the middle of the dataset, Fill Series will still fill the number for that row.
Making Use of the ROW Function
To number the rows in Excel, you can also use Excel functions.
The serial number inserted in the Fill Handle and Fill Series methods is a static value. This means that if you move the row (or copy and paste it elsewhere in the dataset), the row numbering will remain unchanged.
This shortcoming can be overcome by using Excel formulas.
In Excel, you can get the row numbering by using the ROW function.
Enter the following formula in the first cell and copy it for all the other cells to get the row numbering using the ROW function:
=ROW()-1
The ROW() function gives the row number of the current row. So I have subtracted 1 from it as I started from the second row onwards. If your data starts from the 5th row, you need to use the formula =ROW()-4.
The best part about using the ROW function is that deleting a row in your dataset will not mess up the numberings.
Because the ROW function does not refer to any cells, it will automatically (or should I say AutoMagically) adjust to provide the correct row number. As illustrated below:
Again, this would disregard any blank records in the dataset. Even if you have blank rows, the row number will still be displayed.
The following formula can be used to hide the row number for blank rows, but it will not change the row numbers (such that the next row number is assigned to the next filled row).
IF(ISBLANK(B2),"",ROW()-1)
Making Use of the COUNTA Function
If you want to number rows in such a way that only those that are filled out receive a serial number, this is the method to use.
It employs the COUNTA function, which counts the number of non-empty cells in a range.
Assume you have the following dataset:
There are some blank rows in the above-shown dataset.
Here is the formula for numbering the rows without counting the blank rows.
=IF(ISBLANK(B2),"",COUNTA($B$2:B2))
The IF function determines whether or not the adjacent cell in column B is empty. If it’s empty, it returns a blank; otherwise, it returns the count of all the filled cells up to that point.
For Filtered Data, Use SUBTOTAL
You may have a large dataset and want to filter it before copying and pasting the filtered data into a separate sheet.
You’ll notice that the row numbers remain the same if you use any of the methods shown so far. This means you’ll have to update the row numbering when you copy the filtered data.
In such cases, the SUBTOTAL function can update the row numbers automatically. The row numbers will be preserved even if the data set is filtered.
Let me demonstrate how it works with an example.
Assume you have the following dataset:
If I filter this data based on Product A sales, the result is as follows:
It is worth noting that the serial numbers in Column A are also filtered. As a result, you only see the numbers for the visible rows.
While this is the expected behavior, you can use the SUBTOTAL function to obtain serial row numbering so that you can simply copy and paste this data somewhere else.
The SUBTOTAL function ensures that even the filtered data has continuous row numbering.
=SUBTOTAL(3,$B$2:B2)
The 3 in the SUBTOTAL function indicates that the COUNTA function will be used. The second argument specifies the range to which the COUNTA function will be applied.
The SUBTOTAL function has the advantage of dynamically updating when you filter the data (as shown below):
It’s worth noting that even when the data is filtered, the row numbering updates and stays consistent.
Making a Table in Excel
When working with tabular data, Excel Table is a must-have tool. It greatly simplifies data management and utilization.
This is also my favorite technique out of all the ones demonstrated in this tutorial.
Let me first demonstrate how to properly number the rows in an Excel table:
Choose the entire dataset.
Navigate to the Insert Tab.
Select the Table icon (or use the keyboard shortcut Control + T).
Make sure the range is correct in the Create Table dialog box.
Click the OK button. Your tabular data will be converted into an Excel table as a result of this.
Enter the following formula in cell A2. It is important to note that once you enter the formula, it will automatically fill in all of the cells in that column (you can read more about calculated columns here).
=ROW()-ROW(Table2[#Headers])
It’s worth noting that I used Table2 in the formula above because that’s the name of my Excel table. Table2 can be replaced with the name of the table you have.
There are some additional advantages to using an Excel Table when numbering rows in Excel:
- Because the formula is automatically inserted in the entire column when you insert a new row in the Table, it works when you insert a new row in the Table. This means that when you insert or delete rows in an Excel Table, the row numbering will be updated automatically (as shown below).
- If you add more rows to the data, Excel Table will automatically expand to include this information. And, because the formulas in the calculated columns are automatically updated, it would insert the row number for the newly inserted row (as shown below).
Adding 1 to the Previous Row Number
This is a straightforward method that works.
The idea is to multiply the previous row number by one (the number in the cell above). This ensures that the number in the following rows is increased by one.
Assume you have the following dataset:
The following are the steps for entering row numbers using this method:
- Enter 1 manually in the first row’s cell. It’s in cell A2 in this case.
- Enter the formula =A2+1 in cell A3.
- Copy and paste the formula into all of the column’s cells.
The preceding steps would insert serial numbers into all of the column’s cells. If there are any empty rows, this will still insert the row number for them.
Also, if you insert a new row, the row number will not be updated. If you delete a row, all the cells below it will display a reference error.
These are some quick methods for inserting serial numbers into tabular data in Excel.
If you use another method, please share it with me in the comments section.