One worksheet causes severe "not responding" delay on saving/scrolling

[Kenny Tilley]

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

[#2]

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.

[#3]

tks

it was on a shared drive, but i'd already copied it to my documents folder before doing the above tests.

K

[#4]

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?

[#5]

tks,

if you re-read my original post, you'll notice that i've already narrowed it down to one sheet and that copying/pasting the formulae causes a complete hang but that pasting the values is the thing that works. perhaps i wasn't clear! :)

K

[#6]

I did see that, thanks...so, those external file references in this sheet that you referred to... Those have been moved to your local desktop as well? And they have been troubleshot in the same way?

[#7]

no i edited the relevant VLOOKUPs to make the references local to the workbook, they had been originally copied from another similar workbook and so had absolute paths in them, which i removed, just leaving the lines looking like this:

=IF(B120>0,VLOOKUP(B120,Customers!$A$1:$G$999,2,FALSE)," ")

K

[#8]

ok, i have some more info.

i have deleted all the sheets in the workbook apart from the one that appears to be the problem, then i cleared all the cells in that sheet and the save still hangs!

so i completely deleted all the cells in that sheet and the save still hangs!

So i ran the document inspector and it said i still had links to external files.

so i ran the workbook analyser and got this:

so...

how do i delete these references to these unwanted, non-existing files?  they don't appear to be referenced in any cells, but they do seem to "hang" Excel...

any advice welcomed.

K

[#9]

ok, i've fixed it.  by binary chopping column copy/pasting i isolated the issue to one particular column (which was just a set of dates!).  pasting that back "as values" cleared the "roadblock" and now it saves instantly!

K

[#10]

Sorry to necro this thread but the OP describes my situation exactly, and MS tech support have been unable to isolate the issue. By messing about for several days, discounting various things, I have narrowed it down to either an issue with the Workday function, or - more likely it seems - an issue with conditional formatting. Each cell has 4 duplicated conditional formatting rules, and the program hangs if I attempt to view all the conditional formatting rules for the entire worksheet.

How this actually happened is beyond me. There should only be two rules, one for column A and one for column B. How they got duplicated, why there are so many that it would cause Excel to hang/freeze can only be down to a bug. There's been no copying/pasting on this particular sheet, save for a row of formula that was done before conditional formatting was applied.

I don't know how to get out of this now. I'm going to try and copy/paste the values/formulas to a new sheet and delete this one, because that's the one thing I haven't tried.

[#11]

Just for the benefit of others who may stumble across this thread via Google or other search engine, in my case it was definitely the conditional formatting. I've re-created the data and formulae on another sheet and deleted the old one. Everything back to normal now.

[#12]

hi,

as i said above, i isolated the column that was causing the grief by copying/pasting columns as formulae to a new workbook then i copied the offending column as values and verified that the sheet saved OK. i then reformatted the column using the original format (as date in light blue bold in my case) and t was still OK.

i then deleted  the original sheet and copied the sheet from the new workbook back in its place.

K