MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Spreadsheet Downsizing

Posted by Paul on August 28, 2001 1:11 PM

Two developers automated a spreadsheet and it is now over 41 Megs in size. Any ideas on how to shrink it down some?

Posted by Damon Ostrander on August 28, 2001 5:36 PM

Hi Paul,

One little-known reason why spreadsheets seem to grow even though the data on them doesn't is that when autofill is used to extend data, it also extends the cell formats. This can be a lot of data per cell, and isn't deleted when the cell contents are deleted. You have to do Edit -> Clear -> All (or in VBA range.Clear) to clear a cell entirely. A good check for wasted area on a spreadsheet is to do a Ctrl-End on the keyboard, which selects the cell at the lower right corner of the worksheet's used range (worksheet.UsedRange in VBA). This shows you how much of the spreadsheet Excel believes is in use, and is often MUCH larger than what the user believes is being used. After you have cleared unneeded cells, check the usedrange again, and repeat the process as necessary.

I hope this helps.