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.

**Table of Contents**Expand

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

## 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:

`=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 **5**th 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.

## 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:

`=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.

**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**:

`=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.

## 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:

`=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.

## 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:

`=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.

## 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:

`=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: **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:

`=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

## 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:

`=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. 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:

`=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.

**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**