Why my spreadsheet is too slow and heavy?

[The Godfather]

Hi,

I have a very simple workbook which I record my daily expenses. I have a sheet for each month, and each sheet has some formulas in it. I have prepared an example sheet to give you an idea (I have changed the actual records and made some fictional ones). Here's an example:

http://imageshack.us/f/807/exampleo.jpg/

And here are the formulas running in each sheet:

http://imageshack.us/f/26/formulai.jpg/

On a typical month, I record only 100 to 150 lines of data. I have also limited the ranges of the formulas (as seen in the screenshot, they work until 204th line) to prevent excess calculation time. So far, I have 8 months of data and also a very simple summary sheet (equity movement sheet) which does not bring extra clumsiness to the workbook (only a couple of references and sum formulas).

It takes more than a minute to open the workbook. I cannot quickly and "fluently" copy/paste anything in it, I have to wait a couple of seconds. Saving the workbook also takes more than the usual. I have checked the size of it and it is 35 MB!

I cannot believe that such a simple workbook with too little data and a few formulas can be that painful. If somebody asked me to guess the size of the workbook, I'd say it should be 2,5-3MB at most, instead of 35. Could you please advise me some ways to make the workbook lighther?

Thanks,

The Godfather

 

[Smitty]

Welcome to the Board!

Most likely you have a bunch of unused ranges still being included in what Excel sees as the used range. Kind of like an Access database when you add a table; just because you delete it doesn't make it go away until you compact & repair.

See: http://www.contextures.com/xlfaqApp.html#Unused for clearing up bloat in Excel, similar to the Access compact & repair tool.

And: http://www.decisionmodels.com/ for some general tips on workbook efficiency.

HTH,

 

[The Godfather]

Hi Smitty,

I have deleted the rows below data for each sheet and it really worked. Now the workbook is 84 KB. Thank you very much for your response.

The Godfather

 

[Smitty]

I'd say 84Kb vs. 35Mb is a slight improvement. I don't even want to think about what that compression rate is as a percentage...

I'd keep Debra's UnusedRange code handy if this happens a lot.