Performance constraints - Excel or PC

[BristolJGM]

Hi all - I'm working with a large Excel file with lots of formulas. Please assume that the formulas can't be simplified in any way and that I can't use another program.

Above a certain size (about 250 MB) it starts 'freezing' when I change cells with lots of links to them. It does eventually recover but is very slow to do so.

I am using an Intel Core i7 5960X purchased in 2015 with 8 cores/ 16 threads and 32GB of RAM. I am running 64 bit Excel.

My question is - is the performance bottleneck my computer or is it Excel itself? I could spend money on a new AMD Ryzen 9 5950x with 16 cores/ 32 threads and 128GB RAM, which is about six times faster than my existing PC. However, I don't want to spend money and then find that the freezing has been caused by the spreadsheet itself rather than the PC.

Please assume this can't be fixed by amending the spreadsheet - I would like to increase its size to around 900MB; even if tweaking it could mean that it can get a bit larger before bottlenecking, I don't think this will get it to 900MB.

 

[Saurabhj]

Check if saving file in .xlsb resolves the issue.

 

[BristolJGM]

Afraid not but thanks for the reply. I think what may be causing it to bottleneck is links between worksheets but I've minimized these to the extent reasonably possible.

The PC can actually calculate the spreadsheet reasonably quickly once it gets going. What kills it is changing cells with lots of linked formulas (there are lots of lookups). Even in manual calculation mode this sends it into 'not responding' and it thinks for ages before recovering (the recovery time is way longer than it actually takes to calculate the spreadsheet).

I need to upgrade my PC anyway but I'm worried that an Excel file this big ( > 300mb with a lot of formulas) will bottleneck like this no matter what it's run on.

 

[sandy666]

I don't know the structure of your data but maybe re-build your file with Power Query or even vba (with fewer formulas or not at all)

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[BristolJGM]

It won't work in PowerQuery/ VBA, unfortunately... (The file is confidential so I can't explain). Please assume I need to do everything in Excel with formulas.

It would be great if anyone has a workaround which stops the bottleneck on my current PC. However, my main question is whether a PC upgrade might fail to solve a performance bottleneck like this.

Thanks!

 

[sandy666]

I need to do everything in Excel with formulas

You may split this file to few files (with formulas) but imho it will cause more problems
I have no more ideas, sorry
Have a nice day

 

[BristolJGM]

Thanks for the ideas. If it's any help to anyone I would stress that it bottlenecks even when calculation is set to manual - query what causes Excel to do this.

 

[Fluff]

Do you have a lot of conditional formatting?

 

[RoryA]

300MB for a compressed file is very big. Do you have 64bit Office?

 

[mikerickson]

"A lot of lookup formulas" Helper columns (increasing the numbers of formulas) rather than array formulas.