AutoSum in Excel: the one-click total, and when it grabs the wrong range

To total a column of numbers in Excel, click the empty cell right below it, then click AutoSum, the Greek sigma (Σ) button on the Home tab, or press Alt+=. Excel writes a SUM formula, highlights the run of numbers it thinks you mean, and shows the total once you press Enter. That one click is AutoSum, and its whole job is guessing the range for you. The catch lives in the word guess: when a blank row or a text label sits in the column, AutoSum can stop short and total only part of it.

If you would rather write the total yourself, typing the arithmetic by hand with + or a SUM you type is the alternative; this page is about the button that writes it for you.

Where is the AutoSum button?

AutoSum sits in two places on the ribbon: Home > AutoSum, in the Editing group, and Formulas > AutoSum. Both run the same command. Select a cell, click it, and, in Microsoft’s words, “When you select AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.” The keyboard shortcut skips the ribbon: Alt+= on Windows, Cmd+Shift+T on a Mac.

What lands in the cell is an ordinary SUM formula, the same one you could write by hand. AutoSum only fills in the range.

How AutoSum guesses the range

AutoSum reads the cells next to the one you selected. Put the cursor in the empty cell below a column and it proposes the numbers above; put it in the empty cell to the right of a row and it proposes the numbers to the left. It takes a contiguous run: the numbers next to the cell, up to the first cell that is blank or holds text. Excel highlights that run so you can see it before you commit, and pressing Enter writes the total.

With five amounts in B2:B6 and the cursor in B7, AutoSum highlights B2:B6 and writes =SUM(B2:B6). The header in B1 is text, so the run stops below it and starts at B2.

When AutoSum grabs the wrong range

The same rule that makes AutoSum quick is what trips it. Because the run stops at the first blank or text cell, a gap in the column cuts the total short. Here a blank row 4 separates two groups of expenses:

Microsoft’s own guidance is to check the highlighted border before accepting it, because the range AutoSum proposes may not include every number you wanted, in which case you drag it wider or retype it. A header row, a subtotal line, or a stray blank all do the same thing: cap the run early. So the fix is a habit. After you click AutoSum, glance at the cells it lit up, and press Enter only once they cover every number you meant.

The arrow next to AutoSum: Average, Count, Max, Min

AutoSum is not only SUM. The small arrow next to the button opens a short list of the other running totals it can write. On the Home tab you select the arrow next to Sum in the Editing group and pick Min for the smallest value or Max for the largest; Average and Count Numbers sit in the same list. Count Numbers writes a COUNT, which tallies how many cells in the range hold numbers, so it is the count side of AutoSum rather than a total. Each choice drops a matching formula into the cell and guesses the range the same way SUM does.

In a table’s Total Row, totals write SUBTOTAL

There is one place a total changes what it writes. In an Excel table, turn on the Total Row (Table Tools > Design, then check Total Row) and pick a function from the drop-down in a total cell, and Excel enters a SUBTOTAL in place of a SUM. Microsoft’s example is =SUBTOTAL(109,[Midwest]), where 109 is the SUBTOTAL code for SUM. A SUBTOTAL instead follows the filter: it leaves out any rows the filter has hidden, where a plain SUM adds them all. The SUBTOTAL function covers the codes and the filter behavior in full.

The short version

• AutoSum, the sigma (Σ) button on Home > AutoSum and Formulas > AutoSum, writes a SUM for the numbers next to the selected cell. Run it with Alt+= on Windows or Cmd+Shift+T on a Mac.
• It guesses a contiguous run: the numbers above the cell for a column, or to the left for a row, stopping at the first blank or text cell. Check the highlighted range before you press Enter.
• A blank row or a header caps the run early, so AutoSum can total only part of a column. That is the wrong-range trap: read the range it lit up, and widen it if it fell short.
• The arrow next to it swaps SUM for Average, Max, Min, or Count Numbers. In an Excel table’s Total Row the drop-down writes a filter-aware SUBTOTAL instead.
• Prefer to write the total yourself? Typing the arithmetic by hand with + or a SUM you type does the same job without the guess.
• After a returned workbook’s total reads differently, compare the saved versions: the comparison marks the exact stretch of the SUM range that grew or shrank.