PC Review


Reply
Thread Tools Rating: Thread Rating: 1 votes, 5.00 average.

Exclude data from Pivot Table based on filter?

 
 
Eric
Guest
Posts: n/a
 
      14th Oct 2008
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 With Quote
 
 
 
 
ShaneDevenshire
Guest
Posts: n/a
 
      14th Oct 2008
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 With Quote
 
 
 
 
Eric
Guest
Posts: n/a
 
      14th Oct 2008
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 With Quote
 
New Member
Join Date: Mar 2009
Posts: 1
 
      27th Mar 2009
This is definitely not the right place to post this, but I'm not allowed to be on forums at work and can't spend too much time navigating


I have an Excel 2003 question (for Windows XP). I have a pivot table with two page filters. If I apply filter 1, filter two remains unchanged.

Example:

Filter 1 - Continent - North America, Asia
Filter 2 - Country - Canada, US, China, India

If I set Filter 1 on North America, Filter 2 still displays all four countries, instead of just showing Canada and US. I believe Excel 2007 has this feature already built in (please correct me if I'm wrong) but how can I attain this feature in Excel 2003?

If the only way is to apply some sort of VBA code, please be very specific, i have never used code before in my life.

Thanks for the help
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
two pivot q's - get rid og getpivotdata and pivot based on pivot Light Microsoft Excel Misc 1 4th Dec 2009 02:07 AM
Combo box based on a table to exclude data from second table magicdds- Microsoft Access 4 12th Jul 2008 11:45 PM
Exclude pivot table data from pivot chart? =?Utf-8?B?Y29hbF9taW5lcg==?= Microsoft Excel Charting 4 17th Apr 2007 07:11 AM
Pivot table-How to exclude zero values from it. =?Utf-8?B?RGluZXNo?= Microsoft Excel Worksheet Functions 0 20th Feb 2006 07:39 PM
Pivot table, how do you exclude counting cells with formulas as a =?Utf-8?B?R3JlZyBCb2Jhaw==?= Microsoft Excel Worksheet Functions 4 4th Nov 2004 01:05 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:37 AM.