+ Reply to Thread
Results 1 to 3 of 3

Pivot Table filtering in templates

  1. #1
    Registered User
    Join Date
    11-03-2006
    MS-Off Ver
    365
    Posts
    63

    Pivot Table filtering in templates

    I have an Excel template file with two sheets. One is a pivot table, the other is the source sheet for the pivot data.

    If I filter the pivot table, clear out all values from the source data sheet, and then refresh all data; then, any specific filters I applied are still present in the pivot table sheet (even though the filtered values no longer exist in source!). Also, If I close the worksheet, add new data to the source, then, the old filters are still saved in the pivot table.

    Two questions:

    1) Why/How does this work? ;

    and

    2) Can I manually add pivot filters for items which aren't in the source data? For example, if my source sheet only lists DOGS in an "Animal" column, can I add a filter for CATS in a pivot table based on that source sheet
    Thanks!

    UPDATE: Sorry, it looks like the Pivot "Filter" field itself does not save previous filters. But, any data in the Row and Column Labels will still be present, and any filtering applied to those two fields will be saved even if the source is cleared.
    Last edited by Pepe Le Mokko; 02-09-2020 at 04:15 AM. Reason: Removed unnecesary title part

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Why/How does this work (Pivot Table filtering in templates)

    Try this:

    Right-Click on a Pivot Table cell
    -Pivot Table Options...Data...
    --Number of items to retain per field:
    ->Change from Automatic to None

    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    11-03-2006
    MS-Off Ver
    365
    Posts
    63

    Re: Why/How does this work (Pivot Table filtering in templates)

    Sorry, there wasn't actually any problem with the data being retained, actually, it turns out to be extremely beneficial for me to save the data

    I was just wondering what is going on here (I now see there is something called a Pivot cache where such things are stored).

    Also, I was just wondering if I could add any additional filter values for in the future when I re-import different data into the source sheet. I guess if I need to pre-filter specific values in the future, I can just:

    1) Add the value that I want to filter (in future data imports) to somewhere in an existing column in the Source data;
    2) Refresh Data
    3) Clear the Source Data and
    4) Re-save the workbook as a new template

    This seems to work good enough for now. It just feels very cobbled together and like i am doing something incorrectly. I was just wondering if there was a better way to do this. I guess this would involve manually accessing/updating the pivot cache (not really sure...).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 07-01-2019, 08:01 AM
  2. [SOLVED] How does the pivot table filtering in this file work?
    By WoodmanREI in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-20-2019, 01:51 PM
  3. Replies: 4
    Last Post: 04-01-2017, 03:41 AM
  4. [SOLVED] filtering a pivot chart / pivot table on x-axis values
    By Trebor777 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-20-2016, 01:28 PM
  5. Replies: 0
    Last Post: 07-29-2015, 09:41 PM
  6. [SOLVED] Filtering Data Pivot Table is Based On - Impact on Pivot Table
    By bbg22 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-17-2013, 06:34 AM
  7. Work on table while filtering
    By christchaaya in forum Excel General
    Replies: 5
    Last Post: 04-09-2013, 11:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1