Forum Discussion
- NikolinoDEGold Contributor
Under the "Calculation options" section (like you already mentioned), you'll find a dropdown menu labeled "Workbook calculation."
Manual: Excel only calculates formulas when you manually instruct it to do so by pressing F9 or using the "Calculate Now" button (Shift + F9).
By selecting the "Manual" option in the "Workbook calculation" dropdown menu, you can activate manual recalculation mode. In this mode, Excel will only recalculate formulas when you specifically request it, either by pressing F9 or using the "Calculate Now" button.
If you want to completely stop the recalculation of data tables in Excel, regardless of the settings or conditions, you can consider using a VBA (Visual Basic for Applications) macro to temporarily suspend calculation while you perform your tasks.
Here's a simple VBA macro that you can use to turn off automatic calculation:
Vba codes are untested, please backup your file first.
Sub TurnOffCalculation() Application.Calculation = xlCalculationManual End Sub
You can run this macro by following these steps:
- Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
- Go to Insert > Module to insert a new module.
- Copy and paste the above VBA code into the module window.
- Close the VBA editor.
- Press Alt + F8 to open the "Run Macro" dialog.
- Select TurnOffCalculation from the list and click Run.
After running this macro, Excel will stop automatically recalculating data tables. However, keep in mind that this will also prevent the recalculation of all other formulas in your workbook until you turn calculation back on.
To turn calculation back on, you can create another macro:
Sub TurnOnCalculation() Application.Calculation = xlCalculationAutomatic End Sub
Follow the same steps as above to create and run this macro when you want to resume automatic calculation.
Please note that manually controlling calculation settings via VBA macros can have implications for the functionality and accuracy of your workbook, so use these macros cautiously, they have impact on your specific scenario. The text, steps and code were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- Riley_JohnsonCopper Contributor
NikolinoDE Thank you for the tips and code. I was hoping there was a way to perform the tasks without having to use manual calculation mode.
I would think that regardless of what shortcut you use to paste values, Ctrl + Shift + V or Alt + E + S + V, they should not override the calculation mode. I'm not sure why, but the data tables only periodically recalculate when using Ctrl + Shift + V even though my calculation mode is set to "Automatic Except For Data Tables."
Since I may have multiple workbooks open at a time, it starts refreshing 20-30 Data tables and is painfully slow.
One solution I was thinking about is writing a macro to paste values from the clipboard and then using the Application.Onkey Method to bind the macro to Ctrl + Shift + V. However, then it's a macro and there is no undo.