6 Ways to Subtotal in Microsoft Excel

Learn how to subtotal in Excel using simple methods ranging from manual Excel functions to Excel VBA-based automation.

When you analyze a large dataset in Excel, you might want to group relevant data and create intermediate totals before the grand total. This is commonly known as subtotal. It’s one of the best ways to break down a larger dataset into smaller sections.

In Microsoft Excel, a subtotal analysis might include different statistical functions, like average, sum, counts, max, min, standard deviation, and more.

Follow along with this Excel tutorial until the end to explore various methods to use subtotal in Excel with simple steps and illustrations. Let’s get started!

Using the SUBTOTAL Function

For one-dimensional subtotaling, the SUBTOTAL function is the preferred choice for many Microsoft Excel data analytics experts. Also, this function has been available since Excel 2007.

Using this function is super easy as you simply need to use a numerical value as the primary argument, like 1 for calculating an average, followed by the first cell range reference containing the values. You can add as many cell ranges as you want, separating those with commas.

The primary argument or the function number comes in the following two sets:

FunctionIncludes hidden rowsExcludes hidden rows
AVERAGE1101
COUNT2102
COUNTA3103
MAX4104
MIN5105
PRODUCT6106
STDEV7107
STDEVP8108
SUM9109
VAR10110
VARP11111

The SUBTOTAL function is only helpful when you apply a filter to a large dataset. It automatically calculates the designated function, like average or sum for the visible cells in a filtered table. Furthermore, this function automatically excludes any nested subtotals in the source dataset.

Sample dataset 1

Let’s use the SUBTOTAL function in the above dataset to get the sum of revenue for different product categories.

SUBTOTAL function syntax
SUBTOTAL function syntax

Go to the cell where you want the subtotal value. Enter the following formula and hit Enter:

=SUBTOTAL(9,F2:F10)

In the above formula, the number 9 is for calculating a SUM in the SUBTOTAL function. You can change it according to the statistical calculation you want the formula to perform by referring to the above table.

Subtotal for the whole data
Subtotal for the whole data

The SUM you get so far is for the entire dataset, from F2 to F10.

Activate Sort & Filter
Activate Sort & Filter

Let’s say, you want to find out the revenue for the Smartphone category in the above dataset.

Click on any of the column headers and press Ctrl + Shift + L to activate the Sort & Filter function.

Apply a filter
Apply a filter

Click on the Category filter drop-down arrow and uncheck all categories except for the Smartphone option.

Add subtotals in Excel using a function
Add subtotals in Excel using a function

As soon as you apply the Smartphone filter, the SUBTOTAL function recalculates to show the revenue only for the visible cells.

📒 Read More: 6 Ways to Add a Total Row to a Table in Microsoft Excel

Using the AGGREGATE Function

The AGGREGATE function is more robust than the SUBTOTAL function. It has 19 embedded functions including advanced ones, like PERCENTILE.INC, MEDIAN, QUARTILE.INC, and more. Also, there’s a dedicated argument for considering or ignoring variables, like hidden rows, nested subtotals, nested aggregates, error values, etc.

The only drawback is this function was made available in Excel 2010. So, if you’re using an earlier version, you won’t be able to use this method.

Sample dataset 2

Let’s use the AGGREGATE function to find the average subtotal for the Units Sold column against the Smartphone category in the above dataset. Find below the quick steps:

Applying an AGGREGATE function
Applying an AGGREGATE function

Go to the cell where you want the average subtotal. Type in the following formula and hit Enter:

=AGGREGATE(1,3,E2:E10)

In the above formula, 1 is for applying the AVERAGE function to the selected array and 3 indicates that you want to ignore existing aggregates, subtotals, hidden rows, and errors.

You can change this value according to your worksheet scenario. The formula argument editor drop-down menu will show detailed instructions for all the arguments of this function.

Aggregate average for entire column
Aggregate average for entire column

The result calculated by the AGGREGATE function is an average for the entire column.

Applying a filter
Applying a filter

To narrow down the average value for the Smartphone category, click on any of the column headers and press Ctrl + Shift + L.

