Windows

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.

Excel Logo

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

  • Formula 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.
  • Format: 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.
  • Mistyping: 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
  • Show Formula Button: 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.

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.
    Clicking on the Formulas Tab
  2. Select the “Calculation Options” button on the right side.
    Selecting Calculation Options
  3. Select “Automatic” instead of “Manual“.
    Selecting Automatic
  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
    Clicking on Home
  3. Inside the “Numbers Format” settings in the middle of the window, select “General” instead of text.
  4. DoubleClick 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. DoubleClick on your formula cell
  2. If an apostrophe or space appears up before the formula delete it.
    Space behind the Formula
    Apostrophe behind the formula
  3. Click out of your cell and DoubleClick 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
    Clicking on the Formulas Tab
  2. Inside that, on the right side, there will be a “Show Formulas” button
    Disable the “Show Formulas” Tab
  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 DoubleClick on your Formula cell and press “Enter

Leave a Reply

Your email address will not be published.

Expert Tip

Fix: Excel Formulas Not Updating

If the issue is with your Computer or a Laptop you should try using Reimage Plus which can scan the repositories and replace corrupt and missing files. This works in most cases, where the issue is originated due to a system corruption. You can download Reimage by clicking the Download button below.

Download Now

I'm not interested

Close