Excel wont re calculate cells unless I click in the cell 3

[Davefeet]

I've tried to re calculate Excel cells but for some reason using F9 or Shift+F9 will not work. I have to actually go inside the cell and then press enter and it will recalculate. I'm not changing anything when I do this.

Any suggestions?

SLC: Greatest snow on earth!!!

 

[KenWright]

Copy an empty cell, select all your data and do Edit / paste Special / Add. Sounds like it is all formatted as text.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[01Patrik]

It sometimes happends to me when the cells are linked to other workbooks (even though they are open). What you can do if this is the case is to save the linked wrokbook and close it, then refresh the links (edit / edit links, select 'refresh values'). This should solve the problem.


If the links are internal (no external sources), then choose tools/options, select calculation tab, 'calc sheet'.



// Patrik

 

[Davefeet]

There are no external links, I think it is a bug in Excel.
It will not recalculate unless I actaully go in the cell and then press enter.


SLC: Greatest snow on earth!!!

 

[01Patrik]

Perhaps it is. Just saving, closing & reopening won't help you either?



// Patrik

 

[Davefeet]

nope.




SLC: Greatest snow on earth!!!

 

[01Patrik]

Check tools / options - calculation - make sure it isn't set to manual.

Else, check the formats on the cells - if its set to "text" then the problem you are describing might appear - change to general.



// Patrik

 

[KenWright]

1) It is not a bug in Excel
2) You say saving, closing, reopening doesn't help, so it is not the calculation status being manual as opposed to Automatic as it would update when you do these or hit F9 or CTRL+F9.
3) What happened when you tried what I suggested?
4) If when you tried what I suggested, you had copied a cell that had been formatted as text, then it might not have worked, in which case type a 1 in a cell and make sure it is numeric (It will right align), and then copy that cell, select all your data and do Edit / paste Special / Multiply.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Scott02]

Just to throw my two cents in, I've had the same problem before when I insert a column to the right of another that is formatted as text. If I add a formula, then format as General, it still won't calculate until I edit the cell and hit return. I always format the column then add the formula and haven't had any more problems. Good luck.

 

[KenWright]

Hi Scott, that is because when you insert a row/column it takes on the characteristics of the row/column above/left of where you insert. 3) and 4) of my last note would also fix that if the data in it was numeric, or if formulas you can select the range and do Edit / replace, replacing = with = ( yes I know they are the same :-> ), as it coerces it back to a formula.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[krinid]

Ken,
What does CTRL F9 do? It seems to minimize the active window, but in the above context it seems to have something to do with calculations.

 

[KenWright]

Oops - my bad - Apologies. It should have been CTRL+ALT+F9 which will force a global recalculation.

Thanks for the catch

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[krinid]

Ken,
AH! I see, if I had known about CTRL-ALT-F9 I probably would have picked up on that myself. Next question, what's the difference between a regular F9 and the global CTRL-ALT-F9? The global one seems to be doing something fancy judging by the different display on the status bar, but they both seem to recalculate all formulas of all sheets of all open books.

 

[KenWright]

This link will give you a very nice one page summation of all you could want to know wrt this, in far more eloquent prose than I could come up with

http://www.decisionmodels.com/calcsecrets.htm

Regards
Ken...................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[krinid]

Ken, cheers! Very useful page.

 

[KenWright]

You're welcome. Not just the page that is useful though, the whole site has some fascinating insights into Excel memoty limits/ calculation blah blah blah. Lots of good stuff to trawl through in slow time

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------