Excel Sub Totals
Thread Starter
Join Date: Feb 2003
Location: Io
Posts: 420
Likes: 0
Received 0 Likes
on
0 Posts
Excel Sub Totals
I have a spreadsheet that runs into three pages. I know how to repeat columns in the printed version and also how to freeze panes for the screen, but I need to know if I can show sub totals automatically on the printed and screen versions without having to manually put them in?
I hope this makes some sort of sense.
Maxflyer
I hope this makes some sort of sense.
Maxflyer
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...
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...
