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. So, what was the problem?

Hint: It wasn't one of the obvious solutions.

### Check for Automatic Calculation

When someone tells you that formulas aren't calculating, it's probably 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. In 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.

Or watch on YouTube: Excel Calculation Automatic or Manual

_______________

Debra,

Check my comment on your recent SUMIFs post - there is a bug in Excel 2010 with these functions when the arrays are not located on the same sheet as the formula:

http://blog.contextures.com/archives/2012/02/02/excel-sumifs-sum-with-multiple-criteria/#comment-23047

See also:

http://social.technet.microsoft.com/Forums/en/excel/thread/cda9fef8-8ad4-4d56-8939-49b1ae50c4e7

Thanks Stuart! I've updated the article to include your link.

I read your previous comment, but too quickly -- it obviously didn't register in my brain.

Debra - did you save the file as a xl2010 file first?

I noticed calculation problems when using xl2003 files in compatibility mode with xl2010. Saving as .xlsm, closing, and reopening seems to have solved the problem.

Thanks Alex, I did save the file in xlsm format, and still had the problems. Looks like the SUMIF problem is fixed for 32-bit but not 64-bit

Can the averageif function use in pivot table ?

None of these options worked for me.

What did work, however, was ending the excel.exe task in the task manager.

This solution was very helpful. All of a sudden this morning, when copying and pasting between workbooks, or when copying and pasting within a worksheet, or when using auto-fill to copy my Excel formulas within a worksheet, my formulas stopped calculating. It appeared as only the values were being pasted, not the formulas. I searched and searched and all I came up with was solutions having to do with my clipboard being hijacked by malware, which could not have been the problem as I was not online at the time this problem began to occur. Thank you very much for helping me.

Thanks for this post, appreciate additional solutions other than just turning the calculation on, as is suggested in most other forums. Replace "=" was all I needed to fix dead formulas.

Thank you! This was my exact problem and the solution you have worked splendidly. Saved me hours!

Thanks. it solved my problem.Very useful post.

Thanks alot

You solved my problem righ away. Thanks

Not sure how to post a new thread, but:

Why do these two formulas return the same result:

in col L =IF(E3:E133="*VISAMM*",G20,F20)

where col E has the text string, col M should be false or blank

in col M =IF(E3:E133="*VISATB*",G20,F20)

where col E has the text string, col L should be false or blank

If I reverse the Value if TRUE or FALSE cells I get 0.00

I get a return of col F for both IF Statements

I want to return col F as true when the text string is satisfied in the range.

Thanks,Mark

I have had this issue for a while, the file is saved as a 2003 version (due to issue when we first changed over) and sits on a server. The formula is basic =SUMIF('Full List'!D:D,B19,'Full List'!J:J) so it should work ..and it does sometimes. It is not the autocalc setting and does not work even when force calculated, double clicking in the cell gets that cell to work, but not the others. I have a manual fix, I have a macro to save the file to C:\temp (the formula works straight away) and then saves back to the server. From then on it works fine !!! Until the next day when we do this all again. My fear is how many other files are not calculating correctly !

Is there a fix for this now ?

Thanks ...Bill

Hey, thanks a lot, that ws quick and easy fix, on which I had been stressing and scratching my head over. Thanks again.

Hello,

So I am having an issue with one of my formula's I am using the basic "=Sum" calculation. It work for most cells but for a few it is a number off. So it will give me a result for five for something the should be 6. I have two cells each has 3 in it and the result shows 5. I have checked and I have the automatic calculation on. Anyone who could shed some light on this? I would greatly appreciate it.

Thanks...Kristen

Thank you - it's great when I can find a solution without spending hours searching for the solution

Hi There Guys

I recently bought a notebook with windows 8 on. I purchased Office 2010

What I find and cannot solve is that if I open an old document and change values, excel calculates incorrectly. Even if I re-type all the values in the cell and do the summation over it still calculates incorrectly. If I do aa new calc in the same area(just below) it also calculates incorrectly. If I do another calc in the same page but in a different area the calc is fine. New documents is no problem.

Please help... as it were I am also technologically challenged

produk bottel trigger label box labour Total cost

100ml 10.3 .95 .97 1,00 .1 2,00 3,00

100ml 10.30 0,95 0,97 2,00 0,10 2,00 6,02

Thank you Debra!!!

Saved hours of work for me too.

Great job!!!

My formulas were giving me issues only with subtraction. To fix it, I entered the formula with "spaces". For example:

Instead of =B10-D10 I wrote: = B10 - D10

This helped me after all else failed.

Just Thanks easy when you know how!

The cell values will also fail to update properly if your formula refers to a defined function in another workbook.

Thank you!

i have excel sheet which contain data of license and contracs. i want a cell to notify me the expire status in 3 conditions.. 1) 30 and more days= active 2) below 30 = expire soon 3) 0 and below 0 days = expired. can u provide a formula to solve this pls thanks in advance

=IF(DATEDIF(A4,TODAY(),"D")>=30,"Active",IF(DATEDIF(A4,TODAY(),"D")<31,"Expire soon","Expired"))

I have created a formula for you. Put the date in A contract date in column, I am sure this formula will work.

You can use the following formula if you want to avoid formula errors due to date formats:

