+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : Subtotal with Pivot Table

  1. #1
    Registered User
    Join Date
    09-14-2010
    Location
    Miami, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Subtotal with Pivot Table

    I am trying to take subtotals off of certain Field Columns in a Pivot Table I am working with, however, it seems that the subtotal function in Excel 2007 is either all or nothing. Any way to turn subtotals off for a specific field?

    Just to paint a picture, My pivot shows loan numbers as a row and then the data fields consist of balance, int. rate, variance and other some other stats. I need the balances subtotaled but I do not want the interest rates subtotaled.

    Any help would be greatly appreciated.

    Thanks, Alex

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,476

    Re: Subtotal issue with Pivot Table

    If you use the Subtotals button on the Pivot table tools > Design > Layout > Subtotals control then yes it applies to all fields.

    If you select a cell in a field and right click you will get the option to remove/apply the subtotal for the specific field.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    09-14-2010
    Location
    Miami, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Subtotal issue with Pivot Table

    Quote Originally Posted by Andy Pope View Post
    If you use the Subtotals button on the Pivot table tools > Design > Layout > Subtotals control then yes it applies to all fields.

    If you select a cell in a field and right click you will get the option to remove/apply the subtotal for the specific field.
    Andy, are you using Excel 2007 or an older version? I am using 2007 but I am not getting the option to turn off subtotal when right clicking a cell in the field. Any other ideas?

    I appreciate your help.

    -Alex

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,476

    Re: Subtotal with Pivot Table

    Yes I tested in xl2007.

    What version is xl2009?

    Post example workbook.

  5. #5
    Registered User
    Join Date
    09-14-2010
    Location
    Miami, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Subtotal with Pivot Table

    I use version 2007....sorry I need to change my profile.

    Anyways, when I right click on a field header the closest option I have is called "Value Field Settings".
    The only 2 tabs under this option are "Summarize By" and "Show Values As".
    Any other place that I disable Subtotals from disables them for the entire
    table, not just that one column. I'm going to see if I can figure out how to post an example and maybe it will be easier for you to see.

  6. #6
    Registered User
    Join Date
    09-14-2010
    Location
    Miami, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Subtotal with Pivot Table

    File should be attached. The pivot is shown underneath with a sample of the data. Im trying to remove subtotals from the pivot table in only Columns D and E (Note Int Rate current and Note Int rate prior).

    Thanks a bunch.
    Attached Files Attached Files

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,476

    Re: Subtotal with Pivot Table

    The example helps explain the issue.

    Subtotals apply to row fields and when applied they apply to all the columns in the Values area.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1