Results 1 to 5 of 5

Bring Only Filtered Data to Pivot Table

This is a discussion on Bring Only Filtered Data to Pivot Table within the Excel Questions forums, part of the Question Forums category; How do you retain the data that is filtered in the main worksheet (we filter on 5 or more columns) ...

  1. #1
    Board Regular
    Join Date
    Oct 2008
    Location
    Phoenix
    Posts
    62

    Default Bring Only Filtered Data to Pivot Table

    How do you retain the data that is filtered in the main worksheet (we filter on 5 or more columns) when creating a Pivot Table?

    Are we having trouble because we are using a Shared Workbook?

    Doesn't the administrator (first person in) have rights to make all changes?

    If Shared Workbook restricts this, how can we get around this limitation?


  2. #2
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,232

    Default Re: Bring Only Filtered Data to Pivot Table

    Hello marylynn and Welcome to the Board!

    I'm not sure I entirely understand what you're trying to do, but here goes...

    Let's say your dataset cover Cells A1:E100. If your Pivot Table was built using these Cells as its source data, it doesn't matter whether you then filter on this source data - your Pivot Table will still pull in all of the information held there.

    Is there a reason you're not you're filtering on your source data and not within the Pivot Table itself?

    Matty

  3. #3
    Board Regular
    Join Date
    Oct 2008
    Location
    Phoenix
    Posts
    62

    Default Re: Bring Only Filtered Data to Pivot Table

    Thanks for the welcome!

    Our main spreadsheet contains 52+ columns where we filter out data from columns that we do not need in the pivot table. We want to keep the pivot table a simple summary, uncluttered if possible.

    I saw on another thread that Pivot Tables may not refresh with Shared Workbooks. Is bringing filtered data over to pivot tables another limitation of Shared Workbooks?

    Thank you.

  4. #4
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,232

    Default Re: Bring Only Filtered Data to Pivot Table

    Hello marylynn,

    I can't say I have ever used a Pivot Table within a shared Workbook, but I suspect problems will arise if you attempt to refresh the data when another individual is in that Workbook, potentially updating data that the PT is (trying) to use.

    Another trick I have recently learned is to use MS Query (which is built into Excel) to filter a large dataset so that your PT only displays the data you want to see.

    Have a look here:

    http://www.exceluser.com/explore/msquery1_1.htm

    Hopt this helps.

    Matty

  5. #5
    Board Regular
    Join Date
    Oct 2008
    Location
    Phoenix
    Posts
    62

    Default Re: Bring Only Filtered Data to Pivot Table

    Thanks, Matty. I will look into this next week.

    Bye for now...

Tags for this Thread

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
  •  


DMCA.com