Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Pivot Table Field Filters

  1. #1
    Guest

    Default

    On occasion I create pivot tables which have several unique values (>100) in each field. I often need to filter my pivot table for only one or two unique values in a certain field. The only way to do this is to manually "uncheck" all the unique values I want filtered out of my pivot table. This can be very time consuming when you have several unique values. I found that using the down arrow and spacebar (on my keyboard) allows me to manually "uncheck" the boxes faster, however, this is still manual. Does anybody know how to shorten this process using VBA? Is their another way?

    Thanks,
    RVences

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Consider creating a grouped the field with numerious values, and then using the newly created grouping as a PAGE area field.

    [ This Message was edited by: Mark W. on 2002-03-07 06:27 ]

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    rvences
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you be more specific?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay, suppose your data is...

    {"Field1";4;9;4;1;9;2;6;6;6;2}

    ...and you've created a PivotTable with Field1 in the ROW area and Count of Field1 in the DATA area. You only want to report 2s and 6s, but you don't want to Hide the others. In the PivotTable drag the 6 row (by its border) so that is directly beneath the 2 row. Select the cells containing 2 and 6. Right-click and choose Group and Outline | Group... from the popup menu. Change "Group1 to something meaningful (I'll use "2s & 6s"). Double click the "Field1 2" field button and rename it too (I'll use "Select"). Drag 'Select' to the Page AREA and choose "2s & 6s".

    [ This Message was edited by: Mark W. on 2002-03-07 13:25 ]

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    rvences
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the tip on grouping, however, I still have to manually drag the fields I want "filtered" next to each other. This is more of a challenge then just unchecking the boxes manually. I believe some VBA code is needed to perform this task. I do appreciate your help.

    Thanks,
    rvences

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Place a headline in the cell just rigth to the pivottable headlines.
    While being in this field, set autofilter on.
    Now your pivottable is a member of the autofilter and yuo can filter as you wont to.
    regards Tommy

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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