When dealing with extensive datasets, it becomes crucial to summarize and organize information effectively. This is where the power of subtotals comes into play. This tutorial will give you a complete overview of how to insert subtotals in Excel with proper illustrations.

Subtotals allow you to break down data into manageable groups, calculate aggregate values, and gain a deeper understanding of your information. Today we’ll try to cover everything you need to know, from preparing your data to customizing subtotals based on your requirements.

## Basics of Subtotals

In generic terms, subtotal refers to the total of one set of a larger group of collections. For example, suppose you got 100 marks in your last semester, where the marks of the math course were obtained from the three class tests that you had. In the first class test, you got 10, in the second you got 15, and in the last class test, you got 20. So now you want to know only your math score out of 100 marks. To get that quickly, you can use subtotal.

Subtotals in Excel are a way to calculate summary statistics for groups or subsets of data within a larger dataset. They allow you to create subtotals based on a specified column or columns and perform calculations such as **SUM**, **AVERAGE**, **MAX**, **MIN**, **COUNT**, *Product,* etc.

## How to Insert Subtotals in Excel: 7 Unique Methods

This section will cover 7 unique approaches to grouping your data and adding subtotals to it. With real-life and practical datasets, we will try to create a visualization of how to insert subtotals in Excel worksheet.

### 1. Insert Subtotals in Excel Automatically

Suppose we have a dataset of seller information where sales reps sold different categories of products. The dataset includes the quantity of sold items, unit price, total price, and profit gained from selling those products. Using this dataset, we will find subtotals and grand totals in Excel.

We will use the **Subtotal **feature of Excel to insert subtotals automatically. For this:

- Select a random cell > go to the
**Data**tab > click**Sort**.

- The
**Sort**dialog box will pop up on the window. - In the
**Sort by**field, select the column on the basis of which you want to insert subtotals. - Keep
*Cell Values*in the**Sort On**field. - Order will be ascending:
**A to Z**. - Click
**OK**.

- This will sort the
*Sales Reps*column alphabetically.

Now we will use the built-in feature which is also known as the subtotal shortcut in Excel

- Again click on a cell in the range > select
**Subtotal**under**Outline**group from the**Data**tab.

- The
**Subtotal**dialog box will appear now. We will insert subtotals in Excel using the**SUM**function. **At each change in**field should hold the data on which you will insert subtotals.- Put the
function in the**SUM****Use function**field as we want to calculate the total value. - Click
**OK**.

Now you will be able to insert subtotals in Excel. The *sales Reps *column will hold *subtotals and grand totals*.

- The icon at the top left corner with numbers 1, 2, and 3 will command you how to use subtotals in Excel. It allows handling the groups and showing and hiding subtotals in Excel.

**Read More: **How to Sort Subtotals in Excel

### 2. Add Multiple Subtotals

The previous method allows you to add a single subtotal. However, the **subtotal **feature also allows you to add multiple subtotals in Excel. You can insert multiple subtotals both in the same column or in different columns.

#### 2.1. Insert Multiple Subtotals in Different Columns

In the previous section, we have already shown how we can apply subtotal upon the *Sales Reps* column. So, in this section, we will add another subtotal based on *Category*.

- Open the
**Sort**dialog box from the**Data**tab. - First Sort by
*Sales Reps*. - Add another level.
- Firstly we have sorted by
*Sales Reps,*now we will sort by*Category*. - Click
**OK**.

- Open the
**Subtotal**dialog box from the**Data**tab as described in*Method-1*. - Choose
*Sales Reps*in the**At each change in**field.

- Excel will insert Subtotals to the
*Sales Reps*column. - Again open the
**Subtotal**pop-up. - This time ensures each change in
*Category*. - Make sure the
**Replace Current Subtotals**field is unmarked. If not, it will clear the previous subtotal and result in a single subtotal. - Click
**OK**.

Now we will be able to group multiple times and insert subtotal in Excel.

Select another change in the **Subtotal **box if you want to add subtotals further.

#### 2.2. Find Multiple Subtotals in Same Column

Now, we will insert multiple subtotals in the same column. We will calculate the *average and standard deviation* after calculating the total. So, you will need two more functions. First, we will choose the **AVERAGE** function and then the** StdDev** function.

We will start from after inserting subtotals to *Sales Reps *(gained from *Method-1*) using the **SUM **function.

- After that, open the
**Subtotal**box 2 times more. - First time, choose the
**AVERAGE**function and second time select the**StdDev**function.

