Excel freezing after each cell update

Jjjjjjjjbarry · 20-05-2019 11:44am #1

Hi,

Can anyone help with this?

I have sheet that suddenly started freezing for about 7-10 seconds every time I add/change a value in a cell. Once I add the value and then hit enter or click into a different cell it freezes. This started happening recently and I don't know why.

I've now just discovered a work around. If I put an "=" before the value it doesn't freeze and I quickly move on without waiting for up to 10 seconds.

It's a complicated enough sheet with lots of pivots and lookups so I thought I'd never solve it but the fact that it works perfectly by adding the = before a value means it's not the complexity of the sheet that's causing it?

Obviously, I'd like to solve further and not have to add the = as I used this so much, even that is a nuisance

Any help much appreciated - thanks!

TrueDub · 20-05-2019 11:57am

Try switching off Automatic Recalculation and see if that changes things.

namenotavailablE · 20-05-2019 8:48pm

Anotehr possibility- if your sheet has some VBA code attached, changing a cell triggers the various 'events' attached to the sheet e.g. Sheet Change, Sheet _Calculate etc.

You can add 'Application.Enableevents=false' (without the inverted commas') before the VBA code runs. Remember to also add 'Application.Enableevents=true' at the end of the code

Jjjjjjjjbarry · 20-05-2019 8:52pm

TrueDub wrote: »

Try switching off Automatic Recalculation and see if that changes things.

Unfortunately this doesn't make a difference. It's really odd to me putting in an = makes a difference. Also copying and pasting values works fine.

Jjjjjjjjbarry · 20-05-2019 8:53pm

namenotavailabl wrote: »

Anotehr possibility- if your sheet has some VBA code attached, changing a cell triggers the various 'events' attached to the sheet e.g. Sheet Change, Sheet _Calculate etc.

You can add 'Application.Enableevents=false' (without the inverted commas') before the VBA code runs. Remember to also add 'Application.Enableevents=true' at the end of the code

I'm not that advanced unfortunately so don't think it's VBA. Thanks for the suggestion though!

ct5amr2ig1nfhp · 20-05-2019 8:56pm

What version of Excel are you using? Is it an xls or xlsx? And how large is the file?
Windows 10? Do you have any pending OS or Office updates?

Jjjjjjjjbarry · 21-05-2019 10:33am

It's 2013 and the file is .xlsx
size 19000 KB but always been fairly big.
Windows 10

Work laptop so don't usually notice any updates, think they must happen in the background. I'll check.

Esel · 21-05-2019 2:39pm

What format are the cells into which you are entering data?

Entering = at the start denotes a formula, AFAIR.

Jjjjjjjjbarry · 21-05-2019 3:50pm

They are a number format.

ct5amr2ig1nfhp · 22-05-2019 5:01pm

I presume you have tried the same Excel file on another device (PC/laptop)? Are you seeing the same issue with the file on another device?

Jjjjjjjjbarry · 27-05-2019 11:30am

Oddly enough, I've tried it from someone else's machine and it worked fine without this lag. So must be some setting in my excel that's changed as opposed to the sheet setup.

namenotavailablE · 27-05-2019 11:44am

Try this:

File | Options | Advanced and then check the settings as per the following diagram:

ct5amr2ig1nfhp · 27-05-2019 7:39pm

Do a repair on office if the above doesn't work. See if that resets Excel to whatever way you had your settings.

Jjjjjjjjbarry · 27-05-2019 8:02pm

Yeah the above settings were already like that so must try a repair job and see if that works. Thanks for the help. Good idea checking from another computer. Hadn't thought of that, as dumb as it makes me sound!

ct5amr2ig1nfhp · 31-05-2019 7:24pm

Did you get sorted?

Jjjjjjjjbarry · 11-06-2019 11:07pm

Haven't had a chance to risk it yet. Afraid it will brick my excel! hope to give it a go in next week when I have more IT support on hand!

Excel freezing after each cell update

Comments