Excel, which contains a large number of formulas, will recalculate each time you perform some operation, and you will not be able to perform the next operation until the calculation is finished. If you feel that Excel is too slow, you can stop the recalculation to make it run more smoothly.
Also, if for some reason manual calculations are made without being aware of it, the calculation results will not change even though the values have been changed.
In this issue, we will show you how to switch such recalculation in Excel to manual or automatic.
How to switch between manual and automatic calculation methods in the macroExcel Macro (VBA) to stop automatic calculations.
We have prepared a table
In this case, we have prepared the table below to check if the calculations have been performed.
The quantities of apples, oranges, and tomatoes are entered in column "C" respectively, so that the total quantity is displayed in cell "D6".
Switch to manual
Click the "Calculation Method Settings" button on the "Formulas" tab of the toolbar and click "Manual
The calculation method has now been changed to the leading one. Let's change the values in the table to try it out.
I changed the quantity of apples from 15 to 1000, but the total is still 41. Clearly it is not totaled.
Recalculate once with manual
Next, leave the calculation method as "manual" and recalculate.
In the "Formulas" tab, click on "Run Recalculation.
The total number then changed from 41 to 1026. You can see that a recalculation has been performed.
*If you want to recalculate only in this sheet, such as when a large number of formulas are contained in other sheets, you can use "sheet recalculation" below it.
In this state, I tried changing the apple value again from 1000 to 5000, but the total value did not change.
You can see that the calculation method has not been switched to "manual".
Switch to automatic
Now click on "Automatic" under "Calculation Method Settings" in the "Formulas" tab.
When I changed the quantity of apples to 15 again, the total was automatically calculated to 41.
Conclusion
While switching the calculation method to manual allows even heavy Excel to run smoothly, it is possible that for some reason, such as when using macros, the calculation method is switched to manual without knowing it.
It is important to remember how to switch the calculation method between manual and automatic so that it can be handled in such cases.
How to switch between manual and automatic calculation methods in the macroExcel Macro (VBA) to stop automatic calculations.
Comment