Table sums

[pctuk]

Hi

This is a simple question, but it's sending me crazy!

I am new to Excel 2010, but am very experienced with 2003 (I skipped 2007). I really like the new table feature (which was in 2007 too). However..

I select my data->insert->table. Yes, my table has headers.
Tables tools->design->Add a total row

My table has, say, 10 columns (2000-2010). Adding a total row adds this to the rightmost column. The formula is: =SUBTOTAL(109,[2010]). Great.

Now, how do I copy this total across for the other columns? When I copy that cell to the next column, Excel doesn't seem to change the formula to =SUBTOTAL(109,[2009]) - it just copies =SUBTOTAL(109,[2010]).

There must be an easy way of solving this, but I can't work out what it is. Can anyone help me?

Thanks
Paul

 

[Andrew Poulsom]

If you select one of the blank cells in the total row, you can select eg Sum from the dropdown. For some reason Excel defaults to None for all except the last column.

 

[pctuk]

Thanks for the reply. I'd figured that out, but what about if I have a large number of columns? Surely there's a way to do this - really surprised copy and paste doesn't do it?

 

[pctuk]

Anyone?

 

[Andrew Poulsom]

Select the function you want for the first column. Then drag that to the right. Strange that fill works differently from copy.

 

[pctuk]

Thank you so much. Very strange how this works and copying pasting works. Anyway, thanks for solving this very annoying problem!

 

[pgc01]

Hi Paul

Getting back to your first post, you could have dragged (not copied) the formula to the left and it would adjust the column.