Categories
Excel

How to Use the Excel SUBTOTAL Function to Analyze Data

Microsoft Excel includes a number of features that help you analyze data in a list or database. One of the quickest to ways to perform various calculations and summarize your data involves using the SUBTOTAL function in Excel.

Participants in our Microsoft Excel training are always impressed by how easy it is to add subtotals to a data list and compare various categories of data.

Syntax for the SUBTOTAL Function in Excel

The Excel SUBTOTAL function can perform various calculations including SUM, AVERAGE, COUNT, and more (see table below).

SUBTOTAL can include or exclude values in hidden rows within the range of cells containing your list.

Here is the syntax for the SUBTOTAL function.

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

function_num is the first argument. It’s a number that specifies which function to use when calculating subtotals.

ref1 is he second argument representing a group of cells or a named range you’d like to subtotal.

ref2 is an optional argument that represents a second group of cells or an additional named range.

The SUBTOTAL function can include between 2 – 254 optional references.

Numbers Representing Different Calculations

The SUBTOTAL function will perform any of 11 different calculations depending on which code you include as an argument for function_num. The codes are listed below.

FunctionInclude
Hidden Rows
Ignore
Hidden Rows
AVERAGE1101
COUNT2102
COUNTA3103
MAX4104
MIN5105
PRODUCT6106
STDEV7107
STDEVP8108
SUM9109
VAR10110
VARP11111

How to Use the SUBTOTAL Function

Let’s look at a couple of examples that use the SUBTOTAL function.

In the spreadsheet below, I clicked in cell C12 and typed =SUBTOTAL(9,C2:C11) and pressed Enter.

SUBTOTAL Function

Function_num is 9, which calculates a sum for the numbers in C2:C11. The subtotal is $802,559.

The SUBTOTAL Function and Hidden Rows

If some of the rows in the list are manually hidden, the total won’t change. Because I set function_num to 9, the SUBTOTAL function includes rows that have been hidden.

Subtotal with hidden rows

If I had filtered the list, the SUBTOTAL function would have omitted values from any filtered rows. Unlike rows that are manually hidden, SUBTOTAL always omits filtered rows regardless of whether function_num is set to 9 or 109.

Here is the spreadsheet with rows 3 – 10 hidden and function_num set to 109 (which ignores rows that are hidden).

The subtotal in this example is $125,286.

Ignore hidden rows

How to Automatically Add Subtotals to a List

The easiest way to add subtotals to an Excel list is with the Subtotal button in the Data > Outline group.

It’s a fairly straightforward process. You just need to sort the column containing any data you want to analyze.

Once again, an example might be helpful.

Let’s say I want to create a report that calculates total salaries by department, using the spreadsheet I referenced earlier.

I would do the following.

  1. Sort the list by Dept.
  2. Click in the range of cells containing the data list.
  3. Click the Subtotal button in the Data > Outline group.
    The Subtotal dialog box appears.
  4. Configure the Subtotal dialog box using the settings from the screenshot below and click OK.
Subtotal dialog box

Subtotals are added to the list, automatically summarizing salaries from each department.

Additionally, controls have been added to the worksheet that allow you to expand or collapse the list to show various levels of detail.

automatic subtotals

What’s Next?

If you’ve enjoyed learning about the SUBTOTAL function in Excel, you might want to check out XLOOKUP vs VLOOKUP: Excel Trainer Explains Key Differences. Lookup functions are incredibly useful whenever you need to combine data from multiple worksheets.

See you next time!

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.