Thursday, March 31, 2005

Creating Subtotals in Excel

To create subtotals you need to ensure the following parameters are correct.

1 You need to have a database (fields and records or columns and rows) filled with data. For example this could be a list of stock items, their cost value, quantity on hand and total value on hand (Cost x Qty). They might be categorised into product types and locations.
Eg 6 columns named Item Name, Item Type, Location, Cost Value, Qty on Hand, Total Value
2 The database needs to have a header row with the header labels in it as per the example above.
3 The data needs to be sorted by the subtotal fields. In other words if you were going to subtotal this example by each Item Type, then you would need to sort by Item Type. Further sorting by any other columns is entirely optional in this case. However to make the second part of the exercise work sort also by Location. Thus the data is ordered by Item Type, and then within each Item Type sorted by Location.
4 Click anywhere in the data and and then select Data | Subtotals.
The subtotal wizard will automatically recognise the headings and give you the option of selecting which field you want to subtotal by. In this case select the following:
- Item Type in the box labeled At each change in,
- Sum In the Use Funtion box,
- In the Add Subtotal to ensure that the Qty and Total Value fields are checked. In other words check every field that you want a subtotal for.
- Leave Replace current subtotals and Summary below data checked and optionally select to Page break between groups.
- Select OK and the subtotals will be created. Note that the the subtotal rows have been inserted using the Subtotal function. On the left hand side the grouping lines will have been added with the outline levels indicated at the top.

For a more complex subtotal, eg by location as well simply click Data | Subtotal again.
- This time turn off the Replace Current Subtotals,
- Ensure that you are subtotaling at each change in Location.
- Selecting OK will add another level of outline and subtotals for each location within each item type.

An example is shown below.


Posted by: Jethro | 2:32 AM |