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:

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.

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

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.

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

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.

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

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.

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:

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.

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

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

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

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.

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.

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.

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

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.

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

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

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.

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.

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.

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.

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

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

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.

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.

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.

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.

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

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.

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.

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

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

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

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.

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

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.

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.

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

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

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.

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:

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

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:

• Select the input dataset.

• Enter the column header to sort by (A to Z).

• Enter the column header text for the ‘At each change in‘ field.

• Enter the function type for subtotaling.

• Enter the column header for the ‘Add subtotal to‘ field.

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.