Excel Spreadsheet Calculation Speed and Network Activity

[mrsnrub]

Hi everyone,
I've got a bit of a strange one here, I'm still investigating but thought I might post in case anyone has any ideas.

We have a quite complex spreadsheet (Excel 2003) where the speed of calculations had started to become an issue.

Upon investigation of the speed issues it became apparent that most of the time, when performing the calculation operaton (either automatically or by pressing F9) a network spike would show in the LAN section of the 'Networking' tab in Task Manager. This spike appears only when calculating and drops back down to 0% as soon as the calculation is complete.

This spreadsheet is running completely locally (C drive). There are no links to spreadsheets anywhere on the network.

We found that if you unplug the network cable, then the calculation speed is only a fraction of the time, and obviously there is no network activity showing in the task manager.

To add further confusion to the problem, it does not appear to happen every time. There are times (so far they seem random, but I'm still looking for a pattern), where you open the spreadsheet and calculation is fast, and no network activity is present.

Due to the fact that this activity perfectly coincides with the calculation, I don't believe this has anything to do with any software updates from other applications.

Any help/ideas/advice would be welcomed.

 

[onedtent]

hope you found the answer. else, try increasing Virtual Memory size. Excel did have 128 MB formula size limit, don't know if that's true for 2003.

 

[gizmo1973]

I had this a while back and the gurus at MS tried to explain it as Excel checking for updates whilst performing calculations

Whilst not a satisfactory answer it's the only one they were willing to part with and I simply went to Access with the Excel sheets from then on anyway

Regards, Phil.

http://www.manchester-medical-services.co.uk

 

[SPV]

Knowing what Excel is connecting to/communicating with over the network may give some leads.

A utility like TCPView from

http://www.sysinternals.com

should show this.

 

[mrsnrub]

Hi everyone,
Thanks for the feedback on this one. I never did end up resolving it, but we managed to get calculation speed down to a more bearable time by changing formulas, etc.

One thing I did try, was pulling the network cable out when I was experiencing the network activity and then attempting to save to the local drive. I received the following error:
"Excel is unable to load the cached records for one or more PivotTable reports. You will need to refresh those reports before they can be used again."
I haven't yet had a chance to investigate this further, but I am now wondering whether the Pivot Tables are doing something creative here.

The SysInternals utility is a great idea, I will definately give this a go. Interesting response from the MS gurus, I guess its a feasible explanation, although I must say that you would expect checking for updates to occur during periods of inactivity rather than during one of the most critical operations of a spreadsheet!

I'll post back if I have any success or find anything useful.

Thanks.