Forum Discussion
Pivot table filters reset when erasing or replacing data
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.
I have the same issue. I need to export data from my company's database and the previously existing data could have changed (as sales comments, proposal status, and even budgets get updated months later).
I need to replace all data each week, and I need all 12 of my pivot tables to retain their filter settings.
- 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)
- 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.
- How did you solve it?
