Copying and Filling Table Totals

Suppose you have a table with a number of columns.

And suppose you wanted to sum the Num6 column. You’d select a cell in that column and use Alt+JT+T to add the totals row.

But Excel always sums the last column (that it’s able to) when it should really sum the one you’re on. That’s an easy enough fix

That won’t stop the last column from also being summed, but it’s easier to destroy than create. I’ll let that happen and go delete it if I don’t want it. But we’re not done yet. The craziness is just starting. By the way, I’m using Office 2010. If they fixed any of this stuff in later versions, bully for them, but I’m still not upgrading.

The formula to sum Num6 is: =SUBTOTAL(109,[Num6]). If I copy and paste that to Num5

The formula stays the same. That’s consistent with how copying formulas that use Structured Table Referencing (STR), but it really stinks in this case. If I want to sum all of the columns, I have to go select the appropriate aggregate function from the list. I could change the STR into normal cell references, but then I lose the table goodness.

One option is to select a cell in Num5 and press Alt+JT+T+Alt+JT+T. Turning the totals row off and then on again sums up for that column by calling the code I wrote above. The fact that this works means I should have check to see if there was already an aggregate in the cell before I overwrote it. That is, if I had a count formula in there and turned the total row off and then on again, I’d kill that row. If I fix that, this workaround doesn’t work. But it’s the right thing to do.

Well that doesn’t work. If I have =SUBTOTAL(103,[Num5]) in the cell and turn on and off the total row, it changes to =SUBTOTAL(109,[Num5]).

I think what’s happening here is that Excel tables have a good memory. Once I change the aggregate to sum, that becomes the default for that column. When you show totals for the first time, the event sequence is (with the Target in parentheses)

  1. Add the total row (A5:H5)
  2. Add the total label (A5)
  3. Aggregate the last column (H5)

When I re-show the total row after having changed the aggregate, the event code changes the default aggregate, and when the table restores the formula it’s using the that aggregate. Assume I’m in Num7 when I show totals the first time. Then I move to Num5, add a count, and hide and show totals.

Event Target Num5 Default Aggregate
Show Totals 1st time A5:H5 SUM
Add label A5 SUM
Aggregate Num7 (automatic) H5 SUM
Add Count to Num5 (manual) F5 COUNT
Hide Totals A5:H5 COUNT
Show Totals 2nd time A5:H5 COUNT
Change Event F5 SUM
Add label A5 SUM
Aggregate Num5 (automatic) F5 SUM
Aggregate Num7 (automatic) H5 SUM

The second-to-last change is Excel restoring the Num5 total from the last time. It doesn’t restore it to what it was, it restores it to whatever the last aggregate used was. Since I interjected some code between the adding of the row and the restoration of the aggregate, I changed the default for that column. At least I think that’s what happening.

2 thoughts on “Copying and Filling Table Totals

  1. “I could change the STR into normal cell references, but then I lose the table goodness.” …
    Or you could use your mouse and drag the formula over. More efficient than writing a bunch of code. Then we could have an International Structured Table Mouse Drag Day!


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.