Formulas Not Updating Automatically in Excel? Try These 9 Fixes

In Excel, we all know that when we edit the value of precedent cells, the formula cell also auto-updates and gives output accordingly. Due to this Excel’s notable feature, we can save ourselves from unnecessary and tedious recalculations. 

But, sometimes, Excel may fail to update the changes in the formula. Usually, this happens when the Calculation Option is set to Manual. So, simply, setting Automatic Calculation should fix this.

However, a few users reported they encountered this problem even when the Automatic Calculation was enabled. In that case, you might be facing one of these issues:

But, before we directly jump on to it, end the Excel app completely and reopen it. A quick restart is the best bet to refresh the laggy application. 

Set Calculation Options to Automatic

As a primary fix, the first thing you should do is check if the Calculation Options are set to Automatic or not. If it is set to manual, the formula will not update automatically. You’d have to recalculate or refresh the sheet every time manually after you’ve changed the formula.

To solve this, head to Formulas Tab. Then, click on Calculation Options > Automatic. 

Use Keyboard Shortcuts to Recalculate

Simply setting the Calculation Options to Automatic would mostly solve many users’ errors. However, if it fails to, you can manually recalculate the formula using the keyboard shortcuts. When you work with large and complex formulas, Excel may take some time to automatically update them. 

Enter one of these shortcuts to recalculate the workbook. 

Check and Change Number Formats

Are your values in the correct format? If the formulas have stopped calculating numbers after a certain point, there is a possible chance that the values are in the incorrect format.

For Example, it could be stored as text or any other special number format. During such cases, even the manual recalculate fix does not work.

To solve this, all you have to do is check the input value format from the Number section of the Home Tab and pick the Number format. 

Disable Show Formulas

If the formula stays in the cell instead of the output, it’s most likely you’ve enabled the Show Formulas. So, no matter whether you set an automatic calculation or manually do it, the formula will not update. 

To fix this, head to Formulas Tab and click on Show Formulas to disable it. 

Check Formula Errors and Resolve them

Another probable cause of why Excel won’t update formulas is when there is an error in the formula itself. There might be a typo if you’re working with a large nested function. 

Or, it could be a circular reference which means that you’ve entered the formula cell reference in the formula. For Example, =D2+D3+D5+D4+D6+D7+D11 in the D11 cell. Usually, you’ll get a circular reference warning pop-up when this happens. But, if you click OK, you’ll get 0 as result. 

Regardless of the error you may have, you can check and resolve them. 

1. Select your entire data and go to Formulas Tab.
2. Click on Error Checking.
   
3. Pick one of the options.
   • Error Checking: Looks for common formula errors. If you click on this option, pick Restore to Calculated Column Formula on the Error-checking box to resolve the problem. 
   • Trace Error: Traces cells that are causing an error in the formula with an arrow.
   • Circular Reference: Identifies which cell is the circular reference. Note that this option is greyed out if there is no circular reference.

Reset Iteration Number Calculation

In Excel, Iteration means the number of times recalculation is done in a worksheet until it meets a certain numeric condition. By default, Maximum Iterations is set to 100. Meaning, Excel will recalculate the worksheet 100 times. But, after Iterative Calculation reaches the limit, Excel will halt recalculating which can be a major cause of your formulas aren’t updating.

Now, you can increase the maximum iteration number a little more from the Excel Options menu. But note that when you set a higher iteration number, it will also increase the recalculating time of the worksheet. 

1. Click the File Tab and go to the Options menu.
   
2. On Excel Options, click on Formulas. 
3. Under the Calculation options, set the Maximum Iterations as you wish and click OK.
   

Reset Processor Number for Calculation

We all know that when working with extremely massive spreadsheets, Excel loses its app efficiency and lags. If your formula is not automatically updating, it’s because the recalculation process is taking a lot of time. Your PC’s processor is responsible for all of these.

By default, Excel automatically identifies and uses all processors of your PC. However, you can also determine the number of processors Excel uses to increase the recalculation time of a formula. 

1. From File Tab, navigate to Options.
   
2. On Excel Options, select the Advanced menu. 
3. Scroll until you locate the Formulas section. Pick Manual to enter your own Processor Number.
   
4. Click OK.

Launch Excel in Safe Mode

If you use third-party add-ins in Excel, there’s a high chance it can interrupt the recalculation or used formulas. To check whether these add-ins are the culprit, you can launch Excel in Safe Mode. 

For this, hold down the Ctrl Key and click on Excel App. Once a Safe Mode confirmation window appears, release the Ctrl key and confirm. If your Excel app launches normally with no errors, add-ins aren’t the problem.

However, if you encounter an error, remove add-ins from your Excel temporarily.

1. From the File Tab, click Options. 
   
2. On Excel Options window, select the Add-ins category. On Manage menu, pick COM Add-ins from the drop-down list and hit Go.
   
3. Now, select an Add-In and hit Remove. 
   

Update Excel

If the problem still persists after trying all of the above fixes, your current Excel file might be corrupted. To test this, as a primary workaround, you could copy the entire data and paste it into a new workbook. The formula should recalculate automatically on your new sheet now.

However, if it doesn’t, it’s time to update your Excel app. 

1. From the File tab, click on Account. 
2. On Office Updates, click on Update Options > Update Now. 
   
3. Close the Microsoft Update window.