Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Totals (2003)

    See attached. I'd like to know how to add totals within the sample "pivot table" data. I would like to add each time there is a name change in column A.

    How do I do???

    Thanks.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Totals (2003)

    You should start from the source data instead of adding the subtotals afterwards.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table Totals (2003)

    In the pivot table, dbl-click the field name (G/L Account) and in the dialog box select the "Automatic" under the "Subtotals" area

    [You can't do in your example file, since it is not a pivot table....]

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Totals (2003)

    I do this (automatic under subtotals) and nothing happens. Ideas?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Totals (2003)

    The data in the workbook that you attached does *not* have the correct structure for subtotals or for the data source for a pivot table. The data in column A should be repeated for this.

  6. #6
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Totals (2003)

    I try the the comments in the actual pivot table and I got no changes. I was wanted to do a subtotal at every change in the G/L account.

    I double clicked on G/L account, automatic under subtotals and then closed out of dialog. It doesn't calculate a subtotal at each change in G/L account.

    What do you mean by the "data in column A should be repeated for this."

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Totals (2003)

    Data to be used for subtotals or for a pivot table should be in a database-like table format, like this:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td>Region</td><td>Quarter</td><td>Amount</td><td align=center>2</td><td>North</td><td>Q1</td><td align=right>120</td><td align=center>3</td><td>North</td><td>Q2</td><td align=right>115</td><td align=center>4</td><td>North</td><td>Q3</td><td align=right>108</td><td align=center>5</td><td>North</td><td>Q4</td><td align=right>111</td><td align=center>6</td><td>East</td><td>Q1</td><td align=right>133</td><td align=center>7</td><td>East</td><td>Q2</td><td align=right>128</td><td align=center>8</td><td>East</td><td>Q3</td><td align=right>131</td><td align=center>9</td><td>East</td><td>Q4</td><td align=right>126</td></table>
    instead of like this:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td>Region</td><td>Quarter</td><td>Amount</td><td align=center>2</td><td>North</td><td>Q1</td><td align=right>120</td><td align=center>3</td><td align=right>*</td><td>Q2</td><td align=right>115</td><td align=center>4</td><td align=right>*</td><td>Q3</td><td align=right>108</td><td align=center>5</td><td align=right>*</td><td>Q4</td><td align=right>111</td><td align=center>6</td><td>East</td><td>Q1</td><td align=right>133</td><td align=center>7</td><td align=right>*</td><td>Q2</td><td align=right>128</td><td align=center>8</td><td align=right>*</td><td>Q3</td><td align=right>131</td><td align=center>9</td><td align=right>*</td><td>Q4</td><td align=right>126</td></table>

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table Totals (2003)

    Can you attach a sample workbook with dummy data and the pivot table derived from it, so we can discuss with a better idea of what you have and want?

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Totals (2003)

    I attached file with a short highlighted note on the Pivot table as to what I am trying to do.
    Attached Files Attached Files

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table Totals (2003)

    The problem is that your data is not setup as a database to do that. You want to sum across several columns and to XL that means that they should not be subtotaled as you subtotal identical type of data which is in the same column. Different columns means different fields. You need to reorganize your data.

    There is one rearrangement in the attached. It seems that you could eliminate "xQ" rows since they seem to be sums of the data. If you make a column of Dates and perhaps another column with OP, LE, etc it can summarize easier. I did not do this as I was unsure what all the breakdowns are exactly and it wasn't required to give you want you wanted...

    Steve
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •