Exclude data from Pivot Table based on filter?

Discussion in 'Microsoft Excel Misc' started by Eric, Oct 14, 2008.

  1. Eric

    Eric Guest

    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.
     
    Eric, Oct 14, 2008
    #1
    1. Advertisements

  2. 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.
     
    ShaneDevenshire, Oct 14, 2008
    #2
    1. Advertisements

  3. Eric

    Eric Guest

    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.
     
    Eric, Oct 14, 2008
    #3
  4. Eric

    xo0will0ox

    Joined:
    Mar 27, 2009
    Messages:
    1
    Likes Received:
    0
    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
     
    xo0will0ox, Mar 27, 2009
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    1
    Views:
    763
    Guest
    Jul 6, 2006
  2. Guest

    Exclude zero values from pivot table

    Guest, Aug 4, 2006, in forum: Microsoft Excel Misc
    Replies:
    0
    Views:
    579
    Guest
    Aug 4, 2006
  3. Guest
    Replies:
    1
    Views:
    411
    Debra Dalgleish
    Sep 26, 2007
  4. Guest

    Pivot Table exclude negative values from sum

    Guest, Nov 18, 2007, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    2,489
    Guest
    Nov 19, 2007
  5. Light
    Replies:
    1
    Views:
    419
    Jon Peltier
    Dec 4, 2009
Loading...

Share This Page