Paste values causes Excel to freeze

Hi,

Lately I have been experiencing a frustrating problem which just suddenly appeared - When I copy and then try and paste using the paste only values option, it causes Excel to freeze for up to 5 minutes. I found the time to write this post while I wait for the program to unfreeze.

How do I solve the issue?

Thanks.

Jackblack2

Hi,

It seems like a few extra details may be helpful on this one.
Are you pasting large sections of data?
Are you running multiple large files at once?
Do your work books run a lot of formulas, particularly volatile functions that recalculate every time ANYTHING changes?
Do you have a lot of conditional formatting (also is volatile)?
After excel unfreezes, does the data finish copying?

If this is related to a lot of calculations going on, you should see "Calculating" in the status bar. In this case you can speed things up by turning off automatic calculations while you copy the the data.
Go to "formulas" tab, click on the "Calculation Options" dropdown, and select manual. This is pause all calculations til you it back on, or manually click on "Calculate Sheet" or "Calculate Now"

Copy the data, and don't forget to turn the calculations back on when you are done

My file is over 24mg large and have over a hundred thousant formulas running and a large amount of conditional formatting but I have automatic calculations turned off. The version is the latest - 2019. The data gets pastedbut only after waiting up to 5 minutes for the program to unfreeze. It happens even if I copy and paste values of just one cell.

The version is Excel Home and student 2019 for Windows 10

I never had this problem up until 2 weeks ago. I never added any new formulas or conditional formatting. It just suddenly started freezing although automatic calculations are turned off. It only freezes when I choose the paste only values option not the regular paste option.

I have over a hundred thousand formulae and they use full column references and the conditional formatting covers hundreds of thousands of cells.

Originally Posted by jackblack2

The version is Excel Home and student 2019 for Windows 10

AliGW was asking for you to amend your profile so it shows up under your name, it'll be handy and stop people asking you which version you're using each time you've a problem.

Originally Posted by jackblack2

It just suddenly started freezing although automatic calculations are turned off.

Is it just this one Workbook that is causing this issue? It could have become corrupted somehow if so.

This also suggests to me that the problem may lie outside of Excel. Have you installed any programmes to your computer in the past two weeks? If so try closing them all done one by one till you can rule that out.

Has anything else changed at all in the environment? New hardware?

Have you recently upgraded your Excel to a new version - Older workbooks need to be recalculated by newer versions of Excel.

In general though, lots of formulas with full column references and lots of conditional formatting isn't going to help anything when it comes to calculating cells.

Have a look at the following link to see if you can see anything here that might apply to you;

https://docs.microsoft.com/en-us/off...on-performance

Yes, I recently upgraded my Office program from Offce bought in 2015 to Office 2019.

And its only one Excel file of mine which has this problem suddenly. The one with 24 mb and hundreds of thousands of formulas. I am guessing then that the formulas and the upgrade are to blame. Yet even after the upgrade for a time I could use the paste only values option and it would not freeze like it does now.

Do you have any filters active on the data? I've found this can cause issues on large datasets if the filters result in non-contiguous rows.

. As far as I know, I dont have any activated. How would I know if I have filters activated?

You will see the row numbers are light-blue in your sheet instead of black. Just clear all the filters Data -> Clear to clear any if they are on.

They are black. So its not filters. Its crazy, since I can copy and paste just one cell and it will freeze for up to 5 minutes despite calculations being turned of.

I just thought of something - I lately get a circular reference pop up. Maybe that is the problem. How do I find which cell has the circular reference error?

Try Formulas -> Error Checking -> Circular References

The circular reference option is not active on any tab (nothing happens when I click on it)

I amended my version list as requested. I use 4 different versions, depending on which work station I get access to. My primary version is the Office 365, student version

Problem solved. I just discovered that somehow the automatic calculations options was turned on. I just turned it to manual and the problem disappeared. It must have been turned on when I opened another Excel workbook which was set to automatic and it must have set the other workbook to automatic.

Marking this thread as solved.

Thanks for the help.