How to Insert Subtotals in Excel (7 Unique Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to insert subtotals in Excel


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.

Sort sample data

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

Sort by Sales Reps in dialog box

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

Subtotal feature in 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 SUM function in the Use function field as we want to calculate the total value.
  • Click OK.

Excel Subtotal dialog box

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

How to insert subtotals in Excel automatically

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

Sort by Sales Reps and Product Category

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

Insert subtotals in Sales Reps column

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

Insert subtotals in Category column

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

Insert multiple subtotals in different columns 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.

Subtotals in first column

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

Subtotals for average and standard deviation

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

Insert multiple subtotals in same column


3. Insert Subtotals in Excel Table

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

15-Subtotal feature not working in Excel table

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.

Convert Excel table to range

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

Confirmation for converting to range

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

Excel table converted to range


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.

Different argument of SUBTOTAL function


4.1. Subtotals in Filtered Out Rows

We have applied the following SUBTOTAL formula to insert subtotals.

=SUBTOTAL(9,H5:H18)

SUBTOTAL function inserts subtotals

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.

Filter row in Excel

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.

SUBTOTAL function ignoring filtered rows

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.

Manually hide rows

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.

Subtotal function for manually hidden rows


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

VBA code to insert subtotals

  • Click the Run button to execute the VBA code.

Run the code

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

VBA code inserted subtotals in Excel


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

  1. Columns: Blank
  2. Rows: Category, Product
  3. Values: Quantity, Unit Price, Total Price, and Profit

Excel Pivot Table

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

Subtotals option in Pivot Table Design

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

Show all subtotals at bottom of each group

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.

How to insert subtotals in Excel pivot 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.

From Table or Range from data

  • Click OK on the Create Table dialog box.

Create Table

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

Group by columns in Power Query editor

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

Add aggregation in Group By command box

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

Two group formed in Group By command box

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

Subtotals for group in Power Query editor

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

Power Query inserts Subtotals


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.

How to remove subtotals in Excel

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

Subtotals removed from grouped data

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.

<< Go Back To Subtotal in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo