The SUBTOTAL Function for Excel Tables Total Row Explained

Bottom Line: Learn how the SUBTOTAL function is used in the formulas in the Total Row of Excel Tables.

Skill Level: Beginner

Video Tutorial

Watch on Youtube

Download the Excel File

You can follow along with the video using the same workbook I do, if you like. Here is the Excel file:

SUBTOTAL Function Explained (26.4 KB)

Using the SUBTOTAL Function in Excel

The SUBTOTAL function is a versatile function that can be used in many different scenarios in Excel.

For this post, I explain how the SUBTOTAL function is used in the Total Row of Excel Tables. The total row automatically creates the formula for us, but it's important to understand how the SUBTOTAL function works.

Excel-Tables-Total-Row-Creates-SUBTOTAL-Formulas-Automatically

Turning the Total Row on and off for an Excel Table is easy. Just click anywhere inside the table, go to the Design tab, and check or uncheck the box for Total Row.

Toggle the Total Row on and off from the Design tab

In the next post I explain a different use for SUBTOTAL in more of an outline or report style format. Sometimes you will see subtotal rows interspersed throughout a report or a data set, and then a Grand Total row at the very bottom. I will be discussing how the SUBTOTAL function and the Subtotal Feature of Excel help with that in the next post.

The SUBTOTAL Function Explained

This SUBTOTAL function will only display the totals for any visible cells. If you filter out or manually hide rows, the values from those cells will automatically be removed from the sum that the SUBTOTAL function creates.

The SUBTOTAL Function has two arguments.

  1. The first argument identifies what type of calculation you are looking for, such as an average, a sum, a count, a standard deviation, etc. Each of these types of calculation is assigned a number by Excel. 1 through 11 calculate based on all of the cells in a given data range, while 101 through 111 calculate only the cells that are visible in a data range. The default for the Total Row is 109, which is the designation for a sum of the visible cells.
Subtotal calculations determined by designated number which is the first argument in the SUBTOTAL function
  1. The second argument is the reference. This is simply the data range that you want the subtotal to calculate from. You can include more than one reference if you want the subtotal to include data from non-contiguous ranges.
The second argument in the SUBTOTAL function is the reference
Click to enlarge

Quickly Changing the Calculation Type

Next to the Subtotal calculation is a little arrow that expands to a drop-down box. This menu allows you to select a different type of calculation.

Change the calculation type using the dropdown menu

Instead of clicking the down arrow, you can use the keyboard shortcut Alt + .

AutoSum

Another way to create a SUBTOTAL function is by clicking on the AutoSum button on the Home tab.

Autosum button

Or you can use the keyboard shortcut Alt + =.

This calculates the same as the default for a Total Row (109), which is a sum of all visible cells in the referenced range. The only quirk about using AutoSum is that it doesn't format the result using the same formatting as the reference range.

Related Posts

Check out these related videos and tutorials for more information about the SUBTOTAL function or Excel Tables.

The Excel SUBTOTAL Function – Calculations on Filtered & Visible Cells Only

The SUBTOTAL Metrics Macro – Create a Summary Table of All Function Types

Create SUBTOTAL Formulas with the AutoSum Button or Keyboard Shortcut

Excel Tables Tutorial Video – Beginners Guide for Windows & Mac

Best Practices for Naming Excel Tables

Conclusion

I hope this post has been valuable in helping you understand how the Total Row works in Excel Tables. I welcome any feedback or questions in the comments below. See you in the next post!

    • Hey Ken,

      Great question! The AGGREGATE function was introduced in Excel 2010. It is similar to SUBTOTAL, but has a few additional options and calculation types.

      AGGREGATE has an option to ignore cells that contain errors, which is something SUBTOTAL can’t do. It can also ignore other SUBTOTAL and AGGREGATE functions.

      AGGREGATE also has 8 additional calculation types including: MEDIAN, MODE.SNGL, LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, and QUARTILE.EXC.

      I’ll do a post in the future on AGGREGATE. It’s a great function to know. SUBTOTAL is probably used more frequently because it is built into Tables and used by the Subtotal feature, which I’ll explain in next week’s post.

      I hope that helps. Thanks again and have a nice day! 🙂

  • Is there a way to select the lowest 10 scores out of the last 20 entries in an array > 20 entries (to calculate a golf handicap)?

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >