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.

 

[BristolJGM]

Thanks for the replies. No conditional formatting at all. Yes I do have 64 bit office but I am about to try 32 bit to see if it helps at all.

My computer can calculate the spreadsheet in a reasonable time once it's got out of the initial 'hang' caused by changing data.

The funny thing here is that the same spreadsheet (say 220mb size) can initially run fine and then after a while develop this problem (hanging when data is entered, even in manual mode and getting stuck on a single threaded process). This makes me think that it is a bug but I can't for the life of me figure out what. I've tried opening in safe mode - same issue. I've tried repairing the workbook- doesn't help. Turned off hardware acceleration and it didn't change anything.

 

[BristolJGM]

You can add trying 32 bit Excel to the list. This doesn't help. I've also taken out all styles other than Normal and there is no fancy formatting at all in the workbook.

A factor here is clearly file size but Excel seems quite temperamental as to when this will/ won't cause a 'hang.'

 

[BristolJGM]

As regards the lookups - I've created a helper column and sorted the data to allow me to use approximate match VLOOKUP. The problem is even worse with INDEX/Match if anyone is thinking about suggesting that...

 

[RoryA]

Given the size of your file, I would expect 32bit Excel to fall over much more often.

 

[BristolJGM]

Going back to 64 bit now...!

I have ordered a Ryzen 9 5950x i.e. the current fastest processor on the market for single core performance (it 'hangs' with a very slow single threaded process). I think it's some sort of bug but a faster computer may not hang in this way - we'll see...

 

[BristolJGM]

I think I've worked out what the issue might be:

Excel performance - Tips for optimizing performance obstructions

Learn about ways to optimize Excel functionality such as links, lookups, formulas, functions, and VBA code to avoid common obstructions and improve performance.

docs.microsoft.com

Do not use forward referencing and backward referencing
To increase clarity and avoid errors, design your formulas so that they do not refer forward (to the right or below) to other formulas or cells. Forward referencing usually does not affect calculation performance, except in extreme cases for the first calculation of a workbook, where it might take longer to establish a sensible calculation sequence if there are many formulas that need to have their calculation deferred.

The spreadsheet is riddled with references of this type. It is 'hanging' when data is changed (even in manual) which suggests that Excel is struggling to determine a sensible calculation sequence. I'll need to rework the whole thing...

 

[gdcuk]

Thanks for posting that update, I’m in a similar position and I’d not come across that forward referencing issue

would be interested if the 5950x helps too