Forum Discussion
Kenny Tilley
Aug 31, 2018Copper Contributor
One worksheet causes severe "not responding" delay on saving/scrolling
hi,
I'm using Excel/Office 2016 on windows 10.
i have a workbook (xlsx, approx 4Mb) with multiple sheets. Saving it causes the Excel window to go "white" with a "not responding" for 20-30 secs. Scrolling is similarly delayed and it makes the spreadsheet almost unusable.
i have isolated that there is one particular sheet that is causing the issue because if i delete that sheet, it saves instantly.
if i try to copy the sheet to another sheet or an empty workbook, Excel hangs completely.
if i select all the cells and try to copy/paste them as formulae to a blank workbook, Excel hangs completely.
if i select all the cells and try to copy/paste them as values to a blank workbook, that works.
i have tried "safe mode" and disabling all addins, but it makes no difference.
i have removed all external file references (by searching for '\'), but that made no difference.
i have removed all the conditional formatting but that made no difference.
i have changed all the VLOOKUPs that use other sheets to use a specific cell range (e.g. changed 'Customers'!A:G to 'Customers'!$A$1:$G$999) but that made no difference.
what else might be causing the problem? is there a "logging mode" or any other tool which would help to indicate where the hangup is occurring?
(unfortunately i cant attach the file because it contains company sensitive financial information.)
TIA
K
11 Replies
Sort By
- Geoff FitzpatrickCopper Contributor
Where exactly does the file you are editing "live"? Is it on a shared network drive? Or on your own desktop?
I ask, because with some documents my experience has been that performing tasks like this over a network can prove problematic for a lot of reasons: it may be a transient issue during file server backups; it might be a slower network connection between your desktop and the file server... Anyway one thing to check would be to save a copy of the document to your desktop and see if the behavior continues. It would help narrow down whether some network issue is part of the cause, or if it's some issue with the document itself.
- Kenny TilleyCopper Contributortks
it was on a shared drive, but i'd already copied it to my documents folder before doing the above tests.
K- Geoff FitzpatrickCopper Contributor
So, if it is still happening on your own desktop, and this doesn't happen at all on other documents locally, you've definitely narrowed things down to the file itself.
You said that copying the sheets into a blank workbook worked. Can you do that with each page, saving as you go? That might 100% solve your issue...or at least it would help you narrow down on which sheet the trouble lies?