Mursalin Ibne Salehin
In this article, we will learn to add the automatic serial number with a formula in Excel. There are many ways to add automatic serial numbers in Excel. You can use the fill handle and fill series features of Excel for this purpose. But today we will show 10 methods with formulas. Using these methods, you can easily add the automatic serial number in Excel. So, without any delay, let’s start the discussion.
Download Practice Book
You can download the practice book from here.
10 Easy Ways to Add Automatic Serial Number with Formula in Excel
To explain the methods, we will use a dataset that contains information about the Marks of some students. We will add automatic serial numbers to the dataset using some formulas. Throughout the article, we will try to use the same dataset.
1. Apply Simple Formula to Add Automatic Serial Number in Excel
In the first method, we will use a simple formula to add the automatic serial number in Excel. In this case, we will add 1 with the value of the previous cell. Let’s follow the steps below to see how we can implement the formula.
STEPS:
- First of all, type 1 in Cell B5.
- Secondly, select Cell B6 and type the formula below:
=B5+1
In this formula, we are adding 1 with the previous cell value to get the serial numbers automatically. That is why it is important to type serial number 1 at the beginning.
- After that, press Enter and drag the Fill Handle down.
- Finally, you will see serial numbers like the below picture.
Read More: How to Create a Formula for Serial Number in Excel (7 Methods)
2. Add Automatic Serial Number with Excel ROW Function
One of the famous ways to add automatic serial numbers is to use the ROW function. The ROW function returns the row number of a given cell. We can easily use this function to create a formula for expressing the serial number. Let’s observe the steps below to learn the method.
STEPS:
- In the first place, select Cell B5 and type the formula below:
=ROW()-ROW($B$4)
In this formula, we have used the ROW function. If you type =ROW() in Cell B5, then the output will be 5 because it indicates the 5th row. The output of ROW($B$4) will always be 4. Because we have used absolute cell reference here. That is why we have subtracted ROW($B$4) from ROW().
- After that, hit Enter and drag down the Fill Handle.
- Finally, you will see the automatic serial numbers in the dataset.
Read More: How to Add Numbers 1 2 3 in Excel (2 Suitable Cases)
3. Use Name Manager to Add Automatic Serial Number in Excel
We can also use the name manager to add automatic serial numbers in Excel. In this method, we will define a formula with a name and use it to get the serial numbers. Let’s follow the steps below to see how we can do this.
STEPS:
- To begin with, go to the Formulas tab and select Define Name. It will open the New Name dialog box.
- In the New Name dialog box, add a name. We have named the formula ‘Serial’.
- Also, type the formula below in the ‘Refers to’ box:
=INDIRECT(“R[-1]C”,FALSE)
Here, we have used the INDIRECT function. The INDIRECT function returns the cell reference of a given text string. The first output of this formula is 0. It indicates the previous cell.
- After that, select Cell B5 and type the formula below:
=SUM(Serial,1)
In this formula, we have used the SUM function. It sums up the value of the Serial function and 1. In the first case, the output is 1. Now, if you apply the formula in Cell B8, then, the formula will act like =SUM($B$7,1). That means it will sum up the value of Cell B7 with 1 and show it in Cell B8.
- In the following step, hit Enter and drag the Fill Handle down.
- In the end, you will see automatic serial numbers like the below picture.
4. Insert Excel Formula with COUNTA Function to Generate Automatic Serial Number
You can also use the COUNTA function to generate automatic serial numbers in Excel. The COUNTA function counts the number of cells in a non-empty range. Let’s follow the steps below to see how we can use the COUNTA function to add automatic serial numbers.
STEPS:
- Firstly, type the formula below in Cell B5:
=COUNTA($C$5:C5)
In this formula, we have used the COUNTA function to count the number of cells in the range $C$5:C5. This count will give us the serial numbers automatically. In Cell B6, the formula will change into =COUNTA($C$5:C6). That is why the output will be 2 as there are 2 non-empty cells in that range.
- After that, press Enter and drag the Fill Handle down.
- In the end, you will be able to see the automatic serial numbers.
Read More: How to Increment Row Number in Excel Formula (6 Handy Ways)
5. Create Automatic Serial Number in Excel Using SUBTOTAL Function
Another way to create automatic serial numbers is to use the SUBTOTAL function. The SUBTOTAL function returns a subtotal in a list. It gives users the opportunity to perform different operations in specific groups in a list. The SUBTOTAL function has 2 required arguments. You need to insert the function number in the first argument and the reference in the second argument. Let’s follow the steps below to see how we can implement the SUBTOTAL function to add automatic serial numbers.
STEPS:
- In the beginning, select Cell B5 and type the formula below:
=SUBTOTAL(3,$C$5:C5)
In this formula, we have used the SUBTOTAL function. The first argument indicates the function number. And function number 3 refers to the COUNTA function. So, it will count the cells in the range $C$5:C5.
- Secondly, press Enter and drag down the Fill Handle.
- Finally, the dataset will look like the picture below.
Read More: Subtotal Formula in Excel for Serial Number (3 Suitable Examples)
6. Apply SEQUENCE Function to Get Automatic Serial Number
We can also apply the SEQUENCE function to get automatic serial numbers in Excel. The SEQUENCE function generates a list of sequential numbers. This is an array function and it is available in Excel 365. We will use the COUNTA function inside the SEQUENCE function to create the formula for automatic serial numbers. So, let’s pay attention to the steps below to learn more about this method.
STEPS:
- First of all, select Cell B5 and type the formula below:
=SEQUENCE(COUNTA(C5:C14))
In this formula, the output of COUNTA(C5:C14) is 10. So, the formula becomes =SEQUENCE(10) after the operation of the COUNTA function. And finally, the SEQUENCE function generates a sequential list from 1 to 10.
- In the following step, hit Enter to see the results like the picture below.
Read More: How to Create a Number Sequence in Excel Without Dragging
7. Combine IF and ROW Functions to Add Automatic Serial Number
In this method, we will combine the IF and ROW functions to add automatic serial numbers in Excel. To explain the method easier, we will use a slightly different dataset. You can use this method in the previous dataset. Here, we will add a name in Column C and the serial number will be visible automatically in Column B.
So, let’s follow the steps below to see how we can combine the IF and ROW functions to get serial numbers automatically.
STEPS:
- Firstly, select Cell B5 and type the formula below:
=IF(C5="","",ROW()-ROW($B$4))
In this formula, we have used the ROW function inside the IF function. Here, the formula will check if Cell C5 is empty or not. If Cell C5 is empty, then the output of the formula will also be empty. Otherwise, it will show the value of ROW()-ROW($B$4). In the case of Cell B5, the value of ROW()-ROW($B$4) is 1.
- Secondly, drag down the Fill Handle to copy the formula in the range B6:B10.
- At this moment, if you type a name in Cell C5, then you will see the serial in Cell B5.
- After that, type a name in Cell C6 and C7 respectively and you will get the serial numbers automatically.
- Finally, repeat the previous step to see results in the whole dataset.
Read More: How to Create a Number Sequence with Formula in Excel
8. Use OFFSET Function to Create Automatic Serial Number
We can also use the OFFSET function to create automatic serial numbers in Excel. But this formula is vulnerable because it can give errors for various reasons. The OFFSET function returns a reference to a range. It has 3 compulsory arguments. They are reference, rows, and columns. Let’s follow the steps below to see how the OFFSET function can be implemented to get the serial numbers.
STEPS:
- In the beginning, insert an empty row into the dataset to avoid errors. Here, Row 5 is the added empty row.
- Secondly, select Cell B6 and type the formula below:
=OFFSET(B6,-1,0)+1
In this formula, the first argument of the OFFSET function is Cell B6. The second argument is -1 which indicates the previous row. And the third argument is 0 which refers to the same column. So, the output of OFFSET(B6,-1,0) is 0. Because Cell B5 is empty. So, the output of the formula is 1 in the case of Cell B6.
- After that, hit Enter and drag down the Fill Handle to copy the formula.
- Finally, the dataset will look like the picture below.
Read More: Auto Numbering in Excel After Row Insert (5 Suitable Examples)
9. Add Roman Numbers as Automatic Serial Numbers
Sometimes we need to add roman numbers as serial numbers in Excel. In Excel, we can do it easily by using the ROMAN function. The ROMAN function converts any numbers to roman numbers. Here, we will use the same dataset. So, let’s follow the steps below to know more about this method.
STEPS:
- Firstly, select Cell B5 and type the formula below:
=ROMAN(ROW()-ROW($B$4))
This formula is quite similar to that of Method-2. We inserted the formula of Method-2 inside the ROMAN function.
- After typing the formula, press Enter and drag the Fill Handle down.
- Finally, you will be able to add serial numbers automatically to the dataset.
10. Insert Dynamic Serial Numbers for Filters
In the last method, we will show how you can add dynamic serial numbers for filters in Excel. To do so, we will use the SUBTOTAL function. We will insert the formula first and then apply filters to our dataset. Let’s follow the steps below to see how we can create dynamic serial numbers in Excel.
STEPS:
- In the first place, select Cell B5 and type the formula below:
=SUBTOTAL(3,C$5:C5)
In this formula, we have used the absolute cell reference for the row index only.
- Secondly, hit Enter and drag down the Fill Handle.
- After that, you will see the automatic serial numbers in the dataset.
- In the following step, go to the Home tab and select Sort & Filter. A drop-down menu will appear.
- Select Filter from there.
- As a result, you will see the drop-down arrows in the headers.
- Now, click on a drop-down arrow and apply the filter according to your preference.
- Here, we have unchecked serial numbers 4, 5, and 6.
- Click OK to proceed.
- As a result, you will see the filtered rows are hidden and the serial numbers are updated automatically.
Conclusion
In this article, we have discussed 10 easy methods to add Automatic Serial Number with Formula in Excel. I hope this article will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. You can also visit the ExcelDemy website for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
