Pivot table filters reset when erasing or replacing data

[mikeychococakes]

I created a spreadsheet to track sales data which contains multiple pivot tables.  I intend to save multiple copies of the sheet for individuals to use, then pull all the data into a master workbook. 

Here's the problem:

Whenever I erase or replace data the filters reset.  For instance, I have the data filtered by month.  When I erase the data, those filters go away and it no longer filters by month (instead showing all the data).

Any advice on how to retain the filters would be greatly appreciated. 

[#2]

Hello,

The question is a bit unclear.. Maybe you can upload a sample data for more clarify.

[#3]

@mikeychococakes 

If you don't add data to data model and don't use slicers it shall work. If something like this

Filtered PivotTable

Erased data and refresh

Update data and refresh

[#4]

@mikeychococakes 

Why do you erase data in the first place? Part of the power of the pivot table and its filters is that you can keep adding more months of data, even into the next year(s). Just add "YEAR" as another level of filter. Doing this would enable you and your users to compare results from month to month, or same months but different years....and so forth.

You would need to "Refresh Data" whenever  the table is extended, but things like filters wouldn't be disappearing.

[#5]

@mathetes 

Old thread I know but I have the same issue here. 

I have created an analysis tool which consists of one table of base data and around 80 pivot tables and associated charts which form a number. The Pivot tables largely reside on hidden tabs to power the charts on visible tabs. Those charts have had the filter buttons visibility removed for a clean look and feel. 

When the data is refreshed, the various filters that I've applied must be re-applied which is frustrating. 

Does anyone know a way that you can force a pivot filter to a specific value? I appreciate why it is losing its value (the removal of data means the filter doesn't know what options it has available to it) but there could be another way to construct the Pivot table which isn't reliant on the filter (or additional data sets)?

The ability to apply a filter with a formula would be ideal and extremely powerful. 

[#6]

I should mention I'm aware that I can extend the data, but for my use case that is not valid - the data must be replaced as data across the dataset will have changed (not just new rows to add on the end)

[#7]

It would be better to create an altogether new thread for your question. Excel has new features added since this thread (let us know whether you have the newest version or a 365 subscription)

[#8]

You're totally right, in fact I found a solution in the form of a data connection that uses power query to filter the data.

[#9]

Could you provide the linlk, please @David Leech 

[#10]

How did you solve it?