AutoSum in Excel: What It Is and How to Use It

AutoSum is the Σ button on the Home tab, at the right-hand end of the ribbon. It writes a SUM formula for you and guesses which cells you meant, which is why calculating with SUM sits among the objectives of the MOS Excel Associate exam. Most of the time the guess is right, which is why the button is popular — and why the two cases where it guesses wrong catch people out.

What AutoSum actually does

Click a cell below a column of numbers and press the Σ button. Excel inserts =SUM() and fills in a range by looking upward until it hits a blank cell or a non-number. It then selects that range so you can see what it chose. Press Enter to accept, or drag to correct it first.

That last part matters: AutoSum is a proposal, not a result. The highlighted range is your chance to check it.

The shortcut worth learning

Alt + = does the same thing without leaving the keyboard. On a Mac it is Command + Shift + T.

The shortcut becomes genuinely useful when you select a range first. Select an empty row beneath several columns of figures, press Alt+=, and Excel fills the entire row with column totals in one go. The same works for a column to the right of several rows.

Better still: select a block of numbers including one empty row below and one empty column to the right, then press Alt+=. Excel fills in the row totals, the column totals and the grand total at once.

AutoSum is not only SUM

The small arrow next to the Σ button opens a short menu: Average, Count Numbers, Max and Min. These behave identically to SUM — same range guess, same confirmation — but insert AVERAGE, COUNT, MAX or MIN.

Count Numbers is the one people misread. It inserts COUNT, which counts only cells containing numbers. If you want to count non-empty cells of any kind, including text, you need COUNTA, which is not on the menu and has to be typed.

Where the guess goes wrong

A blank cell in the middle of your data. AutoSum stops at the first blank it meets going upward. If row 8 is empty and your numbers run from row 2 to row 15, the total in row 16 will cover rows 9 to 15 only — and it will look perfectly reasonable. Always glance at the highlighted range before pressing Enter.

Totals mixed in with data. If your column already contains subtotals, AutoSum will happily include them and double-count. In that case use SUBTOTAL instead:

=SUBTOTAL(9, B2:B20)

Function number 9 means SUM. SUBTOTAL ignores any other SUBTOTAL results inside its range, so nested totals do not compound. It also ignores rows hidden by a filter, which is usually what you want when you are summing a filtered list. When the total has to depend on conditions rather than on the filter, SUMPRODUCT can sum on several conditions instead.

AutoSum in a Table

If your data is a proper Excel Table (Ctrl+T), you rarely need AutoSum at all. Tick Total Row on the Table Design tab and Excel adds a total row that uses SUBTOTAL automatically, adjusts when rows are added, and offers a dropdown per column to switch between Sum, Average, Count and the rest.

This is the more robust option for anything you will maintain over time. A Table’s total row cannot drift out of range when the data grows; an AutoSum formula written once against a fixed range can and does.

When AutoSum returns 0

Almost always because the values are text, not numbers. Numbers stored as text align to the left of the cell by default and are ignored by SUM. Select the range, look for the small green triangle in the corner of the cells, and use the warning icon’s Convert to Number option — or multiply the column by 1 in a helper column to force conversion.

The other cause is a circular reference: if the AutoSum formula’s range happens to include the cell the formula sits in, Excel warns you and returns 0. Shorten the range by one row.