Fix: Excel Formulas Not Updating

Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android, and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has replaced Lotus 1-2-3 as the industry standard for spreadsheets. Excel forms part of the Microsoft Office suite of software.

Usually, the formulas are automatically updated when the user presses enter or click out of the cell, However, quite recently a lot of reports have been coming in about the program not updating the formulas automatically and the formulas not updating when dragged. In this article, we will discuss the cause of the problem and provide you with viable solutions to solve the problem.

What Causes the Formulas to not Update automatically?

The causes of the problem are pretty simple and straight forward

Now that you have a basic understanding of the nature of the issue we will move on towards the solutions.

Solution 1: Changing Update Settings.

Sometimes, when you save a spreadsheet and exit the application the formula settings get reset. When you load the save file again the formula settings are sometimes set to be updated manually. This means that the formula cells will only update if you manually update them. In this step, we are going to be changing that setting

1. Click on the Formulas tab on the top of the window.

   

2. Select the “Calculation Options” button on the right side.

   

3. Select “Automatic” instead of “Manual“.

   

4. Try to update your formulas now and see if they work.

Solution 2: Changing Format of Cell

In some cases, it was found that if the cell format of your formula is set to “Text” instead of “General” then the formulas will not update. Therefore, in this step, we will be changing the format of the Formulas.

1. Select the cell with the formula in it
2. Click on the Home tab on the top of the window

   

3. Inside the “Numbers Format” settings in the middle of the window, select “General” instead of text.
4. Double–Click on the Formula cell again and press “Enter” to update the formula automatically

Solution 3: Checking for Mistypes

If there is a space in the start of the formula then the program will not detect it as a formula and just as simple text, therefore, it will not update. Also, sometimes there is an apostrophe at the start of the formula. This apostrophe cannot be seen unless you double click on the cell. In this step, we are going to make sure that there are no mistypes.

1. Double–Click on your formula cell
2. If an apostrophe or space appears up before the formula delete it.

   

   

3. Click out of your cell and Double–Click on it again
4. Press Enter to update the cell

Solution 4: Disabling “Show Formula” Setting

Also, if the “Show Formula” button is toggled on the sheet you are currently working on or any other sheet it will show you the formula instead of the result. Thus, your formula won’t update. In this step, we will be disabling that option.

1. Click on the Formulas tab on the top of the page

   

2. Inside that, on the right side, there will be a “Show Formulas” button

   

3. Make sure that the option is not enabled
4. On the bottom left, if there are any other Sheets opened up instead of the one you are working on, make sure that the “Show Formulas” button is disabled on them also.
5. Now Double–Click on your Formula cell and press “Enter“