Use smartphone filter
Use smartphone filter

Click on the filter drop-down menu for the Category column. Use the context menu to exclude all categories except the Smartphone option.

Insert subtotals in Excel using AGGREGATE
Insert subtotals in Excel using AGGREGATE

Excel shows the subtotal average instantly as you apply a filter.

Subtotal Tool in Data Menu

The Subtotal tool lets you add subtotals in Excel more intuitively than the manual functions. Also, the final dataset is more detailed compared to the previous methods.

For example, the audience can relate to a subtotal of revenue or sales volume with product categories or stores in the same dataset.

Moreover, you no longer need to apply a filter to make the subtotal value obvious. Excel will automatically create different segments for different categories, add subtotals, and at the end add a grand total as well.

Sample dataset 3

Before you can apply this tool to a dataset, you must organize the input data appropriately. The above example isn’t good data to begin with. You must restructure it to group similar items together.

Disorganized columns
Disorganized columns

For instance, you can’t effectively use the Subtotal feature for the above dataset since the items in the Store or Category columns are random. You can but the end result won’t be much useful.

So, you must use the Sort & Filtering tool to sort the dataset by a specific column. In the current exercise, let’s select the category column.

Enable the Sort function
Enable the Sort function

Click on any of the column headers and press Ctrl + Shift + L keys together to activate the Sort & Filtering tool.

Sort a column
Sort a column

Apply the Sort A to Z function for the Category column by clicking on the filter drop-down arrow.

Now, press the Sort & Filter shortcut keys again to deactivate the function.

Sorted Category column
Sorted Category column

The above dataset is the perfect candidate for applying the Subtotal feature.

Select Subtotal
Select Subtotal

Now, select the entire dataset and click on the Subtotal command inside the Outline block of the Data tab.

Configuring Subtotal
Configuring Subtotal

The Subtotal dialog box will pop up. This is the interface you need to use to configure the subtotaling action.

All the options in the dialog box are according to the column headers. For example, if you wish to get a subtotal of revenue by each category of products, select Category for the At each change in the drop-down menu.

Click on the Use function drop-down menu and choose a function. For example, the SUM function.

Checkmark the checkbox for the column header by which you want to segregate the subtotals. Since we need a subtotal SUM by sales value, select the Revenue column header.

Insert subtotals in Excel using Data tools
Insert subtotals in Excel using Data tools

Click OK to create the subtotal view.

The only drawback of this method is the Subtotal tool in the Excel Data tab is only available from Excel 2013 and later editions.

Using the PivotTable Tool

The PivotTable tool is another effortless way to add subtotals in Excel. It also allows you to create different views of subtotals using various columns of the input dataset.

This is the most widely used method for calculating subtotals using SUM, AVERAGE, MAX, MIN, etc., for medium to large datasets because it’s available since Excel 2007 edition.

Sample dataset 4

Let me show you how to use this method in your own worksheet for adding subtotals. As an example, you can use a dataset that resembles the above example.

Use PivotTable command
Use PivotTable command

Select the entire input dataset by pressing Ctrl + A on the source worksheet.

Click the PivotTable command inside the Tables block of the Insert tab. The PivotTable from table or range dialog will show up.

Existing worksheet
Existing worksheet

Select the Existing worksheet option in the dialog box and pick a cell where you want the PivotTable.

PivotTable Fields panel
PivotTable Fields panel

Click OK to generate the PivotTable scaffold and bring up the PivotTable Fields navigation panel on the right side.

Subtotal using a PivotTable
Subtotal using a PivotTable

Now, drag the field you want to group for subtotaling into the Rows field. In the current exercise, it’s the Category column.

Move the column that has the numerical inputs for subtotal calculation using the SUM function. For instance, the Revenue column in this context.

You’ve successfully created a subtotal in Excel using the PivotTable tool.

Update PivotTable with more data
Update PivotTable with more data

Now, you might want to give the table more context by adding more data. For the sample dataset, you can add the Gadget column into the Rows field to show how different products contribute to the revenue for various product segments.

Subtotals by Units Sold
Subtotals by Units Sold

