Tame your computer: calculated results


21-04-2005

Software training expert Karen Roem offers handy tips to help you 'Tame your computer' in her weekly series for Cambridge Network members.

This week she looks at a proven formula (pun intended!) to get more out of Microsoft Excel* by using one of 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.



Tame your computer:Design 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. Avoid blank rows and columns.



The information in a list 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.



Finally, 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, Excel lets you restrict the entries that are allowed in a particular range of cells by specifying a predefined drop-down list. See one of my previous tips for further details.



PS To delete the totals, choose the Data, Subtotals command, and click on the Remove All button in the Subtotal dialog box.







* Unless stated otherwise, these tips were written for Microsoft Office 2000. Most of what is covered however will also apply to earlier and later versions.





Roem logo



21 April 2005





Karen Roem offers software training and support through her company Roem Ltd.

Contact her by email Karen@roem.co.uk or visit her website at www.roem.co.uk.

 

Karen Roem
Microsoft Office-ionado

Roem Ltd – software training and support