Home

Pivot table filters reset when erasing or replacing data

%3CLINGO-SUB%20id%3D%22lingo-sub-1053404%22%20slang%3D%22en-US%22%3EPivot%20table%20filters%20reset%20when%20erasing%20or%20replacing%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1053404%22%20slang%3D%22en-US%22%3E%3CP%3EI%20created%20a%20spreadsheet%20to%20track%20sales%20data%20which%20contains%20multiple%20pivot%20tables.%26nbsp%3B%20I%20intend%20to%20save%20multiple%20copies%20of%20the%20sheet%20for%20individuals%20to%20use%2C%20then%20pull%20all%20the%20data%20into%20a%20master%20workbook.%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20problem%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhenever%20I%20erase%20or%20replace%20data%20the%20filters%20reset.%26nbsp%3B%20For%20instance%2C%20I%20have%20the%20data%20filtered%20by%20month.%26nbsp%3B%20When%20I%20erase%20the%20data%2C%20those%20filters%20go%20away%20and%20it%20no%20longer%20filters%20by%20month%20(instead%20showing%20all%20the%20data).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advice%20on%20how%20to%20retain%20the%20filters%20would%20be%20greatly%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1053404%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1053430%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20filters%20reset%20when%20erasing%20or%20replacing%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1053430%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20question%20is%20a%20bit%20unclear..%20Maybe%20you%20can%20upload%20a%20sample%20data%20for%20more%20clarify.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1053507%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20filters%20reset%20when%20erasing%20or%20replacing%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1053507%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F480501%22%20target%3D%22_blank%22%3E%40mikeychococakes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20don't%20add%20data%20to%20data%20model%20and%20don't%20use%20slicers%20it%20shall%20work.%20If%20something%20like%20this%3C%2FP%3E%0A%3CP%3EFiltered%20PivotTable%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20283px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160480i1B1D1D7299A4D834%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EErased%20data%20and%20refresh%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20272px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160481iBFB2DDD38198F592%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EUpdate%20data%20and%20refresh%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20271px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160482i86811B8ADB35653E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1053688%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20filters%20reset%20when%20erasing%20or%20replacing%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1053688%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F480501%22%20target%3D%22_blank%22%3E%40mikeychococakes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EWhy%20do%20you%20erase%20data%20in%20the%20first%20place%3F%3C%2FSTRONG%3E%20Part%20of%20the%20power%20of%20the%20pivot%20table%20and%20its%20filters%20is%20that%20you%20can%20keep%20adding%20more%20months%20of%20data%2C%20even%20into%20the%20next%20year(s).%20Just%20add%20%22YEAR%22%20as%20another%20level%20of%20filter.%20Doing%20this%20would%20enable%20you%20and%20your%20users%20to%20compare%20results%20from%20month%20to%20month%2C%20or%20same%20months%20but%20different%20years....and%20so%20forth.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20would%20need%20to%20%22Refresh%20Data%22%20whenever%26nbsp%3B%20the%20table%20is%20extended%2C%20but%20things%20like%20filters%20wouldn't%20be%20disappearing.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
mikeychococakes
Occasional Visitor

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. 

3 Replies
Hello,

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

@mikeychococakes 

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

Filtered PivotTable

image.png

Erased data and refresh

image.png

Update data and refresh

image.png

@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.

Related Conversations
Optimize Windows 10 PC reset using the cloud
HotCakeX in Windows Insider Program on
1 Replies
PivotTable : Unexpected behavior with 2 tables
CastyChichi in Excel on
2 Replies
Pulling data from one tab to another
krysphares in Excel on
6 Replies
Set Auto Refresh to Pivot Table as Source Data Changes
ratishkp in Excel on
2 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies