In honor of the upcoming 4th of July weekend, I’ve declared this week to be Pivot Table week. What do Pivot Tables have to do with Independence day? I’ll let you know when I find out.
In the meantime, are you ‘that guy’ who keeps clogging everyone’s email with 20 megabyte files. I want to share a simple trick to cut the size of your Pivot Table workbooks in half.
Each pivot table report you create from a separate data source will create its own pivot Cache that will increase your file size. The increase in file size depends on the size of the original data source that is being duplicated to create the pivot Cache.
Of course, the benefit you get from a pivot cache is optimization. Any changes you make to the pivot table report, such as rearranging fields, adding new fields, or hiding items, are made rapidly and with minimal overhead.
The down side of the pivot cache is that is basically doubles the size of your workbook. So every time you make a new pivot table from scratch, you essentially add to the file size of your workbook.
Note that pivot tables which share the same pivot cache will also share calculated fields, calculated items, and groupings.
You can delete your source data and your pivot table will function just fine. After deleting the source data, saving will shrink the file.
The only functionality you will lose is the ability to “Refresh” the pivot data, as the source data is not there. But DataPig, how do I get back to the raw data if I delete it?
Simple. Just double click the intersection of the row and column totals. This will create a new sheet with all the data currently contained inside the pivot cache.
On this 4th of July weekend, you should check out Debra’s site www.contextures.com. She’s Canadian, but she’s smart like an American.