Table of Contents## Make the Excel Subtotal Function Dynamic

Entire Site

**Although SUBTOTAL is one of
Excel’s most convenient functions, you sometimes
want to choose the function it uses, or apply it to data that can
expand and contract**.

You
use the `SUBTOTAL`

function in Excel to perform a
specified function on a range of cells that have had AutoFilters
applied to them. When the AutoFilter has been applied, the
`SUBTOTAL`

function will use only the visible cells;
all hidden rows are ignored. The operation it performs depends solely
on the number (between 1 and 11) that you supply to its first
argument, `Function_num`

. For example:

=SUBTOTAL(1,A1:A100)

will average all visible cells in the range A1:A100 after AutoFilters have been applied. If all rows in A1:A100 are visible, it will simply average them all and give the same result as: