Excel remain hang

[Tarek_CTG]

I have a huge range of data. And around 20 sheets are connected with that data through some formula. When I replace those data with new ones, my excel remain hang for some seconds.

01. My first question, is there any formula or way that my formula will work only when user wants? For exmaple, whenever I copy paste data the formula reloads automatically, but I want to work formula only when I want, not that time when I am doing copy paste?

02. Is there any way like macro that when I press something then my formula workr?? Or any time limit can be given to work the formula ??

03. Another problem is, when that workbook remains open in my computer, all other workbook works slowly and remains hang for few seconds. What is the solution ?

Thanks in advance.

 

[DRSteele]

One suggestion could be to turn off automatic recalculation under File|Options|Formulas|WorkBookCalc Manual. Do your work and then press f9 to recalculate the open workbooks.

Recalculate formulas that have changed since the last calculation, and formulas dependent on them, in all open workbooks. If a workbook is set for automatic recalculation, you do not need to press F9 for recalculation.
F9


Recalculate formulas that have changed since the last calculation, and formulas dependent on them, in the active worksheet.
Shift+F9


Recalculate all formulas in all open workbooks, regardless of whether they have changed since the last recalculation.
Ctrl+Alt+F9


Check dependent formulas, and then recalculate all formulas in all open workbooks, regardless of whether they have changed since the last recalculation.
Ctrl+Shift+Alt+F9

 

[Tarek_CTG]

But problem is that, If I change option Automatic to manual, then all formula of all workbooks change and all workbooks become manual, but it is not suitable for me. I want different solution.

Thanks in Advance.

 

[DRSteele]

No, I believe that the recalculate options apply to each workbook.

 

[Tarek_CTG]

No, I believe that the recalculate options apply to each workbook.

If I change automatic to manual option, does this setting change to all workbook of that computer or not ??

 

[MARK858]

If I change automatic to manual option, does this setting change to all workbook of that computer or not ??

From Decision Models - controlling calculation


Application level settings.

The following calculation settings are held at the Application level rather than for each workbook, so they apply to all open workbooks.

•Calculation Mode (Automatic, Automatic except Tables, Manual, Calculate before Save).
•Iteration settings (Iteration On/Off, Max Iterations, Max Change).

Rather confusingly these application level settings are saved in each workbook, but only the settings in the first workbook opened are actioned: the settings in subsequent workbooks are ignored.

Workbook level settings.

The following calculation settings are held at workbook level, so each open workbook can have different settings.

•Update remote references.
•Precision as displayed.
•1904 date system.
•Save external link values.
•Accept labels in formulae.

All these workbook level calculation settings are saved and restored with the workbook, except Update remote references.