PPRuNe Forums - View Single Post - Excel Sub Totals
View Single Post
Old 28th Oct 2004, 10:37
  #2 (permalink)  
spork
 
Join Date: Jul 2003
Location: Frimley, Surrey.
Posts: 182
Likes: 0
Received 0 Likes on 0 Posts
The following process inserts sub-totals automatically for you. It’ll save you going to each block of data and putting in a "=SUM(nnnnn)" formula to sub-total the data, which I think is what you’re talking about.

Firstly, your data should have unique column headings for each column. Place the cellpointer anywhere within the data, and sort your data by the column for which you want the subtotals. Keeping the cellpointer within the data, on the Data menu, click Subtotals. In the "At Each Change In" box, select the column that contains the groups for which you want subtotals. (This should be the same column by which you sorted your list)

In the "Use Function" box, select the function you want to use to summarize the data. (In most cases this would be "SUM") In the "Add Subtotal To" box, select, by ticking the box, the column (or columns) that contain the values for which you want subtotals. (Scroll up or down as necessary)

Click on [OK]. (If the database is very large, the subtotalling process may take some time – time for coffee) When the subtotals are complete, the screen will have an extra area to the left to allow you to collapse and expand the subtotalling. Typically, clicking on the "2" will collapse the data to just the subtotal lines, and a subsequent click on the "3" returns the detail.

It’s easy if you want to remove the subtotalling, on the Data menu, click Subtotals again, and click the [Remove All] button.

If this isn't clear, just come back...
spork is offline