How to Prevent Excel from Auto Calculating and Updating Data in Your Workbook?

Sometimes you will see the information that asks you whether you want to save the changes or not. But actually you make no change to the file. Why does this information still appear?

If you make some modifications to an Excel file and then close it without saving, it will ask you whether you want to save the changes. It is indeed a very good feature that can avoid data loss to certain degree.

However, when you open another workbook and check certain values instead of changing it, you will still see the information. You may wonder which value has changed. You can click the button “Cancel” and find in the file. The easiest way is to check whether the “Undo” button is available. But you will see that this button is also invalid.

To figure out the reason, you may continue referring to the rest of the article.

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 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.

Avoid Auto Calculate through Calculation Options

If you find this feature annoying, you can also close it. To make the data more accurate in the workbook, you need to change in Excel options.

1. Click “File” in the ribbon.
2. And then click “Options”.
3. In the “Excel Options”, choose the “Formulas”.
4. And then in the “Calculation Options”, check the option “Manual”.

There are also two different conditions about the option under the “Manual”.

• If you check the option “Recalculate workbook before saving”, the next time you open the workbook, the formulas will not recalculate. If you make no change to the workbook and close it, the formula will not recalculate and you will not see the information. But if you save the workbook manually, even if you still make no change to the file, the formula will recalculate and then save.
• On the other hand, if you uncheck the option, it will not recalculate when you open the workbook. When you save the workbook, the corresponding formula will still not recalculate. This is exactly the difference of the option. And you will not see the information when you close the file.

Now you check the “Manual”, only when you manually recalculate the worksheet (Press F9) or workbook (Press Shift+F9), the formula will recalculate. Hence, you need to decide whether to check the option according to your actual need.

5. And then click “OK” in the window.
6. Next save the workbook.

Besides, this setting only takes effect in the current workbook, so it will not affect other Excel files. And now you will never be bothered by the same problem.

You may also pay attention that all the formulas in the workbook will not recalculate automatically. Suppose there is a formula that refers to a certain cell. When you change the value in the cell, the formula will not recalculate. If you uncheck the option “Recalculate workbook before saving” and you forget to recalculate manually, the result of the formula will actually become an error even if you save the file. This is a very important point that you need to be careful.

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.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com