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.
The good news is that it’s easy to add subtotals to any data list to 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.
| Function | Include Hidden Rows | Ignore Hidden Rows |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
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.
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.
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.
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.
- Sort the list by Dept.
- Click in the range of cells containing the data list.
- Click the Subtotal button in the Data > Outline group.
The Subtotal dialog box appears. - Configure the Subtotal dialog box using the settings from the screenshot below and click OK.
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.
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!
