How to Turn Off AutoSum in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Handy Ways to Turn Off AutoSum in Excel


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

Sample Data

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.

Handy Ways to Turn Off AutoSum in Excel

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.

Sample Data

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.

Sample Data


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

Sample Data

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.

Handy Ways to Turn Off AutoSum in Excel

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.

Handy Ways to Turn Off AutoSum in Excel

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

Handy Ways to Turn Off AutoSum in Excel

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.

Handy Ways to Turn Off AutoSum in Excel

Step 6:

  • When you press the F9 key, you can see that in this case, the outcomes in the E5 cell are manually calculated.

Handy Ways to Turn Off AutoSum in Excel


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.


Related Articles

Bishawajit Chakraborty
Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo