Excel AutoSum Revisited

You just can't get away from AutoSum. If I had a pound for every time I've clicked AutoSum to add up a column of numbers I would probably be sitting on a tropical island somewhere still clicking it to add up all my vast wealth. It's one of the first things you learn about in Excel and, of course, everybody knows it inside out.

Select the cells

Here's a really easy way of calculating column and row totals without having to do all that infernal copying.

First job is to select all the cells containing your numbers and extend the selection to the empty cells where you want the totals.

Click AutoSum

Then you click AutoSum and you're done! It really is that easy. If you only want the column totals then just select the cells with the numbers and the totals are returned into the row below.

Column and Row totals

Here's all the column and row totals done in two simple steps. And they are done perfectly as the range references in the SUMs are implied by the selection. They hardly need to be checked.

AutoSum shortcut

If you prefer to do the whole thing with your keyboard then hold down the SHIFT key and extend the cell selection by pressing the arrow keys. Then use the shortcut key combination ALT= to generate the SUM formulas.

Grand Totals

But it's not just one table is it, you've got loads of them and some serious number crunching to do. You can extend the select-and-click-AutoSum method to do grand totals as well. But, be careful, this method only really works if every line item in your worksheet is drawn into a subtotal.

The Selection for AutoSum Grand Totals

Firstly, calculate all of your individual subtotals and then make a selection of all of the cells containing your numbers and the cells containing your subtotals.

Now, click AutoSum and all the cells containing your subtotal SUMs are detected and the Grand Total is calculated correctly without any risk of double-counting.

Ranges and Unions

Here's the resulting formulas displayed in the cells and you clearly see the formulas containing the original range references for the subtotal SUMs.

The grand total SUMs are unions, showing each subtotal reference separated by commas.

Checking your Formulas

Show the Formulas

As some of us know from bitter experience it is very embarrassing when a simple mistake in your formulas messes up all your careful calculations. It's always a good policy to check that you've got your formulas right but it's a bit painful to view them in the formula bar. 

Formulas displayed in the cells

Display the formulas directly in the cells by clicking the Show Formulas control. It's in the Formula Auditing group of the Formulas tab. Click once to show the formulas and click again to show the results.

Show Formulas shortcut

If you have an older version of Excel then you have to search in the Tools, Options dialog for this option which is not much fun and I recommend that you use the shortcut key combination CTRL ` (that's usually the key under the ESC key on the top right of your keyboard}

Happy number crunching.

Related Posts

Excel-Calculations without formulas
Excel-Sorting by last name
Excel-Switching columns to rows
Excel-Calculating age from date of birth

Training Courses

If you've still got that "I just don't know what I'm doing" feeling then you might like to arrange an Excel training course for yourself or with some of your colleagues. It's really easy to book one of our courses and they're great value for money. See our website for full details.