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.

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?

oshirowanen

Posted 2011-07-08T09:03:43.207

Reputation: 1 858

Answers

158

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 Corbett

Posted 2011-07-08T09:03:43.207

Reputation: 2 219

I have 2007 and somehow this option reverts back to manual. – 3bdalla – 2015-04-25T09:27:49.970

2@3bdalla: Perhaps you have executed a macro that contains the line Application.Calculation = xlCalculationManual? – Jean-François Corbett – 2015-09-29T07:09:13.073

This seems to be workbook related, so it may need to be adjusted for each workbook. – Sablefoste – 2015-11-23T19:01:57.973

@Sablefoste: No, it isn't. At least not in Excel 2010 and earlier... Haven't tested 2013, but see no reason why this would change. – Jean-François Corbett – 2015-11-23T21:03:31.240

@RFB: Please don't edit other possible answers into this one. Just go ahead and post your own answer. I did it for you this time.

– Jean-François Corbett – 2016-07-04T12:54:40.493

In my 20 years plus of using Excel, this is the FIRST time I have come across a sheet that was not set to automatic! – ozzy432836 – 2016-12-09T09:52:14.950

Confirm with Excel 2007: Office button > Excel options > Formulas > Workbook Calculation > Automatic.

Short key to refresh

Ctrl+F9

smstha

Posted 2011-07-08T09:03:43.207

Reputation:

Thank you! We already had the calculation options set to automatic and didn't know why values weren't updating. F9 and Shift+F9 didn't help and left the values the same. Ctrl+F9 not only updated the values, but fixed the problem so it didn't happen again. – april4181 – 2015-12-14T20:52:15.370

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.

So here is my solution to the problem:

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.

Puffin

Posted 2011-07-08T09:03:43.207

Reputation: 71

5If that workbook was an .XLSM or .XLSB it may have had a rogue Worksheet_Change or similar event macro that utilized Application.Calculation = xlCalculationManual and either errored out or simply did not reset it to xlCalculationAutomatic. – Jeeped – 2014-12-22T15:33:19.590

It looks like it is a "known bug" going on at least since 2012, given google results: once you open one file where the "automatic calculation" flag is set to off, all subsequent workbooks you will open will e automatically set to "automatic calculation = off". So the turn-off of automatic calculation is...automatic.

It also happens sometimes that a file gets corrupted and prevents you from re-enabling automatic calculation.

Sometimes there also can be a macro, "hidden" inside a workbook, which programmatically turns off calculation in a specific situation, but there is no way you can know it... until you notice formulas results keep not changing!

Application.Calculation = xlManual

Application.Calculation = xlAutomatic

Sources:

https://answers.microsoft.com/en-us/office/forum/office_2003-excel/excel-sometimes-sneakily-changes-automatic/d9d29b5e-e09d-4fd6-8001-2efeaaf2a71a

https://answers.microsoft.com/en-us/msoffice/forum/all/the-calculation-option-keeps-switching-from-auto/2ed29346-b946-4aaf-9c19-83911eea812e

https://www.automateexcel.com/vba/on-off-calculations/

jumpjack

Posted 2011-07-08T09:03:43.207

Reputation: 288

Reposting 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:

Library/Group containers/*.office/com.microsoft.officeprefs.plist.

Delete this file and restart the OS. A new plist file will be created when Office is restarted. Formulas recalculated again perfectly.

Jean-François Corbett

Posted 2011-07-08T09:03:43.207

Reputation: 2 219

I 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.

JT Singh

Posted 2011-07-08T09:03:43.207

Reputation: 1

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 Taylor

Posted 2011-07-08T09:03:43.207

Reputation: 504

-1

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.

Francesco Alessi

Posted 2011-07-08T09:03:43.207

Reputation: 9

1I suspect that your problem and its solution were different from the one in this question. The fact that there is a different accepted answer means that this problem is different from yours and that solution worked. Typically, the kind of error you describe (turning a value into the wrong type of argument for a formula), will produce an error message. In this question, there was no error message; formulas just required manual recalculation instead of doing it automatically. Your answer is really a somewhat random reference to a different problem you had. – fixer1234 – 2015-02-24T02:14:00.360

This is not the solution. Actually it's an Excel bug which transfers "do not calculate" option from a workbook to another automatically, without warning the user. – jumpjack – 2020-02-27T14:27:08.757

Cells not updating automatically

Answers

So here is my solution to the problem: