How to add new data to the table with existing subtotals?

Consider this example:

http://office.microsoft.com/en-us/excel-help/calculating-subtotals-and-working-with-levels-in-excel-HA001109798.aspx

Say after calculating subtotals by week I then want to add sales data for more weeks (initially we had Week1...Week5 and I would like to add Week6, Week7, Week8) and have them sub-totaled the same way BUT I don't want any changes to previously calculated subtotals.

What is the proper way to do this (if it is even possible)?

The only way I found is this (I use Excel 2007): select all cells, press "Subtotal", press "Remove All", then re-sort by "Week" and generate sub-totals for the whole table from scratch. Problem with this approach is that it recalculates existing sub-totals which is what I want to avoid.

-

Here how you can do it.

• Start out with your original subtotaled data

• Immediately below that data enter the header labels you used for the first subtotal as well as your new data.

• Select the new data, including header labels and run a subtotal, so that you now have two sets of subtotals.

• Delete the header label row and the grand total row for the second set. The two groups of data - old and new - will now be grouped together.

One final thing to do. As shown in cell B2 total above, the grand total only includes the original group of numbers. Just edit the subtotal formula so that it extends to the bottom of the new data. (Assuming you have more than one column of data that is being subtotaled, you'll want to copy the edited formula across for the other columns.)

And you're done!

-
Yes, this works. I really appreciate you taking time to post screenshots. To nitpick: if new data are not entered in correct order (by week) one needs to sort new data before totaling them. – Joe Schmoe Jun 26 '13 at 21:58
Absolutely correct, sorting is needed! – chuff Jun 26 '13 at 22:35