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

**Table of Contents**Expand

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

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

`=SUBTOTAL(9,F5:F16)`

- Press
**Enter**to calculate the subtotal.

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

`=SUM(F5:F16)/2`

- Press
**Enter**to calculate the subtotal of**Product A**.

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

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

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

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

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

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.

### 3. Calculating Average Using SUBTOTAL Function

To calculate the average, type the following formula:

`=SUBTOTAL(1,F5:F16)`

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

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