Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Here how you can do it.

  • Start out with your original subtotaled data

Original data with subtotals in Excel sheet

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

New data entered below original data

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

New data subtotaled below original data

  • 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.

New and old data merged into a single subtotal group

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.)

Entire data range with adjusted subtotal formula

And you're done!

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.