Question Excel locking up

[mabris]

My boss is using Excel 2019 and having lock up issues. He is using a Lenovo M920x Windows 10 with an Intel i7-8700 processor with 32GB memory. He usually works with about 40 spreadsheets open at one time. Excel will freeze. Sometimes you can close down a few of the spreadsheets and it may start working other times you have to close the program completely. Not much rhyme or reason to it. He uses these same sheets on a regular basis as they link to each other. Any known issues with Excel 2019 I should be aware of. Or what to look for in the logs?
Thanx in advance

 

[kanewolf]

Use the Windows task manager to see what the resource utilization is when this happens. Could be low on memory with that much open at once.

 

[mabris]

Use the Windows task manager to see what the resource utilization is when this happens. Could be low on memory with that much open at once.

With all 40 sheets open and all other programs closed CPU usage is 20.1%, Memory usage is 27%

 

[sizzling]

My experience of Excel is it is very dependent on drive speed and this can equally impact performance like insufficient RAM or CPU power. It can behave like it has locked up while reading/writing amounts of data far greater than the spreadsheets being worked. I don’t know why it does this but is something to look into.

 

[kanewolf]

With all 40 sheets open and all other programs closed CPU usage is 20.1%, Memory usage is 27%

If I remember correctly, by default Excel is a 32 bit program. Check the Processes tab. Does Excel show as 32 bit? I don't have 2019 so it may default to the 64 bit version. If it is a 32 bit program, it is still limited to 4GB RAM no matter how much the PC has.

 

[mabris]

If I remember correctly, by default Excel is a 32 bit program. Check the Processes tab. Does Excel show as 32 bit? I don't have 2019 so it may default to the 64 bit version. If it is a 32 bit program, it is still limited to 4GB RAM no matter how much the PC has.

Running Excel 64 bit

 

[mabris]

My experience of Excel is it is very dependent on drive speed and this can equally impact performance like insufficient RAM or CPU power. It can behave like it has locked up while reading/writing amounts of data far greater than the spreadsheets being worked. I don’t know why it does this but is something to look into.

All the spreadsheets or pulling from a server location.

 

[kanewolf]

All the spreadsheets or pulling from a server location.

Do you mean that the sheets are on a remotely mounted volume? If so, copy to local storage as a test. See if that makes a difference.
Or do you mean that they pull data in via a SQL interface or similar?

 

[mabris]

Do you mean that the sheets are on a remotely mounted volume? If so, copy to local storage as a test. See if that makes a difference.
Or do you mean that they pull data in via a SQL interface or similar?

Remotely mounted volume. The only issue with that is all the links in the spreadsheets and making sure they are mapped correctly.

 

[kanewolf]

Remotely mounted volume. The only issue with that is all the links in the spreadsheets and making sure they are mapped correctly.

Is it possible that the remote volume is overloaded? What device is hosting these files? An enterprise class storage or just another PC? What does the network utilization at the bosses PC and the server look like?

 

[sizzling]

I will be honest my understanding is limited but I am a fairly heavy Excel user. I have seen Excel using high local drive usage even when the files are not locally stored. It seems to do a lot of caching locally.

 

[mabris]

Is it possible that the remote volume is overloaded? What device is hosting these files? An enterprise class storage or just another PC? What does the network utilization at the bosses PC and the server look like?

Windows 2012 server. Not overloaded. Will have to check net utilization.

 

[Alabalcho]

I don't want to sound rude, but it seems your boss is using the wrong tool for the task. 40 Excel sheets pulling data from linked sheets is a recipe for disaster.

May be there's a way some of this data to be consolidated in an SQL database (or even another spreadsheet) on a regular basis.

On the subject: I can easily bring Excel down with a million rows, hundred columns sheets. Add a chart or two, and even without remote links, you are ready.