How to Calculate Subtotals in Excel? (A Complete Guide)

In this Excel tutorial, you’ll learn how to insert subtotals in a list of data in a worksheet by using Excel’s built-in Subtotal feature and the SUBTOTAL function. We’ll also discuss how to add, remove, and calculate subtotals in Excel. By the end of this article, you will have a solid understanding of how to use subtotals in Excel for further data analysis and summary purposes.

We used Microsoft 365 for this article. However, all of the functions and features used in this article are available in all versions of Excel.

Subtotals in Excel offer many useful applications, especially when dealing with large datasets. They are excellent at summarizing and evaluating data. You can create reports, and calculate totals, averages, and counts for various groups within your data. For example, you can use subtotals to understand sales across locations and assess costs for different categories in a list. Overall, subtotals simplify data analysis and provide valuable insights.

Download Practice Workbook

How to Calculate Subtotals in Excel

There are 3 simple ways to calculate subtotals in Excel.

1. Using Subtotal Feature to Calculate Subtotal

The Subtotal feature in Excel calculates results for both individual subsections and the entire section, which is not achievable through other methods. We can use the SUM function in this feature to calculate the subtotals of the following dataset.

• Click on any cell of the dataset. Go to the Data tab. Under the Outline group, click on the Subtotal feature.

Click the image for a detailed view.

• It will open the Subtotal dialog box. The used function is SUM. Click OK to proceed.

• As a result, it will automatically calculate the subtotal for the Sales column of the dataset.

All Options of the Subtotal Feature in Excel

The Subtotal feature in Excel offers the following options:

• At each change in: In the At each change in box, choose the column to group by.
• Use function: In the Use function box, select one of these functions to calculate subtotal:
  Sum: Adds up numbers.
Count: Counts non-empty cells using the COUNTA function.
Average: Calculates the average.
Max: Finds the largest value.
Min: Finds the smallest value.
Product: Calculates the product.
Count Numbers: Count cells with numbers using the COUNT function.
StdDev: Calculates population standard deviation from a sample.
StdDevp: Calculates standard deviation for an entire population.
Var: Estimates variance from a sample.
Varp: Estimates variance for an entire population.
• Add subtotal to: Under the Add subtotal to box, check the boxes of columns that you want to subtotal.
• Replace current subtotals: Check this box to remove any existing subtotals.
• Page break between groups: Check this box to insert automatic page breaks after each subtotal.
• Summary below data: If you want the summary row to appear above the details row, uncheck the Summary below data box. Or, check this box to get the summary row below the details row.

2. Applying SUBTOTAL Function to Calculate Subtotal

The SUBTOTAL function in Excel calculates subtotals for a specific range of data, and it takes into account filters or grouping. While this function can perform multiple calculations, we will only focus on Sum (function number: 9) to calculate subtotals. The function also only works for the visible rows to ensure that subtotals are accurate, even when you have applied filters.

• Choose the cell in which you wish to see the subtotal.
• Next, enter the following formula:

• Press Enter to calculate the subtotal.

Click the image for a detailed view.

3. Using SUM Function to Calculate Subtotal

The SUM function in Excel calculates the total of a range of numbers. It sums all the values in the specified range, whether they are hidden or visible. Here, we calculated the sum of sales for each subproduct. Then, to determine the total sales for all products, we took the entire range of sales and divided it by 2 since the cells for the sum of subproducts were already included in the range.

• Select a cell in which you wish to display the subtotal.
• Type the formula below:

• Press Enter to calculate the subtotal of Product A.

Click the image for a detailed view.

How to Add Multiple Subtotals in Excel

Here, we’ll look at how to add subtotals for multiple columns in the dataset. First, we will add a subtotal for the Product Items column, and then we’ll add a subtotal for the States column.

• Select a cell in the dataset.
• Next, click as follows: Data >> Outline >> Subtotal.

Click the image for a detailed view.

• It will then display the Subtotal dialog box. Specify the columns to subtotal, the function to use for subtotals, and other parameters in this dialog box.
• From the At each change in the drop-down menu, select the column that contains the criteria for the first level of grouping.
• In the Use function drop-down menu, select the desired function for the subtotals at the first level.
• From the Add subtotal to list box, select the box that you want to include for subtotals for each column.
• Click OK to apply the subtotals.

• It will generate the subtotals for the Product Items. Now, again go to the Data tab and select the Subtotal option under the Outline group.

Click the image for a detailed view.

• It will open the Subtotal dialog box again.
• In the At each change in drop-down menu, select the column that contains the criteria for the second level of grouping.
• Specify the function to use for subtotals and check the box for each column that you want to include for subtotals.
• Make sure to uncheck the Replace current subtotals option. If not, it will eliminate the preceding subtotal and produce a single subtotal. Click OK.

