What happens when good Excel formulas go bad? A workbook of mine that worked fine for several years, when using Excel 2003, suddenly refused to update all the formulas, after a switch to Excel 2010. Even more mysterious, the calculations worked fine on some machines, but not others. Why are Excel formulas not calculating on some computers?
Hint: It wasn’t one of the obvious solutions.
Check for Automatic Calculation
When someone complains about Excel formulas not calculating , it’s usually because the Calculation setting has been changed to Manual, instead of Automatic.
That can happen if the first workbook that you open in an Excel session was saved as Manual calculation. That setting affects all the other workbooks the you open during that session.
In this case, the workbook calculation was set to Automatic on all the machines – that’s the first thing we checked.
To check the setting, click the Formulas tab on the Excel Ribbon, and click the Calculate Options drop down.
Or, go to the Excel Options window, and click Formulas.
Force the Calculation
Even if the Calculation option is set for Manual, you can use a Ribbon command or keyboard shortcut to force a calculation.
Click the Formulas tab on the Excel Ribbon, and click Calculate Now or Calculate Sheet.
n the tooltip that is shown in the screen shot below, you can see that the shortcut for Calculate Sheet is Shift + F9.
We tried using the Calculation shortcuts, including Ctrl + Alt + Shift + F9 (that one recalculates everything, and starts your car).
However, none of the shortcuts had any effect on the non-calculating cells in this workbook. I also tried a macro that did a full calculation, and that didn’t work either. Sigh.
Kick Start the Formulas with VBA
Next I wrote a macro that replaced all the “=” signs at the start of all formulas in the workbook. This has a similar effect to manually selecting a cell, clicking in formula bar and pressing the Enter key, to recalculate the cell.
Sometimes that brings a tired formula back to life, but not in this case. These formulas weren’t sleeping, they were deceased!
Change the Named Range in the Formula
I spent a considerable amount of time in Google, looking for other suggestions, but didn’t find anything new. So, I kept tweaking and testing, going through the file one worksheet at a time.
Finally, I found the formulas that seemed to be causing the problem. They were SUMIF formulas that referred to a named range on another worksheet.
=IF(B7=””,0,SUMIF(DataEntryStep2a,E7,$D$7:$D$22))
There was no obvious reason why that wouldn’t work in Excel 2010, but I was getting desperate. So, I changed the named range to a worksheet reference, using cells on the same worksheet.
=IF(B7=””,0,SUMIF($E$7:$E$22,E7,$D$7:$D$22))
Miraculously, that solved the problem! I held my breath, while we tested on a few more machines, and everything calculated as it was supposed to.
Why that worked, I have no idea, but if you run into a similar problem, maybe it will help you too. Of course, this solution could stop working again, at the next lunar eclipse!
[Update] There is problem with SUMIF, SUMIFS, AVERAGEIF and AVERAGEIFS, in Excel 2010, when the references are on a different sheet. Thanks to Stuart Valentine, who posted a link to a discussion of this SUMIFS problem. My named range was on a different sheet, so the location was the problem.
Watch the Video
In most cases though, the problem is the Automatic Calculation setting.
To see the effects of opening Excel workbooks with different calculation option settings, watch this short Excel tutorial video.
_______________
Thanks!!! This was really helpful, and saved me a possibly painful call to the Help Desk.
I faced a similar issue – formulas that referred to other formulas were not getting updated despite the ‘automatic workbook calculation’ being active. This is Excel 2010 running on Windows 8. Replacing the equal sign fixed it.
I am still facing another issue – short cuts such as ‘alt-e-t’ to cut a row don’t work. If I make another workbook active and then go back to the original workbook, the short cuts work again.
None of your solutions have been a perfect one for me, but reading this article helped me focusing my research. I found a page on MSDN that gave me the perfect solution. I force the problematic cells to be dirty by typing the following line in the “change” procedure of the worksheet object code.
Application.Range(myRange).Dirty
It simply tells excel that the cells within the range require a recalculation whenever any change occur in the sheet.
The link to the MSDN reference:
http://msdn.microsoft.com/en-us/library/office/bb687891.aspx
Thanks your information is helpful
I would like to get excel tutorials will u please send them to my mail….thank you…
This is so helpful. Thanks a lot. I met the very same problem, formulas not updating due to setying to manual