While working with Excel, one usually comes across minor but repetitive tasks. Knowing the correct way of performing these tasks will save you a lot of time.
One of these tasks is the automatic numbering of rows in Excel. (it’s also called serial number).
Now you might think one way of numbering rows is to do it manually, and you are correct. But it’s not the best and most efficient way. Imagine you have thousands of rows that have to be numbered. Doing this task manually is time-consuming and tiring.
How to automatically number rows in excel? There are many different ways for automatic numbering of rows in Excel and in this article, we’re going to explore each of those methods. There are other likely ways to perform this task which you might come up with, so go ahead and share them with us.
How to Number Rows in Excel
The optimal method for automatic numbering in Excel depends on the data set you’re working on.
For example, you might be working with a continuous data set that starts from row 1, or a set of data that starts from some other row – other than row 1-, or collection of data that includes empty rows and you would only like to number the rows that contain data.
You can use the best method that fits your type of data set.
1. Using the Fill Handle
The Excel fill handle tool manages to discern a pattern based on the cells that already contain data and it automatically fills the other rows using that pattern. Let’s take a look at an example:
Imagine you have a database such as this:
- Enter the number ‘1’ in cell A2 and the number ‘2’ in cell A3
- Select both cells. (A2 and A3)
- A small square appears on the bottom right corner of the last cell
- Move the mouse cursor to that square. It will turn to a plus (+) icon
- Double-click on the plus icon. It will automatically fill the rest of the column
The fill handle recognizes the pattern of the data in the selected cells and fills the rest of the cells based on that pattern. In this case, the numbers in the cells are increasing by 1. So, it will continue filling the cells in the same manner—3, 4, 5, and so on.
Please, note that if the adjacent column does not contain data, the double-click on the plus sign will not work. In that case, you have to click and hold the plus sign, and then drag down to fill the cells.
2. Using Fill Series
Fill handle provides a quick way for numbering rows in Excel, but Fill series gives you more control in how the numbering works. Let’s take a look.
Suppose you’re working with the following database:
- Enter the number ‘1’ in cell A2
- Go to the Home tab
- In the Editing group, click the ‘Fill’ dropdown menu
- Select ‘Series’ from the menu
- In the ‘Series’ window, select the ‘Columns’ option from the ‘Series in’ section
- Enter the ‘Stop value.’ In this case, since we have 8 records, we will enter 8. If you don’t enter any value, the Fill Series will not work
- Click ‘OK’
This action will instantly number the rows from 1 to 8. This can be a useful method while trying to number rows. Unlike Fill Handle, the adjacent column doesn’t need to contain data.
Note: If your data contains empty rows, Fill Series will still number the empty row.
3. Using the ROW Function
Another method is using Excel functions for numbering rows.
In the Fill Handle and FIll Series methods, the number assigned to rows is a static value. Meaning if you add or remove rows in your sheet, the numbers which were assigned do not change. Therefore, you will have to follow the steps again to update the row numbers to correct values.
We can fix this problem using formulas. You can use the ROW function for numbering rows.
In order to number the rows using this function, enter the formula below for the first row and then copy it for the rest:
=ROW(A2) -1
The ROW() function shows the number of the current row. That’s why in this example, we subtracted 1 from the formula and wrote it as ROW () -1. If your data starts from row five, you would have to write the formula as such: ROW()-4
The advantage of using this method for numbering rows is that when you remove or add new rows to the mix, you will not mess up the numbering. It will automatically adjust the row numbers based on the new information.
Note that as soon as a row is deleted, the numbers adjust accordingly.
This method doesn’t take empty rows into account. It will still number rows even if they contain no data at all.
You can use the formula below to hide the empty row numbers.
IF(ISBLANK(B2),””,ROW()-1)
4. Using the COUNTA Function in Excel
If you want only to number the rows that contain data, this is the method you have to use.
This method uses the COUNTA function, which counts the cells in the empty area. Take a look at this example:
Imagine you have the following database:
Note that there are empty rows in the data set. Here is the formula to number the rows without counting the rows that are empty and have no data:
=IF(ISBLANK(B2),””,COUNTA($B$2:B2))
The IF function checks whether the cell adjacent to column B is empty or not. If it is, it will return an empty string. Otherwise, it will return the number of the row.
5. Using the SUBTOTAL Function for Filtered Data
Sometimes while working with very large data sets, you have to filter the data to your specific criteria and then copy the filtered data to another sheet.
In instances like this, if you have numbered the rows, you will find the copied data has the wrong and outdated row numbers. That will force you to update the row numbers once again manually.
In order to fix this issue, you can use the SUBTOTAL function to automatically update the row numbers.
6. Creating a table in Excel
Tables in Excel is a great tool that you should use while working with data. It simplifies the tasks for managing data in Excel.
We also recommend this method compared to all the others mentioned earlier.
Let’s focus on how to correctly number rows using the Excel tables feature.
- Select all of your data
- Go to the Insert tab
- Click on the Table icon. (You can also use the keyboard shortcut Ctrl + T)
- In the Create Table window, check that your selected range is correct
- Click OK. This will turn your data to a table format
- Enter this formula in cell A2. Please note that the formula will be immediately copied to the other rows.
=ROW()-ROW(Table1[#Headers])
Please also note that in the formula, we used the name Table1 because it’s the name of our table in the example. You will have to replace it with the name of your own table.
There are some advantages in using tables for numbering rows in Excel:
- Because the tables in Excel automatically insert formulas in all of the rows, as you add or remove rows, the numbering will be automatically updated.
- If you add new rows to the bottom of the sheet, the table will automatically expand and include the new rows. It will also number the rows automatically.
7. Adding 1 to the Number of the Previous Rows
This is a very simple yet efficient method.
The idea is to add 1 to the number of the previous row. (The number in the top cell)
Suppose you have the following database:
- In the first cell of the first row, manually enter the number 1. In this example the cell is A2
- In cell, A3 enter the formula ‘= A2 + 1’
- Copy the formula for the rest of the cells
This will number each of the cells with the corresponding row number. It will also number empty rows if there are any.
If any row is added, the numbers will not be updated. Also, if a row is deleted, it will remove the numbers for all the rows and instead will show a reference error. Even though it’s a quick method, it’s usually not recommended as it will cause problems in the future.
These were some of the ways you could use autofill row numbers in Excel. If you can think of any other way of performing this task, please feel free to contact us or comment below.