Bishawajit Chakraborty
Every time a cell is modified, Excel by default updates every formula in a workbook. This may cause work to become slower, especially in complex files with several formulas. We can switch the formula computation from automatic to manual to accelerate the entire process. This implies that formulas won’t be automatically calculated after each modification in a workbook. In this article, we will show you how to turn off AutoSum in Excel.
How to Turn Off AutoSum in Excel: 2 Handy Ways
If you omit to manually recalculate and deselect the option to “Recalculate spreadsheet before saving,” the formula outcome will definitely be an error even if you save the file. This is a really crucial point, so take caution. You will discover how to turn off AutoSum in Excel using the Formulas tab and VBA code in the next two techniques. Let’s suppose we have a sample data set.
1. Utilizing Formulas Tab to Turn Off AutoSum in Excel
Access to Excel’s built-in functions and formula auditing tools are available through the Formulas tab. You’ll probably start to rely on formulas more as you get more comfortable with Excel and need to process data. Excel has a large range of equations, referred to as functions, to assist with this. If you need to design your own formulae, Excel includes a complete range of auditing tools to ensure your formula is as accurate as possible. In this first step, we will demonstrate to you how to turn off AutoSum in Excel by utilizing the Formulas tab.
Step 1:
- For instance, when changing any cell value in an Excel spreadsheet, applying a product formula often updates the value automatically.
- In cell E5, we now use the product formula.
- Additionally, the values in cell E5 are immediately recalculated whenever we modify any value in cells D5 or C5.
- Now, jot down the formula below.
=C5*D5
Step 2:
- Firstly, choose the Formulas tab.
- Then, select the Automatic command from the Calculations options.
- Lastly, you can see that in this instance, the results in the E5 cell are computed automatically once you press the ENTER key.
Step 3:
- We first select the aforementioned Formulas tab in this part to turn off the automated computation.
- Then, from the Calculations options, choose the Manual command.
- Even if we adjust the value in cell D5, cell E5 won’t change.
- In cell E5, we must therefore manually alter this value.
Step 4:
- Finally, you can see that in this instance, the results in the E5 cell are manually calculated when you press the F9 key.
Similar Readings
2. Applying VBA Code to Turn Off AutoSum
VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. You can launch the VBA editor using the Alt + F11 keyboard shortcut. In the last section, we will generate a VBA code that makes it very easy to turn off AutoSum in Excel.
Step 1:
- In an Excel spreadsheet, for instance, applying a product formula usually changes the value when altering any cell value.
- Here, we apply the product formula in cell E5.
- And, if we change any value in cells D5 and C5, it will automatically recalculate the values in cell E5.
- Now, write down the following formula.
=C5*D5
Step 2:
- You can see that after pressing ENTER in this case, the results in the E5 cell are automatically calculated.
- Firstly, we will open the Developer tab.
- Then, we will select the Visual Basic command.
Step 3:
- Here, the Visual Basic window will open.
- After that, from the Insert option, we will choose the new Module to write a VBA code.
Step 4:
- Now, paste the following VBA code into the Module.
- To run the program, click the “Run” button or press F5.
Sub Turnoff_Autosum()
'Applying calculation manual statement to turn off autosum
Application.Calculation = XlCalculation.xlCalculationManual
End Sub
VBA Code Breakdown
- Firstly, we specify a name for the subject as Turnoff_Autosum().
- Finally, we apply the statement to turn off autosum using the Calculation = XlCalculation.xlCalculationManual.
Step 5:
- To disable the automated computation, we run the aforementioned VBA code in this section.
- There won’t be any change in cell E5 even if we update the value in cell D5.
- Therefore, we must manually update this value in cell E5.
Step 6:
- When you press the F9 key, you can see that in this case, the outcomes in the E5 cell are manually calculated.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
Conclusion
In this article, I’ve covered 2 handy methods to turn off AutoSum in Excel. I sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
