Excel inflated file size caused by PivotTables - How to reduce file size after removing all Pivots?

%3CLINGO-SUB%20id%3D%22lingo-sub-1586583%22%20slang%3D%22en-US%22%3EExcel%20inflated%20file%20size%20caused%20by%20PivotTables%20-%20How%20to%20reduce%20file%20size%20after%20removing%20all%20Pivots%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1586583%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20working%20on%20a%20quite%20large%20Excel%20model%20and%20performed%20some%20ad-hoc%20data%20analyses%20with%20the%20integrated%20Pivot%20Tables%20and%20Charts%20feature.%20This%20Increased%20the%20file%20size%20from%20~7MB%20to%20~12.5MB%20and%20impaired%20the%20model%20performance.%20Even%20after%20deleting%20the%20Pivot%20Tables%2FCharts%20the%20file%20size%20and%20poor%20performance%20remains%20unchanged.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20far%20as%20I%20know%2C%20the%20data%20analyzed%20in%20the%20Pivot%20Tables%20is%20stored%20in%20a%20separate%20chache%20that%20is%20attached%20to%20the%20Excel%20file%20by%20default.%20Also%2C%20after%20turning%20off%20this%20option%20and%20saving%20again%2C%20the%20file%20size%20is%20unchanged.%20Is%20there%20any%20possibility%20to%20remove%20the%20Pivot%20cache%20i.e.%20reducing%20the%20file%20size%20to%20the%20%22pre%22-Pivot%20level%20of%20~7MB%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%2C%3C%2FP%3E%3CP%3EFelix%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1586583%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1587012%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20inflated%20file%20size%20caused%20by%20PivotTables%20-%20How%20to%20reduce%20file%20size%20after%20removing%20all%20Pivo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1587012%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F758386%22%20target%3D%22_blank%22%3E%40felixws%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20how%20to%20manipulate%20with%20Pivot%20Caches%20is%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.myonlinetraininghub.com%2Fexcel-pivot-cache%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.myonlinetraininghub.com%2Fexcel-pivot-cache%3C%2FA%3E%2C%20but%20if%20you%20remove%20PivotTables%20caches%20also%20shall%20be%20removed.%20Perhaps%20something%20else.%20You%20may%20try%20Clear%20All%20on%20currently%20empty%20regions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1595690%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20inflated%20file%20size%20caused%20by%20PivotTables%20-%20How%20to%20reduce%20file%20size%20after%20removing%20all%20Pivo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1595690%22%20slang%3D%22en-US%22%3EThank%20you%20for%20the%20advice%20to%20Clear%20All%20empty%20regions%2C%20it%20definitely%20helped%20decreasing%20the%20file%20size!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1599763%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20inflated%20file%20size%20caused%20by%20PivotTables%20-%20How%20to%20reduce%20file%20size%20after%20removing%20all%20Pivo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1599763%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F758386%22%20target%3D%22_blank%22%3E%40felixws%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi all,

 

I was working on a quite large Excel model and performed some ad-hoc data analyses with the integrated Pivot Tables and Charts feature. This Increased the file size from ~7MB to ~12.5MB and impaired the model performance. Even after deleting the Pivot Tables/Charts the file size and poor performance remains unchanged.

 

As far as I know, the data analyzed in the Pivot Tables is stored in a separate chache that is attached to the Excel file by default. Also, after turning off this option and saving again, the file size is unchanged. Is there any possibility to remove the Pivot cache i.e. reducing the file size to the "pre"-Pivot level of ~7MB?

 

Any help is greatly appreciated!

 

Best,

Felix

3 Replies
Highlighted

@felixws 

In general how to manipulate with Pivot Caches is here https://www.myonlinetraininghub.com/excel-pivot-cache, but if you remove PivotTables caches also shall be removed. Perhaps something else. You may try Clear All on currently empty regions.

Highlighted
Thank you for the advice to Clear All empty regions, it definitely helped decreasing the file size!
Highlighted