Excel 2013: Table with totals row. Add 'sum' option to each column 2

[pendle666]

Hello

I've a spreadsheet with about 30 columns of figures.

I've created my table, and what I need to do is put the total of each column at the bottom.

When creating table, I ticked the Totals row option which has given me a Total dropdown in A438, but I need the totals in B438, C438 etc also.

I tried just copying across, but the formula remains the same as the first Total - eg SUBTOTAL(109,[AccomAllow])

Because this is a large spreadsheet I'm looking at creating a macro to format it all for me, but I can't even get the totals for each column without selecting each cell separately.

I hope this makes sense as to what I'm trying to do.

thank you

thank you for helping

____________
Pendle

 

[combo]

Add module to any open workbook and paste the code:

Sub AddSumsToCurrentTable()
With Selection.ListObject
    .ShowTotals = True
    For Each lc In .ListColumns
        lc.TotalsCalculation = xlTotalsCalculationSum
    Next lc
End With
End Sub

Select any cell within the table you need to add sums to and run the macro.
It add sums in each column. If you don't need sums in each column, add some interaction or condition to skip defined columns.

combo

 

[pendle666]

great, thanks I'll try that

thank you for helping

____________
Pendle

 

[SkipVought]

I tried just copying across, but the formula remains the same as the first Total - eg SUBTOTAL(109,[AccomAllow])

Yes and No! Depends on your METHOD of COPY n PASTE.

When you create a Structured Table and check Total Row, a SUBTOTAL() formula is placed at the bottom right-hand of the table.

If you SELECT that cell and DRAG the SELECTION FILL HANDLE across (to the right) and each column’s header will appear in the formula.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

And so it does!


Thank you!

thank you for helping

____________
Pendle