How to turn off auto calculation in Excel

Blog

The Reason behind the Phenomenon

In an Excel with plenty of data, it will certainly contain formulas. Some formulas don’t need to refer to another cell. For example, the RAND function. Sometimes you will use it in your worksheet to get some random numbers. Other functions also include NOW, TODAY and others.

And now check the calculation options of the current workbook.

  1. Click the tab “Formulas” in the ribbon.
  2. And then click the button “Calculations Options”.

In this image, you can see that the calculation options is “Automatic”.Now we can get the conclusion. If there is certain

Now we can get the conclusion. If there is certain functions and the calculation options is “Automatic”, whenever you open the file, those functions will recalculate automatically and produce new results. On the other hand, if this is a macro workbook, certain codes will also have the same effect. Thus, even if you made no change to the workbook and the “Undo” button is invalid, there still exist changes in the file. Therefore, the next time you see the information, you need to decide whether to save the changes of those functions according to the actual need.

Video

Change the number of times Excel iterates a formula

Click the File tab, click Options, and then click the Formulas category. In Excel 2007, click the Microsoft Office Button, click Excel Options, and then click the Formulas category. In the Calculation options section, select the Enable iterative calculation check box. To set the maximum number of times Excel will recalculate, type the number of iterations in the Maximum Iterations box. The higher the number of iterations, the more time Excel will need to recalculate a worksheet. To set the maximum amount of change you will accept between recalculation results, type the amount in the Maximum Change box. The smaller the number, the more accurate the result and the more time Excel needs to recalculate a worksheet. Note: Solver and Goal Seek are part of a suite of commands sometimes called what-if analysis tools. Both commands use iteration in a controlled way to obtain desired results. You can use Solver when you need to find the optimum value for a particular cell by adjusting the values of several cells or when you want to apply specific limitations to one or more of the values in the calculation. You can use Goal Seek when you know the desired result of a single formula but not the input value the formula needs to determine the result.

Change the number of processors used to calculate formulas

A computer can have more than one processor (it contains multiple physical processors) or can be hyperthreaded (it contains multiple logical processors). On these computers, you can improve or control the time it takes to recalculate workbooks that contain many formulas by setting the number of processors to use for recalculation. In many cases, portions of a recalculation workload can be performed simultaneously. Splitting this workload across multiple processors can reduce the overall time it takes complete the recalculation. Click the File tab, click Options, and then click the Advanced category. In Excel 2007, click the Microsoft Office Button, click Excel Options, and then click the Advanced category. To enable or disable the use of multiple processors during calculation, in the Formulas section, select or clear the Enable multi-threaded calculation check box. Note    This check box is enabled by default, and all processors are used during calculation. The number of processors on your computer is automatically detected and displayed next to the Use all processors on this computer option. Optionally, if you select Enable multi-threaded calculation, you can control the number of processors to use on your computer. For example, you might want to limit the number of processors used during recalculation if you have other programs running on your computer that require dedicated processing time. To control the number of processors, under Number of calculation threads, click Manual. Enter the number of processors to use (the maximum number is 1024).

Precision of Excel calculations

By default, Microsoft Excel calculates formulas and stores the results with 15 significant digits of precision. However, you can change this and make Excel use the displayed value instead of the stored value when it recalculates formulas. Before making the change, please be sure you fully understand all possible consequences.

In many cases, a value displayed in a cell and the underlying value (stored value) are different. For example, you can display the same date in a number of ways: 1/1/2017, 1-Jan-2017 and even Jan-17 depending on what date format you set up for the cell. No matter how the display value changes, the stored value remains the same (in this example, it’s the serial number 42736 that represents January 1, 2017 in the internal Excel system). And Excel will use that stored value in all formulas and calculations.

Sometimes, the difference between the displayed and stored values can make you think that a formula’s result is wrong. For example, if you enter the number 5.002 in one cell, 5.003 in another cell and choose to display only 2 decimal places in those cells, Microsoft Excel will display 5.00 in both. Then, you add up those numbers, and Excel returns 10.01 because it calculates the stored values (5.002 and 5.003), not the displayed values.

	Selecting the Precision as displayed option will

Selecting the Precision as displayed option will cause Excel to permanently change stored values to the displayed values, and the above calculation would return 10.00 (5.00 + 5.00). If later on you want to calculate with full precision, it won’t be possible to restore the original values (5.002 and 5.003).

If you have a long chain of dependent formulas (some formulas do intermediate calculations used in other formulas), the final result may become increasingly inaccurate. To avoid this “cumulative effect”, it stands to reason changing the displayed values via custom Excel number format instead of Precision as displayed.

For example, you can increase or decrease the number of displayed decimal places by clicking the corresponding button on the Home tab, in the Number group:

How to set calculation precision as displayed

How to set calculation precision as displayed

If you are confident that the displayed precision will ensure the desired accuracy of your Excel calculations, you can turn it on in this way:

  1. Click the File tab > Options, and select the Advanced category.
  2. Scroll down to the When calculating this workbook section, and select the workbook for which you want to change the precision of calculations.
  3. Check the Set precision as displayed box.
  4. Click OK.

	This is how you configure calculation settings in

This is how you configure calculation settings in Excel. I thank you for reading and hope to see you on our blog next week!

Turn Automatic Calculations Back On

To turn back on automatic calculation with the setting xlAutomatic:

1 Application . Calculation =xlAutomatic

I recommend disabling Automatic calculations at the very beginning of your procedure and re-enabling Automatic Calculations at the end. It will look like this:

Your Data is Unique

Your data stored in Excel is certainly the most unique element because no one else has the same data with yours. Thus, the result can be disastrous if you lost your file. Thus, you can prepare the software beforehand. Once your Excel corrupts, you can use this tool immediately to fix Excel. Hence your loss can be reduced to the minimum.

Recent Posts

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Tags