how to insert subtotals in excel for mac

Insert Subtotals In Excel Update Automatically To

SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows.

Despite the name, SUBTOTAL has the ability to perform a variety of math functions, including AVERAGE, COUNT, MAX, and many others (see table below for the complete list).

By default, SUBTOTAL excludes values in rows hidden by a filter, which makes SUBTOTAL very useful in Excel Tables.

Notice the only difference is the value used for the functionnum argument.

There are 11 functions available, each with two options, as seen in the table below.

Notice the values are paired (e.g.

This is related to how SUBTOTAL deals with manually hidden rows.

When functionnum is between 1-11, SUBTOTAL includes cells that have been manually hidden.

When functionnum is between 101-111, SUBTOTAL excludes values in rows that have been manually hidden.

Excel inserts the SUBTOTAL function automatically, and you can use a drop-down menu to switch behavior and show max, min, average, etc.

The reason Excel uses SUBTOTAL for calculations in the Total row of an Excel Table is because SUBTOTAL automatically excludes rows hidden by the filter controls at the top of the table.

Insert Subtotals In Excel Update Automatically To

That is, as you filter rows in a table with a Total row, youll see the calculations update automatically to respect the filter.

SUBTOTAL formulas inserted this way use the standard function numbers 1-11.

This allows the subtotal results to remain visible even as rows are hidden and displayed when the outline is collapsed and expanded.

In addition, a Pivot Table will separate the data from the presentation of the data, which is a best practice.

In horizontal ranges, values in hidden columns are always included.

The Total Row can automatically show summary calculations below a table.

In this video, well look at some formulas you can use to query a table.

We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

How To Insert Subtotals In Excel For Mac