Excel Spreadsheet Downsizing

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.

Damon