In Excel for Microsoft 365 on Windows, press Alt+= to run AutoSum, but this is not a dedicated SUBTOTAL shortcut. To guarantee a subtotal that responds to filters, enter this formula for values in B2:B100: =SUBTOTAL(9,B2:B100).
AutoSum can choose SUBTOTAL in filtered-list situations, but its result depends on the range and context. If you need predictable behavior, enter SUBTOTAL directly and choose the function number that matches your goal. The details below explain how to insert it, how visible rows are treated, and what to check when the result seems wrong.
What the AutoSum shortcut does
On Windows, Alt+= invokes AutoSum. Excel looks at nearby data and proposes a range, then inserts a calculation. For an ordinary unfiltered range, that calculation is usually SUM. When you use AutoSum with a filtered list, Excel can use SUBTOTAL so that rows removed by the filter are excluded. Since AutoSum is context-sensitive, check the formula bar rather than assuming it has inserted a particular function.
To use the shortcut, select the cell where you want the answer, press Alt+=, check the suggested range, and press Enter. If Excel selected the wrong cells, edit the range before confirming. You can also use the AutoSum button on the Home tab or the Formulas tab. On a Mac, keyboard shortcuts can differ by keyboard and Excel setup, so use the ribbon’s AutoSum command if your usual key combination does not work.
AutoSum is handy for a quick total, but it is not a special keystroke that always inserts SUBTOTAL. If your requirement is specifically to calculate only the records currently shown after filtering, enter the function yourself.
How SUBTOTAL works
SUBTOTAL applies a chosen calculation to a reference, such as a column of amounts. Its key advantage over a plain SUM is that it ignores rows excluded by an AutoFilter. This makes it useful for reports where users filter by region, date, product, or another field and expect the displayed total to change with the selection.
For example, imagine B2:B6 contains 12, 8, 5, 10, and 15. With no filter applied, a sum subtotal is 50. If a filter hides the rows containing 8 and 10, the visible amounts are 12, 5, and 15, so the subtotal becomes 32. The formula can stay in place while the filter changes; Excel recalculates it for the visible records.
SUBTOTAL also avoids double-counting other SUBTOTAL formulas in its reference. That is useful in grouped or layered reports, where an overall subtotal range might include smaller subtotal rows. It does not, however, automatically identify meaningful categories or create the outline structure for you. It calculates over the reference you specify.
Choose the right function number
The first argument tells SUBTOTAL what calculation to perform. Numbers 1 through 11 include rows hidden manually, while numbers 101 through 111 exclude manually hidden rows as well. Both groups ignore rows removed by a filter. The second argument is the range or reference to calculate.
- 1 or 101: AVERAGE.
- 2 or 102: COUNT, for cells containing numbers.
- 3 or 103: COUNTA, for nonempty cells.
- 4 or 104: MAX.
- 5 or 105: MIN.
- 6 or 106: PRODUCT.
- 7 or 107: STDEV.
- 8 or 108: STDEVP.
- 9 or 109: SUM.
- 10 or 110: VAR.
- 11 or 111: VARP.
For a filtered total, 9 is often appropriate. Choose 109 when manually hidden rows should also be excluded. The distinction matters: a row hidden with the Hide command is not the same as a row removed from view by a filter. If the difference is important to your report, decide explicitly which behavior you want.
Examples for totals and counts
For a basic total, with amounts in B2:B6 containing 12, 8, 5, 10, and 15, use =SUBTOTAL(9,B2:B6). With all rows visible, the result is 50; if a filter hides the rows with 8 and 10, it returns 32. This example uses 9, so manually hiding a row does not by itself remove that row from the calculation.
For a total that should exclude both filtered-out and manually hidden rows, with the same amounts in B2:B6 and the row containing 10 manually hidden, use =SUBTOTAL(109,B2:B6). The result is 40: 12 + 8 + 5 + 15. If a filter also excludes some other values, those are excluded too.
For a count of visible numeric entries, with values in B2:B6 and two numeric rows filtered out, use =SUBTOTAL(2,B2:B6). If three numeric entries remain visible, the result is 3. To count visible nonempty entries instead, use 3 as the function number; this can include text as well as numbers.
SUBTOTAL can also work with a table column. With an Excel table named Sales and a numeric column named Amount, use =SUBTOTAL(9,Sales[Amount]) to total that column while respecting filters applied to the table. The table and column names in the formula must match the names in your workbook.
Filtered rows versus manually hidden rows
This is the most important behavior to understand when choosing between the two sets of function numbers. A filter hides records that do not meet the current criteria, and SUBTOTAL excludes those records regardless of whether you use 9 or 109. Manual hiding is different: function numbers 1–11 include manually hidden rows, while 101–111 exclude them.
For instance, if an amount row is hidden using the row header’s Hide command, a subtotal using 9 still includes that amount. A subtotal using 109 leaves it out. If instead a filter hides the record, both versions leave it out. This distinction explains why a formula may appear to disagree with what is currently visible on screen.
Another useful point: hiding a column does not generally change which rows SUBTOTAL evaluates. The function’s visible-row behavior concerns rows, not a general rule to calculate only whatever cells happen to be visually exposed in every way. If your layout uses hidden columns or complicated references, verify the calculation against a small known example.
How to choose between SUBTOTAL and SUM
Use SUBTOTAL when people will filter a list and expect totals, averages, or counts to follow the displayed records. Use SUM when the intended result is the total of every value in a range, whether or not some rows are filtered out. A SUM formula does not adapt to filter visibility in the same way.
For a one-time calculation in an unfiltered range, AutoSum is usually faster than entering a formula. For a reusable filtered report, SUBTOTAL communicates the intended behavior more clearly. If you want a report with separate totals for categories, use Excel’s Subtotal command or a PivotTable as appropriate; those are report-building features, while the SUBTOTAL function only calculates over a reference.
In a structured table, SUBTOTAL is often a good choice for a total row because it responds to the table’s filters. The function is not limited to tables, though: it can refer to a regular range. Make sure the referenced cells cover the intended records and do not accidentally include unrelated data below or beside the list.
Common problems and fixes
- AutoSum inserts SUM instead. AutoSum is not guaranteed to choose SUBTOTAL. Replace the inserted formula with SUBTOTAL if filter-aware behavior is required.
- The total includes a manually hidden row. Change the function number from the 1–11 group to the corresponding 101–111 group if hidden rows should be excluded.
- The result is zero or unexpectedly small. Check that the reference covers the data cells and that amounts are stored as numbers, not text. Text that looks like a number may not contribute to a sum.
- The answer does not change as expected. Check whether the rows were filtered or manually hidden, and confirm that the reference includes the rows you are filtering.
- A subtotal seems to be missing from the total. SUBTOTAL ignores other SUBTOTAL calculations within its referenced range to prevent double counting. If you expected a nested subtotal row to be added again, review the range and calculation design.
- The formula returns an error. Check the function number, commas, parentheses, and reference. In standard English-language Excel formula syntax, the arguments are separated by commas; some regional settings use different list separators.
When troubleshooting, compare the formula result with a small set of visible values that you can add manually. Then change one condition at a time: clear the filter, hide a row manually, or adjust the function number. This makes it easier to identify whether the issue is the selected range, the type of hiding, or the data itself.
Frequently Asked Questions
What is the shortcut for the SUBTOTAL formula in Excel?
There is no dedicated Windows shortcut that always inserts SUBTOTAL. Alt+= runs AutoSum and may choose SUBTOTAL in a filtered-list context, but typing the function directly is the dependable way to specify it.
Does Alt+= insert SUBTOTAL or SUM?
It runs AutoSum. Excel commonly inserts SUM for an ordinary range and can use SUBTOTAL when working with filtered data. Inspect the formula Excel proposes before accepting it.
Which SUBTOTAL number ignores filtered rows?
All supported function numbers ignore rows excluded by a filter. Use the 101–111 range when you also want manually hidden rows excluded; use 1–11 when manually hidden rows should still count.
Does SUBTOTAL ignore manually hidden rows?
It depends on the function number. Numbers 1–11 include manually hidden rows, while their counterparts 101–111 exclude them. Both sets exclude rows hidden by a filter.
Can I use SUBTOTAL in an Excel table?
Yes. You can refer to a table column, and the calculation responds to filters applied to the table. Confirm the table and column names in the structured reference match your workbook.
Why does my SUBTOTAL not change when I filter?
Check that the filter applies to the rows in the formula’s reference and that the formula includes the intended data range. Also confirm that you are changing a filter rather than hiding a column or changing a display setting.
Related
- Encrypt and Password-Protect an Excel File
- Encrypt Cells in Excel? What You Can Protect Instead
- How to Encrypt an Excel File With a Password
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Round Down in Excel: Formulas for Specific Decimal PlacesTo round a value down to a chosen number of decimal places in Excel, use the ROUNDDOWN function.…
- Excel SUM and XLOOKUP: How to Add Lookup Results in ExcelTo add the values returned by XLOOKUP, wrap the lookup formula in SUM: with prices in B2:B5, matching…
- Excel Drop-Down List with INDIRECT: Setup and FixesYes. To make a dependent drop-down list in Excel, create named ranges for the choices and use =INDIRECT($A2)…
- Excel Data Sorting: Sort Rows, Columns, and Lists CorrectlyTo sort data in Excel for Microsoft 365 on Windows, click a cell in the column you want…
- Rank in Excel With Ties: RANK.EQ and Unique RanksTo give equal values the same rank in Excel, enter a RANK.EQ formula. With scores in B2:B6, put…
- Excel SUMIF with Dates: Rules, Examples, and FixesTo sum values for a date or date range in Excel, use SUMIF with a date criterion; for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.