Using Excel’s SUBTOTAL Functions

By Angela Henderson, Associate Vice Chancellor of IR, Planning, and Assessment, Keiser University

Excel’s SUBTOTAL function allows users to create subtotals of all cells in a range or only of visible cells. While many users are familiar with Excel’s function codes, some are unaware of how to use function codes to ignore hidden values. This is very handy if you are working with a large data set and want dynamic subtotals that change based upon filtered parameters. Excel offers a variety of subtotal functions, as shown in the table below. By using the function codes below, you can subtotal averages, counts, or sums of all values or only of values that are visible.

 
The SUBTOTAL function can be created in two ways:
  • Formula driven: Enter the following formula, with the appropriate function number from the table above and data reference range.

=SUBTOTAL(function number, reference)

  • Menu driven: Go to the “Formulas” tab in the ribbon and click on “Math & Trig” – scroll down and select “subtotal” from the list of functions. This opens the “Functions Arguments” window (shown below) and allows users to specify the appropriate function number from the table above and data reference range. 
The basic structure of the SUBTOTAL function is: =SUBTOTAL(function number, reference)
  • Function number refers to the numbers shown in the table above and is dependent upon the type of subtotal and whether you want to include hidden values in your subtotal.
  • Function numbers 1 through 11 include hidden values in the subtotal while function numbers 101 through 111 exclude hidden values from the subtotal.
  • Reference refers to the range of data to be subtotaled.

Example:  A SUBTOTAL function to average a column range of visible cells only:

=SUBTOTAL(101, A2:A15)

To change the subtotal to include hidden values in the column range, simply change the function number:

=SUBTOTAL(1, A2:A15)

As shown in the example below, if no cells or rows are hidden, the subtotal will include all cells within the range. For example, with all rows visible in the table below, the grand total duplicates the sum of responses received, as the detail records for each course are included in the total.

Note: The example shown below uses outline grouping to organize data rows – for guidance on how to use outline grouping, visit the Microsoft website.

 

 

As shown in the two examples below, by hiding data rows (either with group outlining or by right clicking and selecting hide), the same SUBTOTAL function now displays the total for only the visible rows.
 
 

 Comments

 
To add a comment, Sign In
Total Comments: 9
 
Terry posted on 3/12/2014 4:39 PM
Wow, I guess I have never really taken a close look at the subtotal function. Another great example of how to make Excel work for you.
Marlene posted on 3/12/2014 4:41 PM
Very handy tip! I never knew how versatile the SUBTOTAL function could be.
Steve posted on 3/12/2014 4:49 PM
Very useful, will help in keeping data sets parsimonious. Thanks!
Julia posted on 3/12/2014 5:29 PM
I was not aware of these functions in Excel. I agree that this would come in handy for large sets of data. Thank you for sharing!
Shabnam posted on 3/12/2014 9:49 PM
Very useful, thanks for sharing.
Mary Jo posted on 3/12/2014 10:44 PM
Great tip and nicely explained!
Lee posted on 3/13/2014 8:29 AM
Thanks Angela - this is a very helpful tip and has given me some new ideas about using Excel more effectively.
Annie posted on 3/13/2014 11:43 AM
This is great information to have. I have been looking for some tips on this functionality in excel.
Jim posted on 3/13/2014 12:10 PM
SUBTOTAL is great(!), but see also the newer and much lesser-known AGGREGATE function (eAIR: https://airweb.org/eAIR/techtips/Pages/AggregateExcel.aspx). AGGREGATE works very much like SUBTOTAL, but adds several additional essential functions such as median, mode, percentiles, and quartiles.