+ Reply to Thread
Results 1 to 12 of 12

Pivot Issue, want SOME Row items values on Pivot but all in total...

  1. #1
    Registered User
    Join Date
    08-30-2005
    Posts
    44

    Pivot Issue, want SOME Row items values on Pivot but all in total...

    This is a good one. Lets say I have a pivot that shows Location, Dept and Total Sales by month. So Location and Dept are in the row area, Period is in the column area and Total Sales are summed in the page area.

    Well, they decide they do not want to see Dept: 999 on the pivot. Thats easy, just click the dropdown and uncheck it....they are amazed at my ingenuity!

    Well, they then notice Dept: 999 is no longer included in the Location total or grand total....makes sesne to me I say!

    Well, they then specify that Dept: 999 should NOT appear on the pivot but SHOULD be included in subtotals / totals.

    I AM STUCK!!! -- not so ingenious I guess!

    Any ideas!!!!!!!!

  2. #2
    Registered User
    Join Date
    08-30-2005
    Posts
    44
    Anyone?

  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    Not possible in Excel...yet.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    08-30-2005
    Posts
    44
    I think Excel is due for a mjor overhaul...and I do not mean that vista crap either...

    I also need to be able to do percentages at the subtotal level.....

  5. #5
    Forum Contributor
    Join Date
    01-23-2006
    Posts
    194
    How about using "group by" and creating a group called "Unassigned" or similar which includes Dept 999 (and hopefully at least one other)
    The totals still balance but "Unassigned" includes Dept 999 and any others you don't want to separately list.

  6. #6
    Registered User
    Join Date
    08-30-2005
    Posts
    44
    I'll look into that!

  7. #7
    Forum Contributor
    Join Date
    05-26-2008
    Posts
    122
    theres no way to do it in pivot table however i think u can wiing it this way

    assuming ur pivot table dat is coming from another excel sheet.

    at the end of the data.. last row
    add a dept called GRAND TOTAL and use the sum forumla to calculate the totals of each column in the last row

    now when you go back to ur pivot table

    the grand total mite be in the middle.. jus use the pivot table> order settings to move it at the very bottom

    remove the grand totals from the pivot table >options

    bold the grand total row.. now if you are using the subtotal option.. u mite get two rows at the bottom looking the same

    jus hide one of the rows and there

    ur grand totals wont change now even if u filter out any company..\

    the only pain is that you have to do that last grand totals row every time u update ur raw data.. but i think will surely impress ur boss


    That do the trick??

  8. #8
    Registered User
    Join Date
    08-30-2005
    Posts
    44
    yes and no...they want to be able to manipulate the pivot having the GRAND TOTAL thus change. Example, they may want to filter on a particular location....so they would see just say location 'A', all depts except 999 but still have 999 included in the total or grand total for location A.

  9. #9
    Registered User
    Join Date
    08-30-2005
    Posts
    44
    the GROUP thing did not work for me...now I have a group they do not wish to see, when I hide it the totals change.

    The best I could come up with was to Click just to the left of Dept 999 so all 999s are highlighted in the pivot....then set the font to white so they disappear...though there is still an extra blank line, it does not look too bad since 999 was at the end......not exactly what I was hoping for but.....

    I still need a way to get % of subtotal...likely can not do that either!

  10. #10
    Forum Contributor
    Join Date
    05-26-2008
    Posts
    122
    just hide the blank row

  11. #11
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561
    manny, that won't work if anyone wants to "use" the pivot table as the hidden rows are tied to the sheet while the pivot is only anchored to one point

    Depending on how necessary this really is (seems a bit sledgehammer to nut for me) we could write a worksheet change macro which re-hides rows with Dept 999 every time you change something on the sheet. Could easily backfire though, if someone puts any other info on your pivot table sheet.
    Last edited by Cheeky Charlie; 08-28-2008 at 07:16 PM. Reason: truly special grammar/spelling action

  12. #12
    Forum Contributor
    Join Date
    01-23-2006
    Posts
    194
    "I still need a way to get % of subtotal...likely can not do that either!"
    - see attached .. may help
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 03-21-2008, 04:35 PM
  2. Replies: 2
    Last Post: 12-15-2006, 12:05 PM
  3. Pivot Tables - Removing old dropdown items
    By jerredjohnson in forum Excel General
    Replies: 1
    Last Post: 12-06-2006, 12:28 AM
  4. eliminate the blank total in my pivot table
    By carsto in forum Excel General
    Replies: 3
    Last Post: 11-30-2006, 04:19 AM
  5. Pivot table running total
    By Avner in forum Excel General
    Replies: 4
    Last Post: 11-23-2006, 12:08 PM

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