Microsoft Excel includes the **SUBTOTAL **function by default**.** This function provides the opportunity to perform a group of Excel function operations. If you are looking for some special tricks to use the subtotal formula for the serial number in Excel, you’ve come to the right place. There are three ways to use the subtotal formula for serial number in Excel. This article will discuss three suitable ways of using the subtotal formula for serial number. Let’s follow the complete guide to learn all of this.

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.

## 3 Suitable Examples to Use Subtotal Formula for Serial Number in Excel

In the following section, we will use three effective and tricky ways to use the subtotal formula for the serial number in Excel. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the **Microsoft Office 365** version here, but you can utilize any other version according to your preference.

### 1. Use of SUBTOTAL Function for Serial Number

Here, we will demonstrate how to use the subtotal formula for the serial numbers in Excel. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. The following dataset contains Student’s name and their corresponding exam score. One effective 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 walk through the steps to see how we can implement the **SUBTOTAL **function to add serial numbers.

**📌 Steps:**

- In the beginning, select
**Cell B5**and type the formula below.

`=SUBTOTAL(3,$C$5:C5)`

- Next, press
**Enter**.

- Now, drag the
**Fill Handle**icon to fill the other cells with the above formula. - Therefore, you will get the following serial number.

- In the following image, we can see that no new serial is created for the cells where there are no corresponding values for the related cells if we drag the
**Fill Handle**icon into the next few cells.

- Now, we are going to filter the data to show the only visible column serial number in the dataset.
- To do this, we have to select the range of cells and press
**Ctrl+Shift+L**.

- This will instantly add a filter to the data as shown below.

- Now, click on the
**Math**column drop-down icon, select**Number Filters**, and finally select**Greater Than**option.

- When the
**Custom AutoFilter**window appears, type your required number in the**is greater than**a box. - Then, click on
**OK**.

- You will therefore get the following column when you filter the Math column data based on a score greater than 70. The
**serial number is automatically generated**when the column is reduced according to the criteria.

**Read More: ****How to Increment Row Number in Excel Formula (6 Handy Ways)**

**Similar Readings**

**How to Create a Number Sequence with Text in Excel (5 Methods)****Create a Number Sequence in Excel Without Dragging****How to Create a Number Sequence in Excel Based on Criteria**

### 2. Combining SUBTOTAL with IF Function

Here, we will use another effective way to create serial numbers by applying **SUBTOTAL **and **IF** functions. The following dataset contains Student’s name and their corresponding exam score. Let’s walk through the following steps to create serial numbers.

**📌 Steps:**

- In the beginning, select
**Cell B5**and type the formula below.

`=IF(C5="","",SUBTOTAL(3,$C$5:C5))`

- Next, press
**Enter**.

- Now, drag the
**Fill Handle**icon to fill the other cells with the above formula. - Therefore, you will get the following serial number.

- Now, we are going to filter the data to show the only column serial number in the dataset.
- To do this, we have to select the range of cells and press
**Ctrl+Shift+L**.

- This will instantly add a filter to the data as shown below.

- Now, click on the
**Math**column drop-down icon, select**Number Filters**, and finally select**Greater Than**option.

- When the
**Custom AutoFilter**window appears, type your required number in the**is greater than**box. - Then, click on
**OK**.

- You will therefore get the following column when you filter the
**Math**column data based on a score greater than 80. The serial number is automatically generated when the column is reduced according to the criteria.

** How Does the Formula Work? **

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

**IF(C5=””,””,SUBTOTAL(3,$C$5:C5))**

If **Cell C5** is empty, the **IF** function will return blank, otherwise, it will return the **SUBTOTAL** function value.

**Read More: ****How to Create a Number Sequence with Formula in Excel**

### 3. Applying MAX and SUBTOTAL Functions

Here, we will use another effective way to create serial numbers by applying **SUBTOTAL **and **MAX** functions. The following dataset contains Student’s name and their corresponding exam score. Let’s walk through the following steps to create serial numbers.

**📌 Steps:**

- In the beginning, select
**Cell B5**and type the formula below.

`=MAX(SUBTOTAL(3,D$5:D5))`

- Next, press
**Enter**.

- Now, drag the
**Fill Handle**icon to fill the other cells with the above formula. - Therefore, you will get the following serial number.

- We can see that no new serial is created for the cells where there are no corresponding values for the related cells if we drag the
**Fill Handle**icon into the next few cells.

- Now, we are going to filter the data to show the only column serial number in the dataset.
- To do this, we have to select the range of cells and press
**Ctrl+Shift+L.**

- This will instantly add a filter to the data as shown below.

- Now, click on the
**Math**column drop-down icon, select**Number Filters**, and finally select**Greater Than**option.

- When the
**Custom AutoFilter**window appears, type your required number in the**is greater than**box. - Then, click on
**OK**.

- You will therefore get the following column when you filter the
**Math**column data based on a score greater than 70. The serial number is automatically generated when the column is reduced according to the criteria.

**How Does the Formula Work? **

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

**MAX(SUBTOTAL(3,D$5:D5))**

The **MAX** function will return the largest value in the list of arguments.

**Read More: ****How to Add Numbers 1 2 3 in Excel (2 Suitable Cases)**

## 💬 Things to Remember

When you are creating serial numbers using the subtotal formula, you need to keep certain things in mind.

✎ You should keep in mind that excel doesn’t consider hidden values the same as the filtered-out values. So, when you hide any rows the serial will not generate automatically.

✎ When using multiple functions in one formula, you need to put the parenthesis carefully, otherwise, the formula will not be executed.

## Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to create serial number by using the subtotal formula in Excel. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website **Exceldemy.com** for various Excel-related problems and solutions. Keep learning new methods and keep growing!