- The
**AVERAGE**function will find out the average of the selected columns. - The
**StdDev**function will calculate the standard deviation.

Based on each of these functions, subtotals will be inserted into your data.

### 3. Insert Subtotals in Excel Table

You can’t apply subtotals directly in the Excel table. The **Subtotal **feature won’t work here.

If you want to insert subtotals, you have to convert the table to range first.

- Select a random cell in your table and go to the
**Table Design**tab > select**Convert to Range**under the**Tools**group.

- Microsoft Excel will ask you whether you want to convert the table to a range. Confirm it by clicking
**Yes**.

Excel will now convert the table to a normal range. You can now apply the **subtotal **feature to insert subtotals.

### 4. Apply SUBTOTAL formula in Excel with Filter

Till now we have used the **Subtotal **feature. Excel also has **the SUBTOTAL function** available to add subtotals to your data. We will use the function for *filtered data*.

The syntax of the **SUBTOTAL** function:

`SUBTOTAL(function_num,ref1,[ref2],…)`

Here, the first argument: **function_num** is described by some numbers (**1-9** and **101-109**) denoting different functions.

Argument |
Functionality |
---|---|

1-9 |
Ignores the filtered rows but includes manually hidden rows. |

101-109 |
Ignores both filtered and manually hidden rows. |

#### 4.1. Subtotals in Filtered Out Rows

We have applied the following **SUBTOTAL **formula to insert subtotals.

`=SUBTOTAL(9,H5:H18)`

As we used the first argument: 9, it will ignore the filtered cell

- Click the drop-down arrow at the
*Sales Reps*column and choose a name (i.e.*Adam*) to filter by that name.

You will observe the function to calculate subtotals only for the filtered cells, it will ignore the rest.

The image below demonstrates the output of the **SUBTOTAL** function for both arguments **9 **and **109**.

**Read more: **How to Use SUBTOTAL in Excel with Filters

#### 4.2. Subtotals in Manually Hidden Rows

If you want to see the output of the **SUBTOTAL** function for manually hidden rows, then hide rows by right-clicking on the row number and choosing **Hide **from the context menu.

Then compare the output for arguments **9 **and **109**. Argument **9 **will include the cells of manually hidden rows but argument **109 **will ignore them.

### 5. Find Subtotals with VBA Code

You can customize a **VBA** code for your dataset and insert subtotals to your data by using **VBA** macro. You will need to launch the **VBA** editor for that. Let’s follow the instructions to use the VBA code for subtotal in Excel!

- Press
**ALT+F11**to launch the*Visual Basic Editor*window. - Click
**Insert**on the Toolbar > select**Module**.

- Insert your VBA code on the
**Module**window.

**Code**:

```
Sub Calculate_Subtotal()
Dim iColumn As Integer
Dim iValue As Integer
Dim xValue As Integer
Application.ScreenUpdating = False
iValue = 5
xValue = iValue
Range("B5").CurrentRegion.Offset(1).Sort Range("B6"), 1
Do While Range("B" & iValue) <> ""
If Range("B" & iValue) <> Range("B" & (iValue + 1)) Then
Rows(iValue + 1).Insert
Range("B" & (iValue + 1)) = "Subtotal " & Range("B" & iValue).Value
For iColumn = 7 To 8 'Columns to Calculate Sum
Cells(iValue + 1, iColumn).Formula = "=SUM(R" & xValue & "C:R" & iValue & "C)"
Next iColumn
Range(Cells(iValue + 1, 1), Cells(iValue + 1, 8)).Font.Bold = True
iValue = iValue + 2
xValue = iValue
Else
iValue = iValue + 1
End If
Loop
Application.ScreenUpdating = True
End Sub
```

- Click the
**Run**button to execute the**VBA**code.

Excel will now insert subtotals in your data based on *Sales Reps*.

### 6. Add Subtotals in Excel Pivot Table

Let’s say we have a **Pivot Table** in our Excel sheet and we want to add subtotals to the grouped data in the Pivot Table. A **Pivot Table** is used to categorize large datasets according to criteria.

There are four fields in the Pivot table and we have **formatted pivot table** using our data in those fields:

1. **Filter**: Sales Reps

**Columns**: Blank**Rows**: Category, Product**Values**: Quantity, Unit Price, Total Price, and Profit

- Go to the
**Design**tab > click the dropdown of**Subtotals**option under**Layout**group.

