In Excel, use =SUBTOTAL(1,range) to average values while excluding rows removed by a filter. For example, if the numbers are in B2:B6, enter =SUBTOTAL(1,B2:B6); use function number 101 instead of 1 when you also want to exclude rows hidden manually.
What SUBTOTAL calculates
SUBTOTAL applies a chosen calculation to a range, such as an average, sum, count, or maximum. Its useful distinction from a plain average is that it responds to filtered lists: rows hidden by an AutoFilter are left out of the calculation. That makes it suitable for a report where someone might filter a table by department, date, product, or another field and expect the displayed average to reflect only the visible records.
The number 1 tells SUBTOTAL to calculate an average. The range after it tells Excel which cells to evaluate. The function does not require the data to be sorted, and the cells do not need to be formatted as a formal Excel table. It can work on a normal range or on a table column.
SUBTOTAL returns one numeric result, not an average for every group in the data. If a filter leaves no eligible numeric values, there is no meaningful average to calculate; a formula with no numeric values can return a division-by-zero error. For dashboards that may have empty filtered results, consider whether displaying that error is appropriate for your audience rather than assuming an average exists.
Syntax and function numbers
The syntax is SUBTOTAL(function_num, ref1, [ref2], ...). The first argument selects the operation; the next argument or arguments identify the range or ranges. For an average, the two relevant function numbers are 1 and 101. They both calculate an average and both ignore rows excluded by a filter. Their difference concerns rows hidden manually with Excel’s Hide command.
- 1: averages visible and manually hidden rows, but ignores rows excluded by a filter.
- 101: averages visible rows only, excluding both manually hidden rows and rows excluded by a filter.
So, choose 1 when manual hiding should not change the average, and 101 when any hidden row should be left out. These numbers are specific to SUBTOTAL; 101 is not a second average function or a percentage setting. The other operations also have function numbers, but they are not needed for an average.
With values in B2:B20, this formula averages the range while including manually hidden rows and excluding filtered-out rows: =SUBTOTAL(1,B2:B20). With the same cells and a requirement to ignore manually hidden rows too, use: =SUBTOTAL(101,B2:B20).
How filtering changes the average
SUBTOTAL’s main advantage is its response to AutoFilter. When you filter a list, Excel hides records that do not meet the selected criteria. SUBTOTAL recognizes those filtered-out rows and omits them from its calculation. If you change the filter, the result recalculates for the newly visible records. You do not need to edit the formula each time you choose a different category.
To turn on filtering in Excel for Microsoft 365 on Windows, select a cell in the data range and choose Data > Filter. Use the arrow in a column heading to select or clear filter criteria. If your data is formatted as an Excel table, filter arrows are generally available in its header row. The calculation itself is the same on Excel for Mac; ribbon placement and appearance can vary slightly between platforms.
A filter is different from simply changing a cell’s appearance. Hiding rows with a filter affects SUBTOTAL even if the formula’s first argument is 1. Hiding a row manually does not affect the result when the function number is 1, but it does when the number is 101. This distinction matters when a workbook mixes filtering with manual row hiding.
Worked example with visible rows
Suppose A2:A6 contains five product names and B2:B6 contains their sales amounts: 10, 20, 60, 40, and 50. With no filter applied, all five amounts are included. Their total is 180, and 180 divided by 5 gives an average of 36.
With product names in A2:A6 and sales amounts in B2:B6, enter this formula in an empty cell outside the filtered list: =SUBTOTAL(1,B2:B6). With no rows filtered out, the result is 36. If you filter the records so the row containing 60 is excluded, the visible amounts are 10, 20, 40, and 50. Their total is 120, and 120 divided by 4 gives a filtered average of 30; the SUBTOTAL result updates to 30.
By comparison, with those same values in B2:B6, =AVERAGE(B2:B6) continues to average all five values and returns 36, even while a filter hides the row containing 60. This is a common source of confusion: a row that is not currently displayed is not necessarily excluded by every Excel function. SUBTOTAL is designed for this visible-list calculation.
Put the result formula outside the rows being filtered, such as above the table or in a separate summary area. Excel ignores nested SUBTOTAL formulas in a referenced range, which helps prevent a subtotal row from being counted again in another subtotal. Even so, keeping summary formulas separate from the records makes a workbook easier to understand and reduces the chance of selecting an unintended range.
Filtered rows versus manually hidden rows
When planning the formula, decide what the word visible should mean in your workbook. A filtered row is excluded from both function number 1 and 101. A manually hidden row is included by 1 but excluded by 101. If the workbook uses only filters, either number will usually give the same result. If users also hide rows individually, the choice can change the average.
For example, with values in B2:B6, suppose a filter excludes one record and a different record is hidden manually. =SUBTOTAL(1,B2:B6) averages the values left visible by the filter plus the manually hidden value. =SUBTOTAL(101,B2:B6) averages only values that are neither filtered out nor hidden manually. Make the intended rule clear in a nearby note if other people will use the workbook, because the formula itself does not explain why a manually hidden row was included or omitted.
There is also a difference between hiding a row and filtering it out. A user might hide a row to reduce visual clutter, not to remove its value from a report. Function number 1 can preserve that value in the average. Conversely, if hiding a row is meant to exclude it from the summary, 101 better matches that intent. The right option depends on how the worksheet is meant to be used, not on which number is universally better.
When to use SUBTOTAL instead of AVERAGE
Use SUBTOTAL when the result should track the records currently displayed after filtering. It is especially useful for simple interactive reports, filtered lists, and table summaries where a reader narrows the data and expects the average to update. Use AVERAGE when you want the result to cover the specified cells regardless of whether some rows are filtered out.
Neither function treats every cell as a number. AVERAGE, and the average operation within SUBTOTAL, ignores blank cells and text entries in referenced cells; numeric zeroes count as values and can lower the result. If cells contain numbers stored as text, they may not contribute as expected. Check the source data if the result seems inconsistent with a hand calculation.
SUBTOTAL accepts more than one reference argument, so it can calculate across separate ranges. For instance, if the intended data is split into two nonadjacent blocks, the formula can name both references. Use this carefully: including the same cells twice can distort the calculation, and every reference should represent data that belongs in the same average.
SUBTOTAL is not a weighted-average function. It gives each included numeric value equal weight. If one row represents a different number of units or has a different importance, averaging the row-level values may not answer the intended question. In that case, define the weighting method first and calculate using appropriate source fields rather than assuming SUBTOTAL changes the meaning of an average.
Common errors and checks
A frequent mistake is entering =SUBTOTAL(1,B2:B20) and expecting it to ignore rows hidden manually. It will not: use 101 for that behavior. Another is selecting a range that omits the last record or includes a heading, note, or unrelated number. Check the range boundaries and confirm that the selected cells match the records being summarized.
- The result does not change after filtering: confirm that the formula refers to the rows being filtered and that the filter actually hides data rows.
- The result differs from a calculator: check for blanks, text-formatted numbers, zeroes, hidden rows, and the selected function number.
- The formula shows an error: check that the range contains numeric values and that the function number and parentheses are entered correctly.
- The average seems unexpectedly low: zeroes are included as numeric values, even though blank cells are ignored.
Also consider where the formula sits. If it is inside the range being filtered, its row may itself become hidden, making the summary hard to see. Place it outside the filtered records or use a separate summary area. When reviewing someone else’s workbook, inspect both the formula and the current filter state before deciding the displayed average is wrong.
Frequently Asked Questions
What is the Excel formula for a subtotal average?
Use =SUBTOTAL(1,range), replacing range with the cells to average. For values in B2:B20, the formula is =SUBTOTAL(1,B2:B20). It recalculates when a filter hides records.
Should I use 1 or 101 in SUBTOTAL?
Both calculate an average and ignore rows hidden by a filter. Use 1 to include manually hidden rows; use 101 to exclude manually hidden rows as well.
Does SUBTOTAL ignore filtered rows?
Yes. The average calculation excludes rows removed from view by an Excel filter, whether you use function number 1 or 101.
Does SUBTOTAL count zeroes in an average?
Yes. A zero is a numeric value and contributes to the average. Blank cells and text entries in referenced cells are ignored.
Why does SUBTOTAL give a different result from AVERAGE?
AVERAGE includes numeric values in its referenced cells even when a filter hides their rows. SUBTOTAL’s average operation excludes filtered-out rows, so the two results can differ while a filter is active.
Related
- Bullseye Glass Kiln Firing Schedules: A Practical Guide
- Enum in Programming: Meaning, Uses, and Examples
- Java Enum Values: Meaning, Methods, and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Aggregate Functions in UPDATE: What SQL AllowsUsually, no: SQL does not allow an aggregate such as AVG() or SUM() directly as a row-level value…
- Calculating r in Excel: Formula and ExamplesTo calculate Pearson’s correlation coefficient, or r, in Excel, use =CORREL(A2:A6,B2:B6) when the paired x values are in…
- Flash Fill in Excel: Is There a Formula or Shortcut?Flash Fill in Excel is not a formula: it detects a pattern in your example and fills matching…
- Excel Filter in Table Format: How to Set Up and Use ItTo filter data in an Excel table, click a cell in the table and use the drop-down arrow…
- Calculating NPV in Excel: Formula, Steps, and ExamplesTo calculate net present value in Excel, place the discount rate in B1, the initial investment in B2,…
- Vlookup with multiple criteria?Learn how to use VLOOKUP with multiple criteria in Excel, build a reliable helper key, avoid common errors,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.