Reduce Size 50% Before Sending

January 18, 2023 - by Bill Jelen

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.

Build a pivot table with only Revenue in the Values area. The pivot table is two cells: Sum of Revenue in A3 and $6.7 Million in A4. Cell A4 essentially holds the summary of the data in A1:H564. — Figure 947. The data and the pivot cache.

I deleted the worksheet with the data. Now, the workbook appears to have text in only two cells.

Delete A1:H564 and send your co-worker only the 2 cells of the pivot table. — Figure 948. The workbook with only a pivot table and no data.

Here is a comparison of file sizes. The workbook from Figure 947 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.

The workbook with only the 2 cell pivot table is 23KB versus 55KB for the workbook with the data and the pivot table. — Figure 949. 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!

When your co-worker receives the workbook with just two cells, they double-click on cell A4 and all of the detail data will be inserted on a new sheet. — Figure 950. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Nastya Kvokka on Unsplash