The baseline: what AutoSum is supposed to do
Microsoft's AutoSum guidance says to select a cell next to the numbers, apply AutoSum, and press Enter. The cell is directly below a column or directly to the right of a row. Excel enters a SUM formula and highlights the cells it proposes to total.
Microsoft's SUM page adds a useful tip: you can press Alt+= to add the SUM function to a cell quickly, then select your ranges yourself. That page also notes that the AutoSum Wizard works best on contiguous ranges. If there is a blank row or column in the sum range, Excel's selection stops at the first gap.
Nothing here is a bug. AutoSum guesses at your intent, and the highlighted range is Excel showing you the guess.
Trick one: filters flip SUM into SUBTOTAL
Phillips reports that when he applied filters first and then pressed Alt+= beneath the visible records, Excel inserted a SUBTOTAL formula instead. The total then followed the filter as he changed it.
This is not unique to his test. Excel Campus describes the same behavior, and Contextures walks through it as a "quick SUBTOTAL" technique. The formula Excel generates on a plain filtered range is SUBTOTAL(9,...), according to Excel Campus.
Phillips also found a gotcha. If you put a SUM total inside a range and filter afterward, the total row can be treated as part of the filtered data and hidden with the other rows. His advice is to apply filters first, press Alt+= afterward, and keep the total outside the filtered range. This disappearing-total result comes from his own testing. The Microsoft pages reviewed don't document it.
He also says the SUBTOTAL formula stays in place after you clear the filter, so it keeps working the next time you filter.
9 versus 109
The SUBTOTAL function number matters here. Microsoft documents that function numbers 1–11 include manually hidden rows, while 101–111 exclude them. Phillips saw SUBTOTAL(9,...) in his filtered-range test and SUBTOTAL(109,...) in the table Total Row.
Rows removed by a filter are always excluded, whichever number you use. That gives you a practical check:
- Filtered rows only: both 9 and 109 ignore them.
- Rows you hid manually with Hide Rows: 9 still counts them, and 109 does not.
If your sheet mixes filtering and manual hiding, look at the formula after pressing Alt+=. If it shows 9 and you want hidden rows excluded too, change it to 109.
Trick two: Alt+= under a table enables the Total Row
Phillips says pressing Alt+= directly beneath an Excel table didn't insert a loose formula. Instead, AutoSum enabled the built-in Total Row automatically. After that, the totals updated as he filtered, added rows and edited values.
Two caveats apply:
- Contextures says that when a list is a formatted table, it might expand automatically and put the formula in a Total row. Its wording is hedged ("might"), so don't assume it always happens.
- Excel Campus describes a related but different method. Select the cells in the Total Row and use AutoSum to create the SUBTOTAL formulas. Its drawback is that AutoSum doesn't use structured references. It produces something like
SUBTOTAL(109,B2:B6)instead of the table-style reference.
Microsoft's documented route is manual. Click inside the table, go to Table Design (Table Tools > Design in older builds), and check Total Row. Each Total Row cell then has a drop-down with options such as Sum, Average, Count, Max and Min. Microsoft's example formula is =SUBTOTAL(109,[Midwest]). Microsoft also says that if you toggle the Total Row off and on, Excel remembers formulas you applied.
Phillips noticed one more quirk. If a Total Row cell was set to Average, pressing Alt+= again changed it back to Sum. The Microsoft pages reviewed don't document this, so treat it as one tester's observation. It is still a good reason not to hit the shortcut on a Total Row you have customized.
Trick three: your selection decides the layout
This is the part Phillips liked best. He says the size of your selection determines what Excel calculates:
- Select just the data and you get column totals.
- Include an empty row beneath the data and the totals land in that row.
- Include an empty column to the right and you get row totals in that column.
- Include both and you get row totals, column totals and a grand total in one step.
His example had data in B2:E5. He selected B2:F6 and pressed Alt+=. Excel filled in the row totals, the column totals and the bottom-right grand total.
Microsoft's AutoSum page confirms the underlying idea of selecting several result cells and applying AutoSum to total more than one column at once. Its example highlights B7 and C7 together. The full B2:F6 grid result comes from Phillips's demonstration. ExcelChamps independently describes the same selection technique.
Where AutoSum guesses wrong
Phillips reports these blind spots:
- Blank cells stop the range detection, so numbers beyond the gap are left out. Microsoft says the same.
- Text labels inside a dataset can interrupt the suggested range, too. ExcelChamps agrees that AutoSum stops at the first blank or text cell, which gives a total that is too low.
- Numbers stored as text were still included in his suggested range and summed correctly. That is an observed result, not a documented guarantee. Check the value before you rely on it.
- Existing totals get no special treatment. Pressing Alt+= beneath a SUM result simply created another calculation based on that total, which risks double counting in a report.
- Manual selection bypasses range detection. In one test he selected numbers and text together, and Excel summed the cells he had selected.
The claim to treat carefully: Ctrl-selected ranges
Phillips says AutoSum can insert totals into several non-adjacent selections at once if you select them with Ctrl. I'd treat this as unconfirmed. Microsoft's SUM page states that the AutoSum Wizard doesn't work on noncontiguous ranges. It does describe a manual route: type =SUM(, Ctrl+click each range, and press Enter.
The two statements may not conflict. Selecting several separate empty result cells is different from asking AutoSum to total a non-contiguous source range. But Microsoft doesn't document the former. Test it in your own Excel build before putting it into a shared workbook.
A quick checklist
- Look before you press Enter. The marching-ants range is the guess. Adjust it with Shift+arrow keys.
- Filter first, total second, and keep the total outside the filtered range.
- Check the function number in any SUBTOTAL formula: 9 includes manually hidden rows, 109 excludes them.
- For tables, use the Total Row drop-down when you want something other than Sum. Don't rely on Alt+= to preserve it.
- For quick grids, select the data plus one empty row and one empty column, then press Alt+=.
- Check the keyboard and platform. Microsoft lists Alt+= in its Windows shortcut section, and shortcuts can differ on Mac and the web.
The bottom line
Alt+= is small, but it is far from a one-trick shortcut. It switches to SUBTOTAL on filtered data and can produce a full grid of totals from one selection. The table behavior and the Ctrl-selection claim rest mostly on one author's testing, so verify both in your own Excel version. The shortcut saves you typing. It doesn't check your range for you, and an Enter keypress on a wrong guess puts the wrong number in your report.
References
- I've used Alt+= in Excel for years, but I never realized it could do all this How-To Geek · 2026-11-20T12:00:16+00:00
- Use AutoSum to sum numbers in Excel | Microsoft Support support.microsoft.com
- Keyboard shortcuts in Excel | Microsoft Support support.microsoft.com