Excel doesn t update value unless I hit Enter

Question

I have a very annoying problem in one of my worksheets in a workbook  I am using EXCEL 2007  Any cell s value will not be updated unless I hit ENTER  Either if the formula in the cell includes an if condition  or a VLOOKUP function or even an AVERAGE function  All the calculations are set to automatic  Application Calculation   xlAutomatic  and even the calculations for the specific worksheet are enabled  like   ws EnableCalculation   TRUE    Furthermore  the ScreenUpdating is set to TRUE  After I hit the ENTER or I drag down the right corner  the cells will be updated  and they will keep being updated if I make any change  However  after saving the file and reopening it again they will be frozen again  I haven t figured out exactly when they will stop being updated again  All the formatting are set to General or number    Especially in IF conditions  when I check the calculations through the evaluate feature  the result is correct but it is not passed on the screen   Any suggestion  This thing is driving me crazy

User · Answer

It sounds like your workbook got set to Manual Calculation. You can change this to Automatic by going to Formulas > Calculation > Calculation Options > Automatic.

Location in Ribbon

Manual calculation can be useful to reduce computational load and improve responsiveness in workbooks with large amounts of formulas. The idea is that you can look at data and make changes, then choose when you want to make your computer go through the effort of calculation.

User · Answer

This doesn t sound intuitive but select the column you re having the issue with and use  text to column  and just press finish  This is the suggested answer from Excel help as well  For some reason in converts text to numbers

User · Answer

Select all the data and use the option  Text to Columns   that will allow your data for Applying Number Formatting  ERIK

User · Answer

Executive summary   TL DR  Try doing a find  amp  replace of     with      Yes  replace the equals sign with itself  For my scenario  it forced everything to update   Background  I frequently make formulas across multiple columns then concatenate them together  After doing such  I ll copy  amp  paste them as values to extract my created formula  After this process  they re typically stuck displaying a formula  and not displaying a value  unless I enter the cell and press Enter  Pressing F2  amp  Enter repeatedly is not fun

User · Answer

I ran into this exact problem too   In my case  adding parenthesis around any internal functions  to get them to evaluate first  seemed to do the trick   Changed   SUM A1  SUBSTITUTE A2  x   3  1   A3     to   SUM A1   SUBSTITUTE A2  x   3  1    A3

User · Answer

I Encounter this problem before  I suspect that is some of ur cells are link towards other sheet  which the other sheets is returning  NAME  which ends up the current sheets is not working on calculation   Try solve ur other sheets that is linked

User · Answer

Found the problem and couldn t find the solution until tried this    Open Visual Basic from Developer tab  OR right-click at any sheet and click  View code   At upper left panel  select  ThisWorkbook  At lower left panel  find  ForceFullCalculation  attribute Change it from  False  to  True  and save it   I m not sure if this has any side-effect  but it is work for me now

User · Answer

I have the same problem with that guy here  mrexcel com forum excel-questions 318115-enablecalculation html Application CalculateFull sold my problem  However I am afraid if this will happen again  I will try not to use EnableCalculation again

[excel] Excel doesn't update value unless I hit Enter

Examples related to excel

Examples related to vba

Examples related to excel-formula

Examples related to cell

Examples related to enter