Excel file slow to close

[dmacmillan]

Hello All,

A large .xlb file (25Mb) takes 90 seconds to close.

Ran a few tests with negligible impact.
Removed conditional formatting, converted table to values, and checked used ranges.

Opens slowly, but much quicker the closing. Calculation speed slow, but not unbearable (say 10 seconds for a rebuild).

Should I insert a macro to prevent calculation in close?

I’d welcome any other suggestions.

Many thanks,
David

 

[SanjayGMusafir]

Should I insert a macro to prevent calculation in close?

It's a very risky thing to do. Performing calculations before closing or saving is a safeguard against accidental exit and thus saving your precious work/effort.

Among many things that slow down calculations are -

# complicated conditional formats - I had one that checked 90,000 rows before formatting. Any change would trigger recalculating that...
# Too much of lookups - This substantially slow downs system
# Too many interdependent calculations

I would not tell you to remove all or convert table to values because that shall defeat the purpose of using excel. But you need to prioritize what is needed.

for eg -
I let go the above mentioned CF once the purpose was over and saved the rule in a text file, just in case I would need that in future (that I know would be required)
But my file has more that 27,000 lookups running because I know I need them at all time...

Hope it helps you in some useful way...