weird issue with pivot tables not clearing data

%3CLINGO-SUB%20id%3D%22lingo-sub-1380384%22%20slang%3D%22en-US%22%3Eweird%20issue%20with%20pivot%20tables%20not%20clearing%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1380384%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20existing%20pivot%20table%20data%20for%20a%20number%20of%20individuals.%26nbsp%3B%20They%20are%20included%20in%20the%20pivot%20table%20as%20rows.%26nbsp%3B%20Now%20I've%20taken%20that%20spreadsheet%20and%20created%20a%20copy.%26nbsp%3B%20I%20want%20to%20make%20a%20new%20spreadsheet%2C%20with%20the%20same%20fields%20and%20everything%2C%20but%20with%20a%20much%20shorter%20list%20of%20participants.%26nbsp%3B%20The%20problem%20I'm%20having%20is%20that%20the%20filters%20continue%20to%20have%20all%20of%20the%20old%20individuals%20listed%20when%20it%20no%20longer%20applies%20to%20this%20group.%26nbsp%3B%20I've%20refreshed%20the%20data%20several%20times%2C%20but%20they're%20still%20there.%26nbsp%3B%20I%20can%20delete%20and%20re-create%20the%20pivot%20table%2C%20but%20it's%20a%20lot%20of%20work%2C%20when%20I%20want%20to%20reuse%20this%20spreadsheet%20for%20multiple%20groups.%26nbsp%3B%20Has%20anyone%20run%20into%20this%20problem%20and%20know%20how%20to%20resolve%20it%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1380384%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1385079%22%20slang%3D%22en-US%22%3ERe%3A%20weird%20issue%20with%20pivot%20tables%20not%20clearing%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1385079%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F663607%22%20target%3D%22_blank%22%3E%40geekdad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E1.%20Click%20any%20cell%20in%20pivot%20table%2C%20and%20right%20click%2C%20choose%3C%2FSPAN%3E%3CSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EPivotTable%20Options%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bfrom%20the%20context%20menu%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E2.%20In%20the%26nbsp%3B%3CSTRONG%3EPivotTable%20Options%3C%2FSTRONG%3E%26nbsp%3Bdialog%20box%2C%20click%26nbsp%3B%3CSTRONG%3EData%3C%2FSTRONG%3E%26nbsp%3Btab%2C%20and%20then%20choose%26nbsp%3B%3CSTRONG%3ENone%3C%2FSTRONG%3E%26nbsp%3Bfrom%20the%26nbsp%3B%3CSTRONG%3ENumber%20of%20items%20to%20retain%20per%20field%3C%2FSTRONG%3E%26nbsp%3Bdrop%20down%20list%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E3.%26nbsp%3BAnd%20then%20click%26nbsp%3B%3CSTRONG%3EOK%3C%2FSTRONG%3E%26nbsp%3Bto%20close%20the%20dialog%20box%2C%20and%20refresh%20the%20pivot%20table%2C%20now%2C%20when%20you%20click%20the%26nbsp%3B%3CSTRONG%3EFilter%3C%2FSTRONG%3E%26nbsp%3Barrow%2C%20the%20old%20item%20has%20been%20cleared%20at%20once.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHope%20this%20helps.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have an existing pivot table data for a number of individuals.  They are included in the pivot table as rows.  Now I've taken that spreadsheet and created a copy.  I want to make a new spreadsheet, with the same fields and everything, but with a much shorter list of participants.  The problem I'm having is that the filters continue to have all of the old individuals listed when it no longer applies to this group.  I've refreshed the data several times, but they're still there.  I can delete and re-create the pivot table, but it's a lot of work, when I want to reuse this spreadsheet for multiple groups.  Has anyone run into this problem and know how to resolve it?

1 Reply
Highlighted

Hello @geekdad 

 

1. Click any cell in pivot table, and right click, choose PivotTable Options from the context menu,

2. In the PivotTable Options dialog box, click Data tab, and then choose None from the Number of items to retain per field drop down list,

3. And then click OK to close the dialog box, and refresh the pivot table, now, when you click the Filter arrow, the old item has been cleared at once.

 

Hope this helps.