Most valuable professional
  • Excel Book Excerpt

Excel Reduce Size 50% Before Sending

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Reduce Size 50% Before Sending

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.

LE10000916.jpg 

Figure 880 The data and the pivot cache.

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

LE10000917.jpg 

Figure 881 The workbook with only a pivot table and no data.

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

LE10000918.jpg 

Figure 882 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!

LE10000919.jpg 

Figure 883 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.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: