Excel Tutorials · Lesson 38
Typing =SUM( and dragging across a range works, but it is not the fast way. Alt+= inserts a correctly-scoped SUM formula in one keystroke, and it scales from a single column to an entire table selected at once. This lesson covers the shortcut, the multi-block trick from the video, and where AutoSum quietly guesses wrong.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
AutoSum is the command behind the Greek sigma button on the Home tab, and Alt+= is its keyboard shortcut. Pressed with the cursor in an empty cell beneath or beside a range of numbers, it looks at the adjacent cells, guesses which ones you want totaled, and drops in a =SUM() formula with that range already filled in. Press Enter and the total appears, all without typing a single character of the formula yourself.
The word "guesses" matters. AutoSum scans upward first, and if it finds numbers directly above the active cell it proposes summing those. If there is nothing above, it scans left instead. It stops the moment it hits a blank cell, a text label, or the edge of the data, and that boundary becomes the edge of its guessed range. Most of the time that guess is exactly right, because most totals sit directly beneath or beside the numbers they total.
What makes the shortcut worth learning past that single case is that it works on a selection, not just a single cell. Select an empty column beside several rows of numbers and press Alt+=, and every cell in that column gets its own correctly-scoped SUM formula in one keystroke. Select several disconnected blocks of numbers at once with Ctrl+click, each with its own blank total cell already included in the selection, and Alt+= totals every block simultaneously, which is the exact trick demonstrated in the video.
AutoSum is not limited to addition. Clicking the small dropdown arrow beside the sigma button on the ribbon offers Average, Count Numbers, Max and Min as one-click alternatives, each following the identical guess-the-range logic that SUM uses.
Step-by-step, matching the video above.
AutoSum needs somewhere to write the formula. Click the blank cell directly beneath the last number in the column you want totaled.
Hold Alt and press the equals key. Excel scans upward, finds the block of numbers, and inserts =SUM(range) with the range already selected and highlighted with marching ants.
Before pressing Enter, glance at the dashed border around the proposed range. If it stops short of a number, or includes a row it should not, adjust it by dragging the border or retyping the range.
Confirming locks in the formula and the total appears immediately. The cell now holds a live SUM formula, not a static number, so it updates if the source values change.
Select the total cell and drag the fill handle across neighboring columns, or select the whole blank total row before columns of numbers and press Alt+= once to fill every column's total in a single keystroke.
Hold Ctrl and click each blank total cell beneath separate blocks of numbers, one click per block, then press Alt+=. Every selected cell gets its own correctly-scoped total in one action.
Click any cell inside an Excel Table, press Ctrl+A to select the whole table, then Alt+=. A Total Row appears beneath the table with a sum already calculated for every numeric column.
| A: Region | B: Q1 Sales | C | D: Region | E: Q2 Sales |
|---|---|---|---|---|
| East | 18,400 | East | 21,100 | |
| West | 22,900 | West | 19,600 | |
| South | 16,750 | South | 24,300 | |
| Total | 58,050 | Total | 65,000 |
Ctrl+click cell B5 and cell E5, the two blank total cells beneath each separate block, then press Alt+=. Both fill in the same instant, each with its own correctly-scoped =SUM() pointing only at its own block. This is the multi-block trick from the video, and it works with as many disconnected ranges as you can select.
The four situations where the proposed range needs a correction before you press Enter.
AutoSum stops the instant it hits an empty cell. A single blank row inside your data means the proposed range only reaches down to that gap, silently missing everything above it.
If a subtotal sits partway down the column, AutoSum happily includes it, which double-counts every row already captured in that subtotal. Check the highlighted range against any subtotal rows before confirming.
SUM ignores text values with no warning. A column that looks numeric but contains text-formatted entries, common after a CSV import, will total lower than expected and nothing will flag it.
If the active cell holds a label or a stray value rather than being blank, AutoSum still tries to guess a range from its neighbors, and the result can point somewhere unexpected. Always start from a genuinely empty cell.
Four extensions to the basic shortcut worth knowing.
Select a blank row beneath several columns of numbers, from the first total cell to the last, and press Alt+= once. Every column in the selection gets its own total simultaneously, the same logic as summing a single column extended across many.
Click the small arrow beside the sigma icon on the Home tab to choose Average, Count Numbers, Max or Min instead of Sum, each inserted the same way AutoSum inserts SUM, guessing the range from adjacent numbers.
A plain SUM formula totals every row in its range whether or not it is currently hidden by a filter. If you want a total that reflects only the visible, filtered rows, use SUBTOTAL(109,range) instead, which AutoSum does not insert automatically.
Selecting a range of numbers without inserting any formula shows Sum, Average and Count automatically in the status bar at the bottom of the window, useful for a fast check when you do not need the total written into a cell.
| Product | Units | Revenue | Cost |
|---|---|---|---|
| Widget A | 240 | 7,200 | 4,320 |
| Widget B | 185 | 5,550 | 3,145 |
| Widget C | 310 | 9,300 | 5,270 |
| Total | 735 | 22,050 | 12,735 |
Click any cell inside this table, press Ctrl+A to grab the whole thing, then Alt+=. Excel adds a Total Row beneath the table and fills every numeric column at once, using SUBTOTAL rather than plain SUM so the totals stay accurate if the table is later filtered.
When the shortcut is faster, and when it is not.
Any time the numbers sit in one unbroken block directly above or beside the total cell, Alt+= is faster than typing =SUM( and dragging, and it produces the identical formula.
Totaling cells scattered across different areas that are not selectable together, or combining ranges from different sheets, needs a manually typed formula like =SUM(A2:A5,C2:C5,'Sheet2'!B2:B5), which AutoSum cannot construct on its own.
The one habit that prevents every AutoSum mistake is glancing at the highlighted range before pressing Enter. It takes half a second and catches every one of the guessing errors above.
Same idea, different shortcut key.
Google Sheets uses Alt+Shift+= on Windows or ⌘+Shift+= on Mac, since Alt+= alone is reserved by the operating system. It follows the identical guess-the-adjacent-range behavior as Excel.
The sigma icon in the Sheets toolbar carries the same dropdown for Average, Count, Max and Min that Excel offers, inserted the same way with the same range-guessing logic.
Ctrl+click, or ⌘+click on Mac, to select several separate blank total cells across different blocks of numbers, then use the shortcut to total all of them in one action, exactly as in Excel.
AutoSum shows its guessed range with a dashed border for a reason. Confirming without a glance is how a range that stopped short at a blank row, or that swallowed a subtotal, gets written permanently into the sheet.
AutoSum stops scanning the instant it hits an empty cell. A single gap row means everything above it is silently excluded from the proposed total.
A subtotal sitting inside the range gets added into the grand total along with the rows it already summarizes, double-counting that section. Check the highlighted range against any subtotal rows first.
A column with text-formatted numbers sums lower than it should, and SUM gives no warning that anything was skipped. Convert text-formatted numbers before trusting the total.
AutoSum still tries to build a range from a cell containing a stray character or an old value, and the guessed range can end up unrelated to the data you meant to total.
A regular SUM formula totals every row in its range regardless of whether a filter is currently hiding some of them. Use SUBTOTAL(109,range) when the total needs to reflect only visible rows.
A total shouldn’t need a formula rebuilt every time a row is added.
Free 14-day trial. No credit card required.
Alt+=. Click the empty cell below your numbers, press Alt and the equals key together, and Excel inserts a SUM formula with the range already guessed and highlighted.
It scans upward from the active cell first, and if there is nothing above it scans left instead, stopping the instant it hits a blank cell, a text label, or the edge of the data.
Yes. Ctrl+click each blank total cell beneath a separate block of numbers, then press Alt+=. Every selected cell fills with its own correctly-scoped total in a single keystroke.
Click inside an Excel Table, press Ctrl+A to select the whole table, then Alt+=. A Total Row appears beneath the table with every numeric column summed at once.
A blank row inside the data block stops AutoSum's scan early. Check the highlighted range before confirming, and extend it manually if it stopped short.
A subtotal row was likely included in the proposed range, which adds those rows into the total twice, once directly and once through the subtotal. Review the highlighted range for any subtotal rows before pressing Enter.
Some of the numbers are probably stored as text, which SUM silently skips without any warning. Convert the column with Text to Columns or a helper formula using VALUE.
Yes. Click the small dropdown arrow beside the sigma button on the Home tab to choose Average, Count Numbers, Max or Min instead of Sum, inserted the same way.
Alt+Shift+= on Windows or Cmd+Shift+= on Mac, since Alt+= alone is reserved by the operating system there. It follows the same range-guessing logic as Excel.
Use SUBTOTAL(109,range) instead of a plain SUM. AutoSum does not insert this automatically, but a Table's Total Row uses it, which keeps totals accurate when the table is filtered.
No, not automatically. A formula combining ranges from different sheets, such as =SUM(A2:A5,'Sheet2'!B2:B5), has to be typed manually.
Select the range of numbers without pressing anything. Excel shows the Sum, Average and Count automatically in the status bar at the bottom of the window.
Yes. Select a blank row beneath several columns of numbers, from the first total cell to the last, and press Alt+= once to fill every column's total simultaneously.
Every business starts with a spreadsheet. Updoot is where you scale past it, with rollups that stay current without a single Alt+=.
Start Your Free Trial →