Large Excel File with formulas goes very slow on updating

[mawama]

Hey,

Merry Christmas.

I have large excel file with formulas that goes very slow on updating.

Kindly what to do making it goes faster????

Cheers,

Mawama

 

[mrhstn]

Hi Mawama

There isn't an awful lot to go on here so this is going to be very general.

Make sure the layout is neat and organised.
Remove any unnecessary formatting.
Be sure you have thought the model through so there is efficient flow.
Use helper cells, columns and rows.
Only calculate what you need. I understand most functions are intelligent enough to be 'self-dynamic' but not all users are and an erroneous entry on a sheet can result in problems.

There will be other things you can do so you will likely find others chipping in suggestions.

Here is a useful article - https://msdn.microsoft.com/en-us/vba/excel-vba/articles/excel-improving-calcuation-performance

Hope this helps

 

[mawama]

Hi Mrhstn,

Thanks for the feedback BUT things still slower! Using manual option it works better but when place F9 STILL YET TAKING TIME.

Regards,

Mawama.

 

[mrhstn]

Yeah, switching to manual calculation will still take the same time, it'll just calculate less frequently which is why I don't tend to offer it as a solution.

Did you try anything else?

I often get files sent to me at work exceeding 10MB, on opening I remove the formatted unused space and that simple action usually drops the file size to a few hundred KB and the performance drastically improves.