Weekly Computer Tip # 100
12 February 2005

This tip first appeared in the Cambridge Evening News

Calculating subtotals and grand totals in a list (Microsoft Excel)

A few of you have written to me asking where the usual tip was last Saturday, and the answer is that I was sticking to my New Year's resolution to give myself some time for sightseeing when away for business. (One of my other resolutions was to stick to my New Year's resolutions! ;-) This means, however, that we're getting very close to the discount deadline of 15th February so if you've been thinking about signing up for the next Excel with Excel seminar (and learning about the fifteen most underutilised features that can make you more productive with less work) ... coming Tuesday is the last day to sign up at the discounted price. Go ahead and sign up now, while you're thinking about it!

Now on with this week's tip - drum roll please ... number 100! It's a sneak preview of one of the many features I will demystify on 15 March and it's a proven formula (pun intended ;-) to get more out of Excel by using its powerful database features. In Excel, a database is known as a list. An example of a database might be a detailed listing of your expenditures, including month, expenses classification, cost and so on. If you've designed the list so that the first row of the list contains column labels (so-called field names) and that all rows have similar items in the same column - avoiding blank rows and columns - the information can be sorted, filtered and manipulated without having to know how to write complicated formulas. Let's say you want to get totals for expenses incurred each month or by expense type.

Here's how:

1. Make sure the list is sorted by the column you want to subtotal (for example, Month or Classification). To do so, simply select any cell in that column and click on either the Sort Ascending or Sort Descending button on the Standard toolbar.
2. Click anywhere inside your list.
3. Choose the Data, Subtotals command.
4. In the Subtotal dialog box, which is subsequently displayed, select to Sum the column At each change in the Month or Classification field.
5. Click on the OK button.

Excel automatically displays the subtotals and a grand total at the end. It also "outlines" the list, which enables you to collapse the worksheet to show various levels or expand it to show all of the data. Simply click on any of the - or + signs to hide or show more detail. Alternatively, click on 1, 2 or 3 to see, respectively, 1). the grand total, 2). the subtotal as well as the grand total or 3). all of the data.

By the way, successful use of this feature depends on the accuracy of your data entry. We can make mistakes (after all, we're only human!) or if data entry is a team effort you are facing the "What Do We Call It Problem", such as "do we enter England, United Kingdom, UK, Great Britain or GB?" Well, astute readers of this series of weekly tips might remember you can restrict the entries that are allowed in a particular range of cells by specifying a predefined drop-down list. See tip # 65 of my tip archive for further details (http://www.roem.co.uk/tip_65.html)

Until next week.

PS I’m committed to sharing the best of what I know with others so please don’t keep me a secret. If you enjoyed today’s tip, please forward it to anyone you feel may benefit. Alternatively, feel free to reprint it (with full copyright and subscription information) in your newsletters and message boards.

Home   Training   Support   Clients   Contact Details   News   Disclaimer