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.

Excited

If calculation is definitely not being disabled, then I'd guess you have a circular reference problem. – Weeks

I checked all sheets and non of them has the Circular Reference at the status bar next to Ready. Moreover, the Check Circular Reference option is disabled in the Formula tab – Excited

Is it only that one worksheet? If so, have you ever used EnableCalculation on that sheet to disable calculations? – Weeks

I have used this flag but now even if I set in all worksheets this flag to True, the problem isn't resolved. – Excited

I have heard before of instances where using that property caused problems. I'd try recreating that sheet. – Weeks

I will try to do that then. Do you know how can I isolate a number of worksheets and prevent from calculating when I don't need them? Because some of them are pretty heavy and take ages to calculate but I don't use them. – Excited

That's a whole separate question, and the answer will depend on the nature of your model. – Weeks

It seems that this flag was the problem. 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. My other problem is that I have a sheet (sheet2) with more than 10,000 cells which are formula driven based on values in another sheet (sheet1). By making any change in sheet1 it takes ages to update sheet2, but I don't want to use the values in sheet2 yet. So I want to isolate them for a while. – Excited

Answer 1

Executive summary / TL;DR:
Try doing a find & 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 & 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 & Enter repeatedly is not fun.

Kampong

You saved my life. Thanks for the jugaad – Execrable

Such an excellent and simple solution, works in Excel 2016. I created this as a Macro to do it automatically: Sub InternalRefresh() Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub – Wherever

This worked for me, but I still do not understand why it was necessary. This appears to be a bug in Excel. – Hekate

This worked for me, wish I could have solved it programmatically though. – Irresolution

Thanks .. finally! It is still a hand action, but at least I can do it in one go. I had date values "22/03/2022" that don't want to format as "2022/03/22". Instead of "=" I therefore replaced the "/" by "/" .. since the values all have this char. – Cooncan

I had the same problem, Excel "Format cells..." shows a Date, but doesn't recognize it as such. So replacing the year 2024 with 2024 and it works! – Yuji

Answer 2

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

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.

Husein

No, as I have said, the calculations are set to Automatic, so the issue is not there. Thanks however for the prompt reply. – Excited

Menu bar looks bit different on recent Mac versions (with the dark green background), but approximately the same place. Myself and a coworker thank you! – Worriment

This is not answering the question. You would THINK it does... but it doesn't. – Grandmamma

Answer 3

Found the problem and couldn't find the solution until tried this.

1. Open Visual Basic from Developer tab (OR right-click at any sheet and click 'View code')
2. At upper left panel, select 'ThisWorkbook'
3. At lower left panel, find 'ForceFullCalculation' attribute
4. 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.

Yolanthe

You, sir, are a lifesaver. – Beller

This works bu has tons of side-effects learn.microsoft.com/en-us/office/vba/api/… – Knockwurst

This is the REAL solution! – Binding

Answer 4

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.

Lyman

Answer 5

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.

Excited

Answer 6

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

Wileywilfong

Great trick to use to get around excels stupid quirks. Especially when you are talking thousands of rows. – Dissolution

Answer 7

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

Budge

Answer 8

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) 

Interrogate

Answer 9

Found a simple solution.

Let's say once cell A1 is changed, so you expect cell B1 change the color by conditional formula.

Make the problematic cell - B1 has a formula with dependency to cell - A1

So, for example instead of my cell B1 just have text value "Text", i'll change it to formula like:

=IF(A1>0,"Text","Text") - means: if A1 > 0, then insert value "Text" to B1

The result is - each time cell A1 is recalculated, it forces also cell B1 color to be updated.

Dhumma