Excel SUBTOTAL() Function: Dynamic Calculations on Filtered Data

The SUBTOTAL() function executes statistical calculations while automatically adjusting to ignore hidden or filtered rows:

Task: Calculate total sales across all rows using =SUBTOTAL(9, C2:C7):

The Core Advantage: Filter the Region dropdown below (e.g. to West) and watch how =SUBTOTAL(9, C2:C7) recalculates to include only visible rows:

Observe the difference between SUM (filter-blind) and SUBTOTAL (filter-aware):

Managers use SUBTOTAL to allow stakeholders to filter by sales territory and immediately inspect regional volumes:

Function number 1 calculates the average of visible cells:

Use 2 for numeric COUNT and 3 for non-empty COUNTA:

Function 5 finds the minimum and function 4 finds the maximum price for visible categories:

Memorize these top 6 function codes:

Switch between regions to inspect regional totals:

If you manually hide a row (Right Click ➔ Hide Row), SUBTOTAL(9, ...) still counts it, whereas SUBTOTAL(109, ...) excludes it:

Test your formula selection skills:

Dashboard Task: Enter formula =SUBTOTAL(9, E2:E9) or =SUBTOTAL(109, E2:E9) to calculate the visible revenue:

Test your mastery of SUBTOTAL function numbers and filter handling:

Production rules for data professionals:

• Auto-Exclusion of Nested Subtotals: If you place regional subtotals inside column C and then write a grand total =SUBTOTAL(9, C2:C100) at the bottom, Excel automatically ignores the inner subtotals. You will never double-count numbers.
• Always Use Above Table Headers: Place your summary SUBTOTAL card above the filtered table rows so applying filters never hides the summary row itself!
• Table Total Rows Use SUBTOTAL: When you check "Total Row" in an Excel Table (Ctrl + T), Excel automatically inserts a =SUBTOTAL(109, ...) formula.