How to Insert Subtotals in Excel: 7 Unique Methods

Method 1 – Insert Subtotals in Excel Automatically

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

Use the built-in feature, also known as the subtotal shortcut in Excel.

  • 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 to calculate the total value.
  • Click OK.

Excel Subtotal dialog box

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 instruct you on how to use subtotals in Excel. It allows you to handle groups and show and hide subtotals in Excel.

Method 2 – Add Multiple Subtotals

2.1. Insert Multiple Subtotals in Different Columns

In the previous section, we showed how we can apply a subtotal to the Sales Reps column. Add another subtotal based on Category.

  • Open the Sort dialog box from the Data tab.
  • Sort by Sales Reps.
  • Add another level.
  • We 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.
  • Open the Subtotal pop-up.
  • This time ensures each change in Category.
  • 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

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

Insert multiple subtotals in the same column. After calculating the total, we will calculate the average and standard deviation. You will need two more functions. Choose the AVERAGE function and then the StdDev function.

Start after inserting subtotals to Sales Reps (gained from Method-1) using the SUM function.

Subtotals in first column

  • Open the Subtotal box 2 times more.
  • 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 these functions, subtotals will be inserted into your data.

Insert multiple subtotals in same column

Method 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 whether you want to convert the table to a range. Click Yes to confirm.

Confirmation for converting to range

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

Excel table converted to range

Method 4 – Apply SUBTOTAL formula in Excel with Filter

We used the Subtotal feature. Excel also has the SUBTOTAL function available to add subtotals to your data. Use the function for filtered data.

The syntax of the SUBTOTAL function:


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 applied the following SUBTOTAL formula to insert subtotals.


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

4.2. Subtotals in Manually Hidden Rows

See the output of the SUBTOTAL function for manually hidden rows; hide rows by right-clicking on the row number and choosing Hide from the context menu.

Manually hide rows

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

Method 5 – Find Subtotals with VBA Code

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


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
    iValue = iValue + 1
    End If
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

Method 6 – Add Subtotals in Excel Pivot Table

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. Add the grand total of the group at the bottom of the table.

How to insert subtotals in Excel pivot table

Method 7 – Use Power Query to Insert Subtotals

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

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

How to remove subtotals in Excel

  • 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 must 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 them to remove them.

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

You can use the grouped outline feature to collapse or expand subtotals in Excel. 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 update the subtotals when the data changes automatically. You can also refresh the subtotals manually by right-clicking on a subtotal row and choosing “Refresh” from the context menu. If they are not automatically updated, ensure your workbook auto-calculates 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.

<< Go Back To Subtotal in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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