VERY slow opening file?

[dobby1303]

Hi,

I'm a teacher and trying to set up a markbook for all m classes next year, I've got it set up how I'd like, but it is VERY slow to open, I'm talking 2-3 minutes at least to open, often longer.

I suspect I've made either some of the formulas or conditional formatting very messy (or possibly both), but I can't figure out where, are there any kind souls out there who could help me?

https://www.dropbox.com/s/gdsx4l78lw1x783/2016-17.xlsx?dl=0

Thank you!

 

[Michael M]

Without downloading the file.....
Why would you need a 135mb file to setup a markbook

And I;m guessing there won't be many posters that will go to the trouble of downloading it either at that size!

Also, have you looked at any of your formulae that calcl the whole column and reduced them to a smaller range, eg =SUM(A1:A100 rather than =SUM("A:A")
This could also apply to your CF as well

 

[gallen]

That is a large file indeed. Conditional formatting can cause issues if its on many many cells.

I'd start here though and try to get the file size down.

Also check there is no code running on the open event of the workbook

 

[steve the fish]

The problem id imagine is to do with used ranges. Take a look at the scrollbar on the right hand side. Are any smaller than to be expected? If so delete the spurious lines using CTRL-SHIFT-DOWN then right click and delete. Excel may complain. Just click yes. Save the workbook. Check all worksheets.

 

[dobby1303]

Thanks for all the quick responses!

extra cells - I thought if I deleted a column or row, excel would repopulate it the end of the table?

conditional formatting - unless I've missed one, they only apply to necessary cells, although some do have multiple conditionals on them

ranges - again, unless I've missed one, only used where necessary, not applied to entire rows/columns

errors - because none of the marks or grade boundaries are in yet, most are returning errors at the moment, I've set error checking to ignore them though

formula - there are lots of lookup tables and logic formula "if(isnumber...) to calculate totals/ averages from taken marks to give current grade - I suspect it's this that is causing the problems, but don't know how to fix it

I know it's large, but I don't know why, that's part of what I'd like help with

 

[steve the fish]

Did you look at the scroll bars??? Pull them down. Do they go to the expected end of range or down to row 1048576?? I suspect the latter....

 

[dobby1303]

THANK YOU!!! It had done that on 3 of the sheets and I hadn't noticed! I'm pretty sure it's sorted now! File size is now 1.2MB and it opens fine!

 

[steve the fish]

Good its a pretty common problem with pasted data and does use up a lot of resources if excel thinks it needs to calculate the cells. The deletion/ saving resets the used range so those cells can be ignored.