- Select
**Show all Subtotals at Bottom of Group**from the dropdown menu.

The pivot table will now insert subtotals at the bottom of each group based on ** Row Labels**. Excel will also add the grand total of the group at the bottom of the table.

### 7. Use Power Query to Insert Subtotals

Power Query is a powerful data transformation and data preparation tool that is part of Microsoft Excel and Power BI. It allows you to import, transform, and combine data from various sources into a format that is suitable for analysis and reporting.

In this section, we will use a *power query to insert subtotals for groups*.

- Click a cell in the range > go to the
**Data**tab > click**From Table/Range**under**Get & Transform Data**.

- Click
**OK**on the**Create Table**dialog box.

- Excel will shift you to the
*Power Query Editor*window. - Select the columns (i.e.,
*Sales Reps*and*Category*) in the data that you want to deal with > click**Group By**icon from the**Transform**tab.

- In the
**Group By**dialog box, give a New column name. - Select the type of operation that you want to perform (i.e., SUM).
- Select the
**Column**where you want to perform the calculation (i.e., Total Price). - Click on
**Add Aggression**to group further.

- To calculate subtotals of the
*Profit*, choose*Profit*in the**Column**field and**Sum**in the**Operation**field. Give this aggression a name (i.e.,*Profit*). - Click
**OK**.

- You will see subtotals inserted based on the Sales Reps and Category.
- Go to the
**Home**tab > click dropdown of**Close & Load**> select**Close & Load**.

The **Power Query** editor will load your grouped data with subtotals to the Excel worksheet.

## How to Remove Subtotals in Excel

If want to remove subtotals from the range, you have to utilize the **Subtotal **feature again.

- Open the
**Subtotals**dialog box from the**Data**tab > click**Remove all**.

- You will get your data converted to the normal range by removing groups and subtotals from it.

**Note**: Removing subtotals leaves the data sorted, not like the original data as it was before sorting.

## Frequently Asked Questions

**1. Why Can’t I remove subtotals in Excel?**

Subtotals may not work for several reasons.

i) Protected worksheet: You will need to unprotect your sheet if it is protected with a password.

ii) Shared workbook: You may need to unshare the workbook or check with the workbook if the workbook is shared among multiple users

iii) Custom formulas or macros: If subtotals were added using custom formulas or macros, you may need to modify or delete the formulas/macros to remove the subtotals.

**2. How do I collapse or expand subtotals in Excel?**

To collapse or expand subtotals in Excel, you can use the grouped outline feature. Excel automatically adds outlining symbols that allow you to collapse or expand the subtotal groups. Clicking on the minus (-) or plus (+) symbols next to the group rows will collapse or expand the subtotals, respectively.

**3. Can I automatically update subtotals in Excel when the data changes?**

Yes, you can set Excel to automatically update the subtotals when the data changes. You can also refresh the subtotals manually by right-clicking on a subtotal row and choosing *“Refresh”* from the context menu. If it is not automatically updated, ensure your workbook *auto calculate formulas*.

**4. Is AGGREGATE the same as SUBTOTAL function in Excel?**

No, there are differences between **AGGREGATE** and** SUBTOTAL** functions although the syntax is similar.

## Key Takeaways from the Article

- The “Subtotal” feature in Excel allows you to insert subtotals for groups of data based on specified columns.
- To insert subtotals, select the range of data and go to the “Data” tab, then click on the “Subtotal” button in the “Outline” group.
- You can add multiple levels of subtotals by selecting multiple columns to subtotal by.
- Subtotals can be collapsed or expanded using the grouped outline feature, indicated by minus (-) and plus (+) symbols.
- Subtotals can be automatically updated when the data changes by using Excel’s tables or dynamic named ranges.

**Download Practice Workbook**

You can download the practice workbook from here.

**Conclusion**

So, we have learned how to insert subtotals in Excel using some convenient ways. It enables you to apply functions like Sum, Average, Count, and more to aggregate and summarize data depending on chosen columns. Subtotals can be added at different levels, and you can change how they look to suit your tastes. For improved data organization, you can collapse or enlarge subtotals using the grouped outline function. When the underlying data changes, subtotals can be updated automatically, guaranteeing precision and effectiveness. Additionally, you can use subtotals with pivot tables or charts by exporting or copying them to other programs. You may efficiently evaluate and present compiled data in your Excel worksheets by making use of these features.