Software

Two quick ways to sum Excel data

Manually entering a SUM() function can introduce errors into your spreadsheet. These two easy point-and-click methods are safer and more efficient.
There are a number of ways to insert a SUM() function into an Excel spreadsheet. The hard way is to enter the entire function manually. That method is prone to mistakes because you must know the cell references and then enter them correctly. If you like, you can enter =SUM( manually, highlight the range to avoid incorrect cell references, enter the closing ), and then press Enter. That method eliminates the problem with cell references, but it still requires a lot of steps. There are two easier ways to sum values:
  • Highlight the row or column, plus one blank cell for the result, and press [Alt]++.
  • Highlight the row or column, plus one blank cell for the result and click AutoSum on the Standard toolbar.
For example, if you want to sum the values in cells B2:B5, you selecte cells B2:B6, and then press [Alt]++ or click AutoSum. Excel will display the sum in cell B6. You don't have to enter a thing -- just highlight and click or press. Obviously, AutoSum is the easiest route, but the Standard toolbar isn't always available, so knowing the [Alt]++ combination might come in handy. Most methods work with multiple columns and rows and noncontiguous blocks of values.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

28 comments
persifal300
persifal300

Excel 2007 it-is [ALT]+= and not [ALT]++

guflaate
guflaate

Here is another shortcut without using the mouse. Works best on continuous rows of numbers. Go to cell below last row: Use SHIFT + ALT + [=] and it places formula =SUM(x:y) If you have several columns of same length you can mark cells below last row and use SHIFT + ALT + [=]. If the leftmost column has all rows filled with numbers, the remaining columns will have same formula for the individual column and all cells does not need numbers. Try to delete the formulas, delete a number somewhere in the different columns, use SHIFT + ALT + [=] and see the differences.

Arcturus16a
Arcturus16a

Excel's new "feature" the Ribbon makes me take my hand off the keyboard too many times. ALT= works just right and will help me make up some of the lost productivity from moving my hand to the mouse and then back to the keyboard.

abdelwahaab
abdelwahaab

if you want to sum the values in cells B2:B5, you selecte cells B2:B5 wihout blan cell and then press click AutoSum

Nauthstar
Nauthstar

If you have a block of numbers (e.g. B1:D5)and you want to add the columns and the rows, highlight them all - including a spare column to the right and row at the bottom. Then use Alt+= or Autosum It will put all the equations in at once!

JG ENTERPRISES
JG ENTERPRISES

I just wanted to add a couple of points. 1- If there are no breaks in the group of numbers you want to add with the SUM function then by simply selecting the cell for the function and clicking on the Autosum symbol, the formula will be referenced correctly. (EXCEL 2007) 2- If you use FILTERS, you may want to use the @SUBTOTAL() function instead as this will give you a total based on the filtered selection.

j_eyon
j_eyon

I disocovered if any of the cells to be totalled is a =SUM() formula, it won't get added into the total.

keith.mildenhall
keith.mildenhall

"you must know the cell references and then enter them correctly." Exactly so; you must know and understand what you're doing. I've just spent two hours training people in Excel and, as always, used the most basic of techniques with formulae. I do this for the simple reason that it promotes better understanding of the process. I do point out that there are several ways to perform such operations (as is usually the case in MS Office) but that if you can handle writing a formula from scratch you can work on any spreadsheet you may be confronted with and will be in a better position to understand any formula errors. Utter reliance on Autosum or shortcuts has a nasty habit of breeding the 'blackbox with a button' approach to computing (I do this because I get a result; push the button and out it comes)rather like a child let loose with a calculator.

mossy37
mossy37

While these are quick ways to get a result in an adjacent cell it is an incorrect process. As with all other functions we should place the cursor in the cell where we want the result, then select the function (SUM or AutoSum) and then select the cell range, . This remains consistent if we want the result in cells not adjacent to the cell range. Geoff Moss

TheProfessorDan
TheProfessorDan

I just tried the short cut keys and the + sign on the number pad didn't work. My PC may be the exception but just thought I should point that out.

WinHaven
WinHaven

Nice to know some shortcut keys, especially with the ribbon of doom looming overhead now.

stephen.bryant
stephen.bryant

If the highlighted blank cell is either above a column of numbers or to the left of a row, Excel 2007 still puts the subtotal below or to the right of the numbers, not in the highlighted cell.

stapleb
stapleb

The blank columns and/or rows indicates to Excel where totals are to be placed. You can also select non-contiguous blocks, ensuring you include blank columns and/or rows, and use Alt = to sum each of the selected ranges.

DBlayney
DBlayney

If there are a number of adjacent columns of numbers of the same height, this works very nicely: select the cells beneath the columns; click the Autosum button and it fills in all the totals at once. The columns can be different heights but need to end on the same row. If you the totals to stand out, just CTRL-B before Autosum. Put the Autosum function on the Quick Lauch bar if you do this a lot

dhays
dhays

With the standard =sum() you can sum as many =sum() cells as you need I am doing it now in a spreadsheet to give me a running total between different sets of data. I haven't tried the shortcuts mentioned here.

CyberCowboy1
CyberCowboy1

I agree with Kenneth. Kudos for proper teaching technique. teach them to add, subtract, multiply & divide on paper (and/or in their head), or they will not know when they mis-keyed and the calculator gave them a "wrong" answer. AFTER they have mastered the basics, they can learn "shortcuts" but they will have a better understanding of when the shortcuts (or calculator in the instance of our example of children) are appropriate to use. Where were you when I was taking training?

kennethc
kennethc

Keith, Just to add my support to your views on Excel training and in particular formulas. Sometimes people are all too eager to know the short & snappy method without developing an understanding of how Excel functions. Kenneth

wbird
wbird

This did not work with my Office 2003. ALT= did work.

xcel-pro
xcel-pro

[Alt] [=] worked for me Using Office 2007 on a Gateway laptop with numberpad the {Alt] ++ would not work on numberpad

p@re
p@re

You may also select a Range plus a blank row and/or a blank column, and then press the SUM icon to add the SUM of each column on the last line, The SUM of each line on the last column and a Grand Total... even if there are blank cells...

nboz
nboz

All you have to do is click in the cell where you want the answer. You'd only need to select the cells first if they are not contiguous.

hilaryeberhardt
hilaryeberhardt

It doesn't work in Office 2007 either, but ALT= does. However, thanks for the tips!

melissatutors
melissatutors

[Alt][=] is what worked for me as well in Excel 2007.

CyberCowboy1
CyberCowboy1

This is true & you just hit enter to finalize if they are contiguous. If you select the cells and hit "[alt]=" the formula automatically finalizes. Not really an EXTRA keystroke, either select at beginning or hit enter at the end... choose your poison. ;-)

Editor's Picks