Pivot Table Field Filters

Mar 6th, 2002, 09:34 PM

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

Mark W. · Mar 7th, 2002, 06:27 AM

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 ]

rvences · Mar 7th, 2002, 10:03 AM

Can you be more specific?

Mark W. · Mar 7th, 2002, 12:52 PM

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 ]

rvences · Mar 14th, 2002, 08:12 PM

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

Tommy Bak · Mar 15th, 2002, 02:09 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 6th, 2002, 09:34 PM	#1
Guest Posts: n/a	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

Mar 7th, 2002, 06:27 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]

Mar 7th, 2002, 10:03 AM	#3
rvences New Member Join Date: Feb 2002 Location: rvences Posts: 9	Can you be more specific?

Mar 7th, 2002, 12:52 PM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]

Mar 14th, 2002, 08:12 PM	#5
rvences New Member Join Date: Feb 2002 Location: rvences Posts: 9	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

Mar 15th, 2002, 02:09 AM	#6
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	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