Let’s say, you want to create a subtotal for Units Sold against the Category column. Remove the Revenue column from the Values field. Now, drag and drop Units Sold into Values. Excel will automatically update the PivotTable with the relevant subtotals.

Value Fields Settings
Value Fields Settings

You might also want to explore other subtotal functions to analyze your dataset. To switch to a different subtotal function, click on any row in the subtotal column of the PivotTable. In this exercise, it’s the Sum of Units Sold.

Right-click and select the Value Field Settings option from the context menu.

Switching to AVERAGE from SUM
Switching to AVERAGE from SUM

In the Value Field Settings dialog box, select a different subtotal function, like AVERAGE from the Summarize Values By column.

Subtotal in Excel using AVERAGE
Subtotal in Excel using AVERAGE

Click OK to apply the modification and update the existing PivotTable.

Using the Power Query Tool

If you need to extract subtotals from a large dataset that wouldn’t even fit in an Excel worksheet, you must use Power Query. It allows you to load unlimited rows and columns temporarily from an external database server for data transformation and extract concise data into an Excel worksheet.

Power Query is available as an Excel add-in in Excel 2010 and 2013 and as a built-in tool in Excel 2016 and newer editions.

From Oracle Databases
From Oracle Databases

Firstly, you need to import your dataset to the Power Query Editor tool. If you’re doing so from an external database, go to the Data tab and click on the Get Data command.

You’ll find various options, like From Database, From Azure, From Online Services, etc. Hover the mouse cursor on the suitable option to find more choices. For example, the From Database option allows you to import datasets from SQL server databases, from Oracle databases, and more.

From Table Range
From Table Range

For this exercise, I’ve imported an example dataset from an Excel worksheet by using the From Table/Range command from the Data tab.

Create table
Create table

When you select the input dataset and click on the From Table/Range command, the Create Table dialog will show up. Click OK.

Dataset in Power Query
Dataset in Power Query

Excel will load your data to the Power Query Editor tool.

Group By Sum
Group By Sum

Now, click Group By and configure the dialog box that shows up as explained below:

  • Select the Basic Group By option.
  • Click on the first drop-down menu to select against which column you want to group and subtotal. Here, it’s the Category column.
  • Into the New column name field, enter a name for the new column, like Subtotal.
  • Click on the Operation drop-down menu and select a function, like Sum.
  • Finally, click the Column drop-down menu and select the column on which you want to perform the SUM, like the Revenue column.
Subtotals in Excel using Power Query
Subtotals in Excel using Power Query

Click OK to apply the changes and Power Editor will create a new dataset only showing the subtotal values.

Group By Store and Revenue
Group By Store and Revenue

To get back your input dataset, simply click the cross icon to the left of the Grouped Rows action in the Applied Steps menu on the right side of the Power Query Editor interface.

Delete grouped rows
Delete grouped rows

To change the dimension for subtotals, like assessing a store-wise performance of revenue, in this sample dataset, you simply need to select the Store column in the first column-selection drop-down menu.

Subtotal by Store
Subtotal by Store

As soon as you apply the rest of the conditions, Power Query Editor will quickly update your subtotals.

Close and load to
Close and load to

Now, to import the subtotal values into your Excel worksheet, click the File tab and select the Close & Load To option.

Import data dialog
Import data dialog

The Import Data dialog box will show up. Click on the Existing worksheet option and select a cell where you want to put the subtotals table.

How to add subtotal in excel using Power Query
How to add subtotals in Excel using Power Query

Click OK to export data from Power Query to Excel.

Using Excel VBA

If you’re looking to automate the subtotal creation process from sorting the input dataset to the Subtotal tool configuration, you should try this Excel VBA-based approach.

The script I’ve shared below will walk you through the whole process with visual dialog boxes. All you need to do is enter the column headers by which you want to create the subtotals table.

Before diving deep into the script and macro, go through this Excel tutorial to find out how to set up a macro using a VBA script:

📒 Read More: How To Use The VBA Code You Find Online

Now, use the following VBA script to create a VBA macro that’ll add subtotals in Excel:

