Master Excel Subtotals: 7 Proven Techniques for Accurate Data Summaries

Method 1 – Insert Subtotals in Excel Automatically

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

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

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

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

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

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

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.

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

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

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

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:

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

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

4.1. Subtotals in Filtered Out Rows

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

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.

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.

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

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

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

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

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

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

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