Note: although the Outline feature is an "easy" way to insert subtotals in a set of data, a Pivot Table is a better and more flexible way to analyze data. In addition, a Pivot Table will separate the data from the presentation of the data, which is a best practice. This allows the subtotal results to remain visible even as rows are hidden and displayed when the outline is collapsed and expanded.
SUBTOTAL formulas inserted this way use the standard function numbers 1-11. You can find this feature at Data > Outline > Subtotal. SUBTOTAL with outlinesĮxcel has a Subtotal feature that automatically inserts SUBTOTAL formulas in sorted data. That is, as you filter rows in a table with a Total row, calculations automatically respect the filter. Excel uses SUBTOTAL for calculations in the Total row of an Excel Table because SUBTOTAL automatically excludes rows hidden by the filter controls at the top of the table.
Excel inserts the SUBTOTAL function automatically, and you can use a drop-down menu to switch behavior and show max, min, average, etc. The SUBTOTAL function is used when you display a Total row in an Excel Table. Values in rows that have been "filtered out" are never included, regardless of function_num. Note: SUBTOTAL always ignores values in cells that are hidden with a filter. When function_num is between 101-111, SUBTOTAL excludes values in rows that have been manually hidden. When function_num is between 1-11, SUBTOTAL includes cells that have been manually hidden. This is related to how SUBTOTAL deals with manually hidden rows. There are 11 functions available, each with two options, as seen in the table below. Notice these values are "paired" (e.g. SUBTOTAL behavior is controlled by the function_num argument, which is provided as a numeric value. = SUBTOTAL ( 3 ,B7:B19 ) // count visible = SUBTOTAL ( 9 ,F7:F19 ) // sum visible Available calculations