When you filter a list, ordinary AVERAGE still includes the hidden rows. SUBTOTAL with function 101 averages only the visible cells — the number updates live as you filter.
The example
Filter the list — the average follows.
| A | B | |
|---|---|---|
| 1 | Region | Visible avg |
| 2 | West | = SUBTOTAL(101,…) |
The formula
The formula:
How it works
How it works:
SUBTOTAL(101, range)averages only cells left visible by a filter.- The 1xx codes exclude filtered and manually hidden rows; the 1–11 codes (e.g. 1) exclude filtered rows only.
- It recalculates instantly as you change the filter — perfect for a summary row above a table.
- SUBTOTAL also ignores other SUBTOTALs in the range, so nested subtotals don’t double-count.
Code cheat-sheet: 101 = AVERAGE, 109 = SUM, 102 = COUNT, 103 = COUNTA, 104 = MAX, 105 = MIN, all visible-only. The newer AGGREGATE function does the same and can also skip errors — =AGGREGATE(1, 5, range) averages visible, error-free cells.
Try it: interactive demo
Toggle rows “visible”.
Variations
Sum visible
Code 109:
Count visible
Code 102:
Visible & error-free
AGGREGATE:
Pitfalls & errors
101 vs 1. Use 101 to also exclude manually hidden rows; 1 excludes only filtered rows.
Put it outside the data. A SUBTOTAL inside the filtered range can hide itself when filtered.
Errors still break it. SUBTOTAL doesn’t skip error cells — use AGGREGATE(1,5,…) if the range has errors.
Practice workbook
Frequently asked questions
How do I average only filtered (visible) rows in Excel?
What's the difference between SUBTOTAL 1 and 101?
How do I average visible cells that also have errors?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class