ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week

Excel Tip of the Week #380 - AutoSum redux

Author: David Lyford-Smith

Published: 09 Feb 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week! This week we have a Basic User post in which we’re returning to the humble AutoSum feature. This was last discussed back in TOTW #261.

What does AutoSum do? Is it a good feature?

AutoSum automatically writes one or more formulas for you.  In its simplest form, it can be used to automatically add a total to a column of figures:

Image 1

It can be invoked easily by either pressing the AutoSum button on the Formulas bar, or with the keyboard shortcut Alt =.  If you select data first, then AutoSum will apply to that data; if you just press the button with nothing in particular selected, Excel will do its best to figure out what is intended.

AutoSum is certainly the fastest way to write a simple function, and so it’s widely used by many – as evidenced by the fact that it’s the second button on the Formulas tab.  It skirts around the need to understand Excel’s formula syntax, so it’s also popular with less experienced users.  And it also simplifies the process of writing a formula because it selects the cells for you.

But AutoSum also has its pitfalls.  Because it’s automated, Excel can occasionally go wrong in the cells it selects.  Here are a few examples:

Image 2

Here, an account code that is in numeric format has been picked up by the AutoSum.  If not noticed this will throw out the totals.

Note that AutoSum is smart enough to exclude headers if they are dates, even though in other respects Excel treats dates like numbers.

Image 3

Here an error has occurred due to a gap in the underlying data. Note that this happens even though the neighbouring column has no gaps. This is particularly tricky in a very tall dataset, where gaps may not be obvious.

However, careful use can avoid these issues. For example, if you select the data you want to sum before activating AutoSum, it will carry out the job automatically:

Image 4

Going further with AutoSum

There are actually a few other little tricks you can do with the AutoSum feature.

First, if you want your totals to be separated from the figures, just include some extra blank rows in your selection:

Image 5

Secondly, you can get AutoSum to add row totals instead of column totals by selecting the appropriate range:

Image 6

And in fact, you can do both at once, as well as a grand total:

Image 7

And finally – despite the name, AutoSum doesn’t have to be a SUM function!  You can choose from a few other popular functions if you use the Formulas bar:

Image 8

And that’s about everything there is to know about AutoSum!  It’s a handly little feature, but always check carefully that your formula ranges are correct, ideally by selecting your data yourself first, rather than pressing the key in the cell where you want your total and hoping Excel figures it out correctly.

You may also like

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel polaroid
Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250