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 2010–2016, 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.
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
- 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
- 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.
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.
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!
If your formulas are not still not working make sure you check our post about 8 Most common mistakes when using Excel Formulas.