Slow to open - large file ???

[sbrazil]

Hi,
I a, new to this forum and impressed by all the knowledge on here.

I have a question for you all. I am a teacher and have an excel doc which I use to record all of my students grades etc. But, every time I open and close it, it seems to be extremely slow. When I take a look at the file it is 30mb in size.

Am I doing something wrong?

Is there any way I can reduce the size of it to speed it up?

I have recently been trying out a few codes, could this be adding to the overall size of it?

Looking for ward to your response,
Thankyou,
Scott

 

[Datsmart]

To lessen File Bloat, avoid the following:
Formats that extend over entire Columns, (65536 rows) or entire Rows, (256 columns). Even larger in Excel 2007.
Graphics, even if they are hidden.
Formulae that reference entire rows or columns.
Named ranges that are referenced from worksheets other than their own.
Pivot tables that have had data changed, added to, and refreshed time and again.

 

[sbrazil]

Hi John,
Wow that was a quick response. Thanks for that.

But mate I have to apologise for my stupidity, can you please just dumb it down or me a little.

When you say format over entire columns and rows, do you mean to only format the work area which I am working on, and nothing else? As I am quite sure I am already doing this. How can I check up on this?

Also, when you say "Formulae that reference entire rows or columns." do you mean when I am putting in codes? This is also something I would love help with. Would you be able to help me oput with this. I will wait to hear from you on this before I ask the next question.

thanks for this John,
Scott

 

[wigi]

When you say format over entire columns and rows, do you mean to only format the work area which I am working on, and nothing else? As I am quite sure I am already doing this. How can I check up on this?

Press Ctrl-End and see in which cell you end up.

(Do this on every sheet.)

 

[sbrazil]

Hi guys, thanks for your responses. Really appreciate it.

Just did the control-end, and wow! yes i was way off the page, on at over 1000000!

Awesome guys, you have saved the day.
Again thanks,
Have a good one,
Scott

 

[zerohead]

To lessen File Bloat, avoid the following:
Formats that extend over entire Columns, (65536 rows) or entire Rows, (256 columns). Even larger in Excel 2007.
Graphics, even if they are hidden.
Formulae that reference entire rows or columns.
Named ranges that are referenced from worksheets other than their own.
Pivot tables that have had data changed, added to, and refreshed time and again.

Well, if the above things are not allowed, then what would Excel left for us? I thought those things add flexibility in the calculation. But indeed, I also have problems in extremly slow calcuation mainly due to too much formulae in the whole worksheet.

 

[Datsmart]

Well, if the above things are not allowed, then what would Excel left for us?

Formats and Formulae that reference entire Rows or Columns ARE allowed.
It is just recommended to NOT do that to avoid slower calculation.
Excel 2007 now has 1,048,576 Rows and 16,385 Columns. If you have a formula that referrences the entire worksheet, that formula has to calculate all 17,180,917,760 cells.
Keeping your formulae referrencing just your used range can speed calculations dramatically.

 

[zerohead]

yes, your advices really help, for my case, the time for a single update of calculation is reduced from 35 sec to 15 sec. thx.