• Thus, you will get the subtotals as expected. You can now group multiple items.

How to Insert Subtotals in a List of Data in Excel?

To insert subtotals in a list of data, we can apply the Subtotal feature in Excel that enables you to calculate subtotals for both individual subsections and the entire section.

• To insert subtotals in a list of data, select a cell on the dataset. Next, go to the Data tab.
• Under the Outline section, select Subtotal.

Click the image for a detailed view.

• The Subtotal dialog box will then appear.
• Choose the column that provides the requirements for subtotals from the At each change in the drop-down menu.
• Choose the desired function for the subtotals from the drop-down option under the Use function.
• For each column, you wish to add subtotals, check the box in the Add subtotal to list box. Click OK.

• As a result, Excel will automatically insert the subtotals for the dataset.

How to Use SUBTOTAL Function in Excel?

When working with a range of data in Excel, the SUBTOTAL function allows you to do computations while ignoring any other SUBTOTAL functions that are also present in the range. There are 3 ways to use the SUBTOTAL function in Excel.

1. Calculating Subtotal for Filtered-out Rows

When calculating subtotals, we can filter out rows. The SUBTOTAL function can automatically adjust to the filtered-out rows and display results for the visible rows.

Click the image for a detailed view.

2. Calculating Subtotal for Manually Hidden Rows

We can also manually hide rows by selecting the rows, right-clicking on them, and choosing the Hide option. However, by hiding rows manually, you can’t exclude the cells from the SUBTOTAL function.

Click the image for a detailed view.

This produces a wrong result by calculating all the cells in the range. Now, to correct this, we can modify the SUBTOTAL function. Use the function number 101-111 in the SUBTOTAL function to calculate only visible cells excluding all manually hidden rows.

Click the image for a detailed view.

3. Calculating Average Using SUBTOTAL Function

To calculate the average, type the following formula:

Click the image for a detailed view.

How to Remove Subtotals in Excel

You can remove subtotals from the Subtotal feature to get the original dataset back. Follow the steps below to remove subtotals in Excel:

• To remove subtotals in Excel, first, select a cell in the dataset.
• Click as follows: Data >> Outline >> Subtotal.

Click the image for a detailed view.

• A Subtotal dialog box will appear as a result.
• Click on the Remove All button to remove all the subtotals from the dataset.

• As a result, it will remove all the subtotals from the dataset.

Read More: Remove Subtotals

Things to Remember

• The SUBTOTAL function is set by a number in the first part called function_num.
• There are two sets of numbers: 1 to 11 and 101 to 111.
• The first set (1-11) doesn’t count cells hidden by filters but counts cells hidden by you.
• The second set (101-111) ignores all hidden rows, both filtered and manually hidden.
• Excel’s Subtotal feature uses function numbers 1 to 11.

Frequently Asked Questions

1. What is Subtotal 9 in Excel?

Answer: The number 9 in the argument of the SUBTOTAL function stands for the SUM function. It calculates the sum of a range of values when using the Subtotal feature.

2. How many Subtotals are in Excel?

Answer: Excel allows you to add multiple subtotals based on the grouping criteria you choose. There is no fixed limit to the number of subtotals you can have.

3. How do I show all Subtotals in Excel?

Answer: To show all subtotals in Excel, you can expand or unhide the grouped data by clicking the small buttons with the minus (-) and plus (+) signs next to the row numbers.

4. Why use SUBTOTAL instead of SUM?

Answer: Subtotal is used when we want to calculate subtotals within a range while ignoring other subtotals. It can include or exclude hidden or filtered cells. The SUM function does not have any of these features.

Conclusion

In conclusion, subtotals in Excel are useful for organizing and analyzing large data sets. Throughout this article, you have learned about the different features of subtotals, such as their insertion, modification, and removal. By using these methods, you can perform data analysis more easily, find patterns and trends, and gain valuable information from your data.

When calculating subtotals using the SUBTOTAL function, you have options. You can use function_num 1 to 11 to count visible cells hidden by filters. Alternatively, you can use function_num 101 to 111 to include cells hidden by filters or hidden manually.

Another choice is to use Excel’s Subtotal feature, which employs the 1 to 11 function numbers when calculating subtotals.

If you have any further queries, feel free to ask in the comment section below or on our forum. Thank you for reading this article!

Subtotals in Excel: Knowledge Hub

• Insert Subtotals
• Make Subtotal and Grand Total
• Use SUBTOTAL with Filters
• Sort Subtotals
• VBA Code for Subtotal

<< Go Back to Learn Excel