Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default Exclude data from Pivot Table based on filter?

Does Excel 2003 offer any option to exclude data from a pivot table, when the
source data is a table that has been filtered?

Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,344
Default Exclude data from Pivot Table based on filter?

Hi,

If you are asking - if the source of a pivot table has an auto filter (or
advance filter), can I turn off the display of those items in the pivot
table? The answer is yes/no.

No - there is no built in option to say don't display filtered data from the
source.
Yes - you may be able to duplicate the situation

Remember in a pivot table when the Page field is filtered the filtered items
are not displayed by default. So what one needs to do is place the Auto
Filtered fields of the source data into the Page fields. You can then filter
on the page field in the same manner you do in the source data.

A couple of points - 1. if you want the filtered field in the row, or column
area it technically can't be in the page area. In that case create a second
field in the source which is a duplicate of the filtered field and place this
one in the page area.
2. if you want the filter to automatically apply to the pivot table when you
apply it to the data source you should consider VBA. You can code a change
event to handle this.
--
Thanks,
Shane Devenshire


"Eric" wrote:

Does Excel 2003 offer any option to exclude data from a pivot table, when the
source data is a table that has been filtered?

Thanks.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default Exclude data from Pivot Table based on filter?

Shane - Thanks for your help.

"ShaneDevenshire" wrote:

Hi,

If you are asking - if the source of a pivot table has an auto filter (or
advance filter), can I turn off the display of those items in the pivot
table? The answer is yes/no.

No - there is no built in option to say don't display filtered data from the
source.
Yes - you may be able to duplicate the situation

Remember in a pivot table when the Page field is filtered the filtered items
are not displayed by default. So what one needs to do is place the Auto
Filtered fields of the source data into the Page fields. You can then filter
on the page field in the same manner you do in the source data.

A couple of points - 1. if you want the filtered field in the row, or column
area it technically can't be in the page area. In that case create a second
field in the source which is a duplicate of the filtered field and place this
one in the page area.
2. if you want the filter to automatically apply to the pivot table when you
apply it to the data source you should consider VBA. You can code a change
event to handle this.
--
Thanks,
Shane Devenshire


"Eric" wrote:

Does Excel 2003 offer any option to exclude data from a pivot table, when the
source data is a table that has been filtered?

Thanks.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Filter lines containing pivot table and non pivot table data Grover Excel Worksheet Functions 0 September 24th 07 07:20 PM
Pivot table data filter Neil Excel Discussion (Misc queries) 4 July 24th 07 03:09 PM
Exclude pivot table data from pivot chart? coal_miner Charts and Charting in Excel 4 April 17th 07 07:11 AM
How Do I Do an Exclude Filter based on a Named Range? Dawg House Inc Excel Worksheet Functions 8 April 11th 06 02:49 AM
Filter based on Pivot table michaelp Excel Worksheet Functions 3 December 7th 05 01:48 AM


All times are GMT +1. The time now is 03:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"