How to Add Automatic Serial Number with Formula in Excel

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.

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:

• After that, press Enter and drag the Fill Handle down.

• Finally, you will see serial numbers like the below picture.

2. Adding 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:

• After that, hit Enter and drag down the Fill Handle.

• Finally, you will see the automatic serial numbers in the dataset.

3. Using 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:

• After that, select Cell B5 and type the formula below:

• 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.

Read More: How to Auto Generate Number Sequence in Excel

4. Using Excel 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:

• After that, press Enter and drag the Fill Handle down.

• In the end, you will be able to see the automatic serial numbers.

5. Creating Automatic Serial Number in Excel with 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 Formula in Excel to add Serial Number automatically.

STEPS:

• In the beginning, select Cell B5 and type the formula below:

• Secondly, press Enter and drag down the Fill Handle.

• Finally, the dataset will look like the picture below.

6. Applying SEQUENCE Function to Add Automatic Serial Number with Formula in Excel

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 Microsoft 365. We will use the COUNTA function inside the SEQUENCE function to create the formula for automatic numbers sequence without dragging. So, let’s pay attention to the steps below to learn how to add automatic numbering in Excel.

STEPS:

• First of all, select Cell B5 and type the formula below:

• In the following step, hit Enter to see the results like the picture below.

7. Combining IF and ROW Functions to Add Automatic Serial Number in Excel

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:

• 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: Auto Serial Number in Excel Based on Another Column

8. Using Excel 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:

• 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

9. Adding 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:

• 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. Adding Dynamic Serial Numbers in Excel with the Filter Option

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:

• 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.

Read More: Automatically Number Rows in Excel

Download Practice Book

You can download the practice book from here.

Conclusion

In this article, we have discussed 10 easy methods to add an 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. If you have any suggestions or queries, feel free to ask in the comment section below.

Related Articles

• How to Autofill in Excel with Repeated Sequential Numbers
• How to Number Columns in Excel Automatically
• Auto Generate Invoice Number in Excel
• Auto Generate Serial Number in Excel VBA

<< Go Back to Serial Number in Excel | Numbering in Excel | Learn Excel