Problem: I have to upload a file over my aunt's 56 baud modem. Can I reduce the size of the workbook?
Strategy: When you save a workbook with a pivot table, Excel saves the data on the worksheet, plus the data in the pivot table cache. You can't actually delete the pivot table cache, but you can delete the data on the worksheet.
Here is a workbook that contains data and a pivot table. You are seeing two windows of the same workbook.
-
The data and the pivot cache.
I deleted the worksheet with the data. Now, the workbook appears to have text in only two cells.
- The workbook with only a pivot table and no data.
Here is a comparison of file sizes. The workbook from Fig 909 is 55K. The workbook without the data worksheet is 23K. The workbook from the previous topic, the one with two pivot tables and two pivot caches is 79K.
- File size is reduced by having only the pivot cache.
OK, so I've proved that you can save space in a workbook by deleting the data. What good is that?
Say that you transfer that file over the modem, then you get back to work and open the file with only the 2-cell pivot table.
Excel will ask if you want to enable the data connection to the invisible cache.
Cell A4 in the pivot table is essentially a grand total of all rows in the pivot table. Double-click that cell and Excel will bring put the contents of the pivot table cache into a new worksheet in the workbook!
- Double-click A4 and all the data is inserted.
Gotcha: While I've used this trick a dozen times, it is always unnerving to delete your data. You should make a backup copy of the entire workbook before deleting the data. You never know if the pivot table cache would become corrupt.