Can you "Deflate" Excel files?

[Guest]

Hello,

I have been noticing that a particular excel file that I've been often
working with is getting "Inflated" (for lack of a better term), meaning that
it seems to be getting dis-proportionately larger since the amound of data
added to it does not correlate to it's increased size.

Does anyone know of any way to "Deflate" it?

Thanks in advance for your replies.

Phil.

 

[Bernie Deitrick]

Phil,

Try Rob Bovey's code cleaner:

http://www.appspro.com/Utilities/CodeCleaner.htm

HTH,
Bernie
MS Excel MVP

 

[Otto Moehrbach]

Phil
The most common cause of an inflated file is that Excel 'thinks' the
file is larger than it actually is. For instance, say you have data in the
range A1:M200. It's your file and you look at it and that is the range of
your data.
But you do Ctrl - End and Excel jumps to cell AC6000. That is the cell
that Excel thinks is the last cell of your data. Hence Excel thinks your
data range is A1:AC6000. That makes for a file that is way larger than what
you actually have.
Go to every sheet in your file and see what the last cell actually is,
then do Ctrl - End and see what Excel says is the last cell of your data.
If the difference is significant, then you have to reset "the last cell".
How do you do that?
Here is how. Say your actual used range is A1:M200. Scroll to row 201.
Select that row (the row, not a cell in the row). Do Ctrl - Shift - Down
Arrow. This selects every row from row 201 down to the bottom of the sheet.
Right-click in the colored area and click on Delete. Now select Column N.
Do Ctrl - Shift - Right Arrow. Right click in the colored area and click on
Delete.
Do that for every sheet in the file.
Save the file.
Look in Explorer and see the size of the file.
There ways to automate this if you have a lot of sheets in your file. HTH
Otto

 

[Guest]

Hello Otto,

That did the trick!

Thanks.

Phil
The most common cause of an inflated file is that Excel 'thinks' the
file is larger than it actually is. For instance, say you have data in the
range A1:M200. It's your file and you look at it and that is the range of
your data.
But you do Ctrl - End and Excel jumps to cell AC6000. That is the cell
that Excel thinks is the last cell of your data. Hence Excel thinks your
data range is A1:AC6000. That makes for a file that is way larger than what
you actually have.
Go to every sheet in your file and see what the last cell actually is,
then do Ctrl - End and see what Excel says is the last cell of your data.
If the difference is significant, then you have to reset "the last cell".
How do you do that?
Here is how. Say your actual used range is A1:M200. Scroll to row 201.
Select that row (the row, not a cell in the row). Do Ctrl - Shift - Down
Arrow. This selects every row from row 201 down to the bottom of the sheet.
Right-click in the colored area and click on Delete. Now select Column N.
Do Ctrl - Shift - Right Arrow. Right click in the colored area and click on
Delete.
Do that for every sheet in the file.
Save the file.
Look in Explorer and see the size of the file.
There ways to automate this if you have a lot of sheets in your file. HTH
Otto