Subtotal Formula in Excel for Serial Number (3 Suitable Examples)

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.

Subtotal Formula for Serial Number in Excel: 3 Suitable Examples

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

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

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

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.

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

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

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.

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.