Practice free

How to sum a column in Excel

Draft. Not published yet.

Click the empty cell under the numbers, press Alt+= (or select AutoSum on the Home tab) and press Enter. Excel writes =SUM( ) with the cells above and shows the total.

Part of the Math and trig functions guide.

Method 1: Use the AutoSum button

Ribbon

  1. Click the empty cell under the numbers, for example A5.
  2. Select Home > Editing > AutoSum.
    Drawing to come: The Home tab with AutoSum in the Editing group
  3. Check the cells Excel picked: the cell shows =SUM(A2:A4), leaving out the Sales header.
  4. Press Enter.

A5 shows 60, the total of 10, 20 and 30.

Method 2: Press Alt+=

Keyboard

  1. Click the empty cell under the numbers, for example A5.
  2. Press Alt+=.
  3. Press Enter.

The same =SUM(A2:A4) as the button, and the same total: 60.

Method 3: Type the SUM formula

Formula

Use this when the total goes somewhere other than straight under the numbers.

  1. Click the cell that should show the total.
  2. Type =SUM(.
  3. Select the cells to add, for example A2:A4.
  4. Type ) and press Enter.

The cell shows the total, and the formula bar shows =SUM(A2:A4).

What goes wrong

The total is smaller than it should be

SUM skips a number stored as text. With A3 holding 20 as text, =SUM(A2:A4) returns 40. Set the cell’s format back to General and type the number again; the total goes back to 60.

SUM shows #VALUE!

That happens when text goes straight into the formula, as in =SUM("a",3). Text in the cells you select is skipped instead; text typed between the brackets is an error.

What Excel returns

We ran these in desktop Excel (build 16.0.20527) and recorded what each cell showed.

FormulaExcel showsNote
=SUM(1,2,3)6Three numbers typed straight into the formula.
=SUM("a",3)#VALUE!Text typed straight into the formula is an error.

Try it

Live grid coming soon

Your turn: In A5, write a formula that adds A2 to A4.

Functions used

Questions people ask

Does SUM add the header at the top of the column?

No. AutoSum and Alt+= start below the header, and SUM skips text in the cells it adds.

Why is the total smaller than it should be?

A number stored as text is skipped. With 20 stored as text, =SUM(A2:A4) over 10, 20 and 30 gives 40, not 60.

Part of the Math and trig functions guide.