An easy way to 'cut the wait time' is to change the calculation mode in Excel. There are different types of calculation available, why an Excel user might want to change from one mode to another and some of the pitfalls to watch out for with manual calculation. Types of Calculation Available in Excel.
I have a formula in a cell which counts the values in 2 other cells. Normally, after entering in the values in these 2 other cells, the formula cell updates automatically when Enter is pressed or when the cells are clicked out of.
- I am on a Mac running OS 10.4.11. Whenever I launch Excel 2008, I have to go to Preferences and set Calculation to automatic. Then Excel calculates automatically until the next time I launch the program.
- 2011-10-12 So if you start up Excel, set the calculation mode to Automatic, then open a Workbook that has been saved in Manual then it will NOT change back to automatic. If you then open a second workbook that was saved in Automatic mode Excel will still be in Manual mode (the second workbook does not change anything).
However, for some reason, the value calculated by the formula does not update automatically. I am having to click on the formula bar, then I have to click enter for the formula cell to do its thing.
Why is this happening?
Jean-François CorbettHow To Change Calculation To Manual In Excel For Mac 2016
migrated from stackoverflow.comNov 5 '14 at 18:11
This question came from our site for professional and enthusiast programmers.
7 Answers
A likely cause is that Calculation is set to manual. To change this to automatic in the various versions of Excel:
2003: Tools > Options > Calculation > Calculation > Automatic.
2007: Office button > Excel options > Formulas > Workbook Calculation > Automatic.
2010 and newer: File > Options > Formulas > Workbook Calculation > Automatic.
On Mac:
- 2008: Excel Preferences > Calculation > Automatically
In some cases you may prefer to keep it set to manual, for example if there are many heavy calculations to perform. In such cases, you can simply press F9 when you want the calculations to update.
Jean-François CorbettJean-François CorbettConfirm with Excel 2007: Office button > Excel options > Formulas > Workbook Calculation > Automatic.
Short key to refresh
Ctrl+F9
I had a case of this just now on Excel 2010: a particular spreadsheet that would not auto-recalculate. I changed the setting as indicated above; but the auto-recalculate still did not work, and upon rechecking the 'Calculation' option, found it had reset itself back to 'Manual' all by itself. Three attempts later and it was still adamant it wanted to be 'Manual' and nothing else.
Copy the contents of the spreadsheet to a new one, and deleted the old one (it wasn't a particularly important spreadsheet, thankfully), and everything was fine. I can only assume that somehow the file had become corrupted.
A good reason to keep backups.
NifleI also have had this problem with a very large spreadsheet that just stopped updating itself over the weekend, but having checked the above solutions, setting were already set to automatic, and sheet is too big to rebuild, so I was at a loss.
My solution was to find an old version of the same excel spreadsheet which was not having thses issuesbefore I archived it, which is of similar size and complexity to the current version.
I opened this spreadsheet, checked that this was still working and autoupdating with current installation of MS Excel and any new automatic office updates (which it did work with), and then just reopened the original spreadsheet. 'Hey presto' it was working again.
I ran into an issue where some cells weren't calculating. I checked all the normal things, like cell type, auto calculation, etc...
What I found to be the problem was the formula and the fact that I surrounded numbers with quotes. That probably turned the cell into a text format cell, even though technically the cell was still a number and listed as one under the formats.
I stripped the quotes and the cells calculated as normal.
In my instance I was using a particular add-in called PI Datalink. Somehow PI's Calculate method was no longer running during the normal workbook recalculate. In Settings I had to change Automatic Update command to Full Calculate and then back again. Once the original setting had been restored, the add-in ran as normal.
Steve TaylorSteve TaylorReposting this snippet that user RFB had (inappropriately) attempted to edit into my answer:
A possible cause is that the Office Prefs file is corrupted. In OSX this can be found in:
Delete this file and restart the OS. A new plist file will be created when Office is restarted. Formulas recalculated again perfectly.
Not the answer you're looking for? Browse other questions tagged microsoft-excelmicrosoft-excel-2003 or ask your own question.
This is driving me a little bit crazy. In my old PC days I used to press either F5 or F9 (I can't remember) to have Excel recalculate.
What is the Mac equivalent?
PS: Perhaps this is not the place to ask. If so, please redirect me. :)
Thomas Ayoub3 Answers
AFAIK it's F9 on Windows .
So did you try the keystrokes Fn+F9 ?
IconDaemonTo recalc in Mac:
Command + =
You can try also changing Excel preferences:Command ,Calculation'Manually' radio button
fn + f9 works if you disable mission control shortcut (it is defined as F9 by default)
