Excel Formulas are not working, not updating & calculating (Quick and Easy fixes!)

Excel Formulas are not working, not updating & calculating (Quick and Easy fixes!) Microsoft Excel
When we talk about Microsoft Excel the first things that come to mind are Excel’s formulas and functions. Can you imagine a case when formulas stop working? Well if you are here maybe you have asked questions like: Why formula is not updating its value automatically? Is my Excel or PC broken? How to make my Excel calculating and updating formulas again? Don’t worry, this may happen to everyone, there is nothing wrong with your Excel!

Excel formulas are not updating

The value returned by Excel’s formula doesn’t update automatically – the cell with the formula continues to show the old value even after changing the values of the dependent cells.

The problem is most likely caused by accidentally changing the calculation setting from Automatic to Manual. To fix this we just have to set the Calculation option back to Automatic.

To change the Calculation option we have to go through these simple steps

  • In Excel 20102016, click on File >> Options >> Formulas >> Calculation options section >> Under Workbook Calculation Select Automatic
  • In Excel 2007, click on the Office button >> Excel Options >> Formulas >> Workbook Calculations here select Automatic
  • In Excel 2003, click on Tools >> Options >> Calculation >> Calculation select Automatic

If you are using new ribbon interface you can go to the Formulas tab > Calculation group, click on Calculation Options and from the drop-down menu select Automatic.
Excel Formulas are not working, not updating & calculating (Quick and Easy fixes!) Microsoft Excel

Excel Formulas are not working, not updating & calculating (Quick and Easy fixes!) Microsoft Excel

Force Excel Formulas to Recalculate!

Ok then if you have really good reason to keep your calculation method to Manual at least how can you refresh all formulas?  To make excel formulas recalculate just hit Calculate button on the ribbon!

If you like using shortcuts you can try one of those:

  • To recalculate the whole workbook press F9 or go to Formulas tab on the ribbon and under Calculation group hit Calculate Now
    Excel Formulas are not working, not updating & calculating (Quick and Easy fixes!) Microsoft Excel
  • To run recalculation only on Active Sheet press Shift + F9 or go to Formulas tab on the ribbon and under Calculation Group hit Calculate Sheet
    Excel Formulas are not working, not updating & calculating (Quick and Easy fixes!) Microsoft Excel
  • If you want to recalculate all the sheets in all open workbooks press Ctrl + Alt + F9

If you need to recalculate only One Formula on one sheet just enter the editing mode by either double click on the cell or pressing F2, then hit Enter!

Excel shows the Formula, not the Result

There are three main reasons why this may happen to you, we will discuss all three of them!

Formula is entered as Text

Frequently when we enter formulas in excel don’t think of the Number Format. However, sometimes we may enter a formula that has been formatted as Text, to check this just select the cell with the formula and look at the Number Format box on the Home Tab.
Excel Formulas are not working, not updating & calculating (Quick and Easy fixes!) Microsoft Excel

If that is the reason just change the Number Format to General and hit Enter!

Show Formulas is turned ON

The second reason why you see your formula instead of the result may be the Show Formulas option.  To turn that option OFF you just have to go to Formulas tab and under Formula Auditing turn off Show Formulas button.
Excel Formulas are not working, not updating & calculating (Quick and Easy fixes!) Microsoft Excel

There is a space before your Formula

The last case in which you can have cells showing formulas instead of results is when you have entered a space or apostrophe before the Formula (Equal sign).
In that case Excel formats the cell as Text and you won’t see the result. To fix this issue, just delete the leading space or apostrophe from the cell!
Excel Formulas are not working, not updating & calculating (Quick and Easy fixes!) Microsoft Excel

If your formulas are not still not working make sure you check our post about 8 Most common mistakes when using Excel Formulas.

 

 

One thought on “Excel Formulas are not working, not updating & calculating (Quick and Easy fixes!)

Leave a Reply