=IF(A11>TODAY(),"Expired",IF(DATEDIF(A11,TODAY(),"D")>=30,"Active",IF(DATEDIF(A11,TODAY(),"D")<31,"Expire soon","Check Dates")))

I thought i had the same problem - tried lots of solutions...just realised I had 'Show Formulas' activated. Ooops.

Thank you! perfect instructions. Fixed the problem :)

Thank you for helping me solve my calculation issues. I don't know how it got changed from automatic to manual and would never have thought that there would be a setting to make the formulas only change by saving.

Thank you..just like Eric I inadvertently changed my calculation settings to manual. Learn something new every day...

Hello everybody,

I have a problem with external link when used with index function otherwise rest of external link are working perfectly.

I have created a external link after doing all the appropriate setup for automatic update of values the destination file formula updated correctly. even if not opened the source file. This is working perfectly as expected. but when i used the same thing with index function it updated ONLY when source workbook was opened otherwise it don't updated. please please help on this i am stuck with this and not able to continue further development.

BELOW ARE THE TWO FORMULAS. #1 IS UPDATING AUTOMATICALLY. BUT #2 NOT UNLESS SOURCE FILE OPENED.

1> ='D:\SHIVA\[CABLE_2013.xlsx]Customer_collection_2013'!Q4

2> =INDEX('D:\SHIVA\[CABLE_2013.xlsx]Customer_collection_2013'!$A$10:'D:\SHIVA\[CABLE_2013.xlsx]Customer_collection_2013'!$Q$100001,MATCH(A11,'D:\SHIVA\CABLE_2013.xlsx]Customer_collection_2013'!$A$10:'D:\SHIVA\CABLE_2013.xlsx]Customer_collection_2013'!$A$100001,0),17)

Thanks,

Shiva Naik

THANK YOU! This had been making me CRAZY. Easy fix!

It seems there are many reasons why this is happening, but none of the above have fixed my issue which is in Excel 2013.

I should say I have auto calculate on, and on my own excel files ( ie ones created on MY pc - no problems)

However If I get an excel file from a client and try to add the simplest of formulas ( eg If I write ="fred" in cell A1) it should say fred - it just sits there saying ="fred"

This is fairly basic microsoft - have you really not tested this ???

The only solution I have found is to save the incoming file as a csv format and then reload it - bingo - all automatic calculations work perfectly.

I realise this is a long time after you asked the question - but I recently came across the same problem dealing with excel files exported from another program. It turned out the cells were formatted as "Text". Changing that to "General" got them to calculate again.

Thnx a lot... i salute you.. was simply unable to findout why "only my excel sheet cannot calculate"..

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

Hey, Thanks a lot. You saved me hours... This was my issue, too.

Thanks! Solved my problem quickly with no frustration

Many Thanks! Could not think the solution could be too easy. Saved a lot of time and efforts of mine.

Wow, thank you so much!

I have a formula returning N/A and I know it has to do with not dealing with a specific cell returning the result of "= Completed" but I am racking my brain on how to get it in the formula.

@Mike, if the value in A3 isn't in the list on the Manual Pend sheet, you'll get an #N/A error, from the first VLOOKUP.

The Manual Pend sheet is a pivot table and only picking up work items that had been pended. I am trying to determine to total number of days it took to complete a task whether it was pended or not.

Thanks a lot guys...

Superb man

Excel sheet back to behaving as it should. Thanks so much!

@Brandon, Great! Glad the tip helped you

Hi,

I have problem with excel formula,

when I make I G10 Multiply with I10 then they show rust wrong the G10 cell have also divid formula, if I remove the divide formula from G10 and add value then they make result right, you can see screenshot in this link, http://i.imgur.com/Q0j8fTx.png

pleae fixed this problem,

Sajida

Thanks a ton!

I have a problem with formula in excel if I write for example =0.5*2 then ENTER "Error msg displays that my formula is incorrect; but if I change to write =0,5*2 then ENTER "Answer is 1" as I expect: I can see that my computer takes (,) comma instead of (.)! What can I do to reset it to normal

Was racking my brain on a similar issue. I wrote my own functions in VBA that read data across other named ranges on the same work sheet, and changes to their value would not update cells with that function. My problem was the named references were used in the VBA, not as parameters to the function. If you recode your functions even to require a parameter you won't use, but some cell that you know will update when you enter changes, then Excel will refresh the cell calling your function. Excel seems to look in the function property of each cell for references, and if you use the dependency tool, you can see how it inter-relates your cell that isn't updating. You have to select the cell you want to update manually to use the tool (Formula / Formula Auditing, Trace Dependents). Even if you don't have a single cell that updates every time you make changes to data, you can create a hidden row at the bottom and use a countA function to count the number of cells that have data in them. This won't result in any overflow errors because you can't have enough cells to cause an overflow. For example, "=COUNTA(1:60)" can be placed in cell A61. Make one of your parameters to your built in function called "RefreshWhen as variant" and just don't use it anywhere in the function. When you type your formula, include all the necessary parameters your function needs, and the additional reference to cell A61. Now any time data is changed in the cells of rows 1 though 60, A1 recalculates, thus all uses of your function referencing it will recalculate.