VBA script 1
Sub CreateSubtotals()
    Dim ws As Worksheet
    Dim rng As Range
    Dim sortColumn As String
    Dim subtotalColumn As String
    Dim functionType As String
    Dim addSubtotalTo As String
    Dim func As XlConsolidationFunction
    Dim sortColumnIndex As Integer
    Dim subtotalColumnIndex As Integer
    Dim addSubtotalToIndex As Integer
    
    ' Select the input dataset
    On Error Resume Next
    Set rng = Application.InputBox("Select the input dataset:", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    
    ' Enter the column header to sort by
    sortColumn = Application.InputBox("Enter the column header to sort by (A to Z):", Type:=2)
    If sortColumn = "" Then Exit Sub
    
    ' Enter the column header for 'At each change in'
    subtotalColumn = Application.InputBox("Enter the column header for 'At each change in':", Type:=2)
    If subtotalColumn = "" Then Exit Sub
    
    ' Enter the function type
    functionType = Application.InputBox("Enter the function type (Sum, Count, Average, Max, Min, Product):", Type:=2)
    If functionType = "" Then Exit Sub
    
    ' Map function type to XlConsolidationFunction
    Select Case LCase(functionType)
        Case "sum": func = xlSum
        Case "count": func = xlCount
        Case "average": func = xlAverage
        Case "max": func = xlMax
        Case "min": func = xlMin
        Case "product": func = xlProduct
        Case Else: MsgBox "Invalid function type": Exit Sub
    End Select
    
    ' Enter the column header for 'Add subtotal to'
    addSubtotalTo = Application.InputBox("Enter the column header for 'Add subtotal to':", Type:=2)
    If addSubtotalTo = "" Then Exit Sub
    
    ' Find the column indexes
    sortColumnIndex = Application.WorksheetFunction.Match(sortColumn, rng.Rows(1), 0)
    subtotalColumnIndex = Application.WorksheetFunction.Match(subtotalColumn, rng.Rows(1), 0)
    addSubtotalToIndex = Application.WorksheetFunction.Match(addSubtotalTo, rng.Rows(1), 0)
    
    ' Sort the dataset
    rng.Sort Key1:=rng.Columns(sortColumnIndex), Order1:=xlAscending, Header:=xlYes
    
    ' Add subtotals
    rng.Subtotal GroupBy:=subtotalColumnIndex, _
                 Function:=func, _
                 TotalList:=Array(addSubtotalToIndex), _
                 Replace:=True, _
                 PageBreaks:=False, _
                 SummaryBelowData:=True
    
    ' Show completion message
    MsgBox "Subtotals created successfully!"
End Sub
Macro dialog
Macro dialog

Press Alt + F8 to bring up the Macro dialog box. There, select the CreateSubtotals macro and hit Run.

Excel VBA will show the following dialog boxes in cascade so you can enter relevant data:

Input cell range
Input cell range
  • Select the input dataset.
Sorting conditions
Sorting conditions
  • Enter the column header to sort by (A to Z).
At each change in
At each change in
  • Enter the column header text for the ‘At each change in‘ field.
Function VBA for subtotal
Function VBA for subtotal
  • Enter the function type for subtotaling.
VBA Add subtotal to
VBA Add subtotal to
  • Enter the column header for the ‘Add subtotal to‘ field.
Added subtotal in Excel using VBA
Added subtotal in Excel using VBA

Find above a subtotal table created by running this VBA macro.

Conclusions

You’ve, so far, explored how to subtotal in Excel using various Excel functions, and user interface tools. You’ve also learned the approaches for adding subtotals using advanced Excel tools, like PivotTable, Power Query, and Excel VBA.

Use the comment box below to share your feedback or suggestions about this Excel tutorial, if any. If you know a better and easier method than the ones I’ve outlined above, do mention that in your comment.

About the Author

Bipasha Nath

Bipasha Nath

Bipasha is a technical content writer with 10+ years of experience in the technology industry. She previously worked in a SaaS software development company focused on SharePoint, OneDrive, Word, Excel, Word solutions.

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