Why does my spreadsheet process so slowly?

[yousufj56]

Hi everyone,

Here is a link for my spreadsheet. Hope you can download it. The query itself doesnt run too slow but the excel spreadsheet as a whole is slow. Can't scroll fast enough, make selections fast enough etc. This occurs even if it is the only file that is open.

https://drive.google.com/file/d/0B3S...it?usp=sharing

I have a feeling it has something to do with refreshing query or the connections it has established. Not sure if that makes sense but you'll know what I mean when you see the file.

Thanks in advance!

Yousuf

 

[mole999]

you appear to have over 4500 named ranges that are duplicates and broken which do need clearing out

 

[mole999]

another thing is your formatting

I can't see a value in having a big chunk of your main page all nicely boxed, and then so many conflicting CF on those top rows, each is a calculation and 16000 cells long

 

[mole999]

The final thing for me is the file you shared is xls, yet the CF (graduated) I didn't think arrived until xlsx format

 

[yousufj56]

you appear to have over 4500 named ranges that are duplicates and broken which do need clearing out

Hi,

Thanks for your reply

I'm not an expert at vba. I don't know how to make the required changes. Would you be bale to make the changes and send me my spreadsheet?

Thanks a lot

 

[mole999]

have a search for Navigator Utilities (I believe its a free trial), that will fix all the web lookups for you
look at your your CF, it dosen't take long to sort them into numeric order, delete the obvious errors, multiple cross application
save it as xlsm or xlsb from 2007 as it should be

doing that i got it down to less than 100Kb and runs in less than a second

you'll learn more