Bottom Line: Learn about the different calculation modes in Excel and what to do if your formulas are not calculating when you edit dependent cells.
Skill Level: Beginner
Watch the Tutorial
Download the Excel File
You can follow along using the same workbook I use in the video. I've attached it below:
Why Aren't My Formulas Calculating?
If you've ever been in a situation where the formulas in your spreadsheet are not automatically calculating as they should, you know how frustrating it can be.
This was happening to my friend Brett. He was telling me that he was working with a file and it wasn't recalculating the formulas as he was entering data. He found that he had to edit each cell and hit Enter for the formula in the cell to update.
And it was only happening on his computer at home. His work computer was working just fine. This was driving him crazy and wasting a lot of time.
The most likely cause of this issue is the Calculation Option mode, and it's a critical setting that every Excel user should know about.
To check what calculation mode Excel is in, go to the Formulas tab, and click on Calculation Options. This will bring up a menu with three choices. The current mode will have a checkmark next to it. In the image below, you can see that Excel is in Manual Calculation Mode.
When Excel is in Manual Calculation mode, the formulas in your worksheet will not calculate automatically. You can quickly and easily fix your problem by changing the mode to Automatic. There are cases when you might want to use Manual Calc mode, and I explain more about that below.
Calculation Settings are Confusing!
It's really important to know how the calculation mode can change. Technically, it's is an application-level setting. That means that the setting will apply to all workbooks you have open on your computer.
As I mention in the video above, this was the issue with my friend Brett. Excel was in Manual calculation mode on his home computer and his files weren't calculating. When he opened the same files on his work computer, they were calculating just fine because Excel was in Automatic calculation mode on that computer.
However, there is one major nuance here. The workbook (Excel file) also stores the last saved calculation setting and can change/override the application-level setting.
This should only happen for the first file you open during an Excel session.
For example, if you change Excel to manual calc mode before you save & close the file, then that setting is stored with the workbook. If you then open that workbook as the first workbook in your Excel session, the calculation mode will be changed to manual.
All subsequent workbooks that you open during that session will also be in manual calculation mode. If you save and close those files, the manual calc mode will be stored with the files as well.
The confusing part about this behavior is that it only happens for the first file you open in a session. Once you close the Excel application completely and then re-open it, Excel will return to automatic calculation mode if you start by opening a new blank file or any file that is in automatic calculation mode.
Therefore, the calculation mode of the first file you open in an Excel session dictates the calculation mode for all files opened in that session. If you change the calculation mode in one file, it will be changed for all open files.
Note: I misspoke about this in the video when I said that the calculation setting doesn't travel with the workbook, and I will update the video.
The 3 Calculation Options
There are three calculation options in Excel.
Automatic Calculation means that Excel will recalculate all dependent formulas when a cell value or formula is changed.
Manual Calculation means that Excel will only recalculate when you force it to. This can be with a button press or keyboard shortcut. You can also recalculate a single cell by editing the cell and pressing Enter.
Automatic Except for Data Tables means that Excel will recalculate automatically for all cells except those that are used in Data Tables. This is not referring to normal Excel Tables that you might work with frequently. This refers to a scenario-analysis tool that not many people use. You find it on the Data tab, under the What-If Scenarios button. So unless you're working with those Data Tables, it's unlikely you will ever purposely change the setting to that option.
In addition to finding the Calculation setting on the Data tab, you can also find it on the Excel Options menu. Go to File, then Options, then Formulas to see the same setting options in the Excel Options window.
Under the Manual Option, you'll see a checkbox for recalculating the workbook before saving, which is the default setting. That's a good thing because you want your data to calculate correctly before you save the file and share it with your co-workers.
Why Would I Use Manual Calculation Mode?
If you are wondering why anyone would ever want to change the calculation from Automatic to Manual, there's one major reason. When working with large files that are slow to calculate, the constant recalculation whenever changes are made can sometimes slow your system. Therefore people will sometimes switch to Manual mode while working through changes on worksheets that have a lot of data, and then will switch back.
When you are in Manual Calculation mode, you can force a calculation at any time using the Calculate Now button on the Formulas tab.
The keyboard shortcut for Calculate Now is F9, and it will calculate the entire workbook. If you want to calculate just the current worksheet, you can choose the button below it: Calculate Sheet. The keyboard shortcut for that choice is Shift + F9.
Here is a list of all Recalculate keyboard shortcuts:
| Shortcut | Description |
| F9 | Recalculate formulas that have changed since the last calculation, and formulas dependent on them, in all open workbooks. If a workbook is set for automatic recalculation, you do not need to press F9 for recalculation. |
| Shift+F9 | Recalculate formulas that have changed since the last calculation, and formulas dependent on them, in the active worksheet. |
| Ctrl+Alt+F9 | Recalculate all formulas in all open workbooks, regardless of whether they have changed since the last recalculation. |
| Ctrl+Shift+Alt+F9 | Check dependent formulas, and then recalculate all formulas in all open workbooks, regardless of whether they have changed since the last recalculation. |
Macro Changing to Manual Calculation Mode
If you find that your workbook is not automatically calculating, but you didn't purposely change the mode, another reason that it may have changed is because of a macro.
Now I want to preface this by saying that the issue is NOT caused by all macros. It's a specific line of code that a developer might use to help the macro run faster.
The following line of VBA code tells Excel to change to Manual Calculation mode.
Application.Calculation = xlCalculationManual
Sometimes the author of the macro will add that line at the beginning so that Excel does not attempt to calculate while the macro runs. The setting should then changed be changed back at the end of the macro with the following line.
Application.Calculation = xlCalculationAutomatic
This technique can work well for large workbooks that are slow to calculate.
However, the problem arises when the macro doesn't get to finish—perhaps due to an error, program crash, or unexpected system issue. The macro changes the setting to Manual and it doesn't get changed back.
As I mention in the video, this was exactly what happened to my friend Brett, and he was NOT aware of it. He was left in manual calc mode and didn't know why, or how to get Excel calculating again.
Therefore, if you are using this technique with your macros, I encourage you to think about ways to mitigate this issue. And also warn your users of the potential of Excel being left in manual calc mode.
I also recommend NOT changing the Calculation property with code unless you absolutely need to. This will help prevent frustration and errors for the users of your macros.
Conclusion
I hope this information is helpful for you, especially if you are currently dealing with this particular issue. If you have any questions or comments about calculation modes, please share them in the comments.
When you turn on the computer and the first spreadsheet you open is in manual mode, if you open more spreadsheets while the first spreadsheet is open, these inherit the manual mode.
¿True or False?
Hi Juan Carlos,
That is true/correct! I just rewrote the section in the article titled “Calculation is an Application Level Settings” to help explain this more clearly.
The calculation mode is NOT associated with or attached to the file. It is a setting at the (Excel) application level that applies to all workbooks you have open on your computer.
Therefore, the setting does NOT travel with the workbook. If you are in manual calc mode and send a file to your co-worker, it will NOT change their computer to manual calc mode. Their computer will remain in the calc mode that it was already in, which is usually automatic calc mode.
I hope that helps. Thanks again and have a great day! 🙂
Hi Jon,
Thanks for addressing this topic. My follow-up question – would it be the same for all online Excel files? I primarily work in SharePoint and different users have the same access to the same files. Is there any risk one user could switch the application level setting and affect all the SharePoint files?
We have not had this trouble in the past but your article makes me wonder how this affects common spreadsheets used in SharePoint or the online version.
Personally, I have not needed to use manual calculation in some time since I believe the PC hardware finally caught up with the software demands. I use a Lenovo laptop with an i7 processor with 16 gigs of RAM. Excel calculates quickly, even in a monster 10 meg spreadsheet with 30+ tabs/worksheets in the file. A reasonable processor can move through the calcs rather quickly in current times. Microsoft must have generally improved the calculating performance.
On the topic of one file affecting others – I would love to hear your expert advice on recommendations for formulas that link to independent spreadsheets on SharePoint or files used in the cloud. Generally linking between files.
One issue we have is that once the file connecting links are established, it’s difficult to rename the files or to move the files to another subfolder. I hope you consider a future topic of linking between files.
Thanks for your great Blog topics – they are always interesting and informative!
-Krist
Hey Krist,
Great question! If you are working in the online web app (browser) version of Excel then this should not be an issue. The online version of Excel does not have Manual Calc mode yet, and I don’t know that it will in the future.
If you are opening the SharePoint files on the desktop app, then the same rules apply. Whatever calculation mode that Excel is in will apply to all files you open on the desktop app.
However, opening a file that was in manual calc mode on someone else’s computer will NOT change your computer to manual calc mode.
The only way this can really happen is if you change the calc mode or if a macro changes the calc mode.
I love the suggestion on the post about formula links with SharePoint or OneDrive files.
We do have a post that covers formula links.
https://www.excelcampus.com/functions/vlookup-other-workbooks/
But it does not cover your question on best practices for renaming files, especially ones stored on cloud drives.
We will add that to our list for future posts.
Thanks again and have a nice day! 🙂
Hey Krist,
I misspoke on my previous reply. If you are using the desktop app and open a file that was saved in manual calc mode by someone else, then that can change your Excel into manual calc mode. This ONLY happens if the file is the first file you open in the Excel session.
I added a section to the post titled “Calculation Settings are Confusing!” that explains this behavior in more detail.
Thanks again!
I often have issue with user defined functions not calculating when not on the active sheet.
One other reason to switch to manual calculation is when you are changing links to data files and there are a lot of calculations that have to change the link. Switching to manual speeds up the change of the link.
Thanks for the tip, Timothy!
Hi Jon,
I have a question regarding the loan amortization schedule spreadsheet you used in your video. In the past, I would be able to run numbers on this spreadsheet and the rows would expand based on the number of payments that were required to satisfy the loan. However, now if I enter 15 years the rows do not expand to show me the full schedule. Do you know why that is? I’m assuming its something MS did when they updated Excel. Did they change the formula? I’m on Excel 2016. Thank you!
By the way, its set for automatic calculation.
Dear, I have to thank you for your efforts.
Could you please provide me by the pivot table ( explain how to do with many financial statement for many companies) I will be appreciate your help.
Regards
I’m trying to sum three cells that contain formulas (=sum(A1,B1,-C1)). The formulas state that if a number is not found, return blank. I’m finding that if C1 doesn’t contain a number, the summation cell returns #Value. Any suggestions? Am I subtracting C1 correctly? It works if the C1 formula returns a number. Using Microsoft 365.
Would this work for you?
=sum(a1,b1,if(isnumber(c1),-c1,0))
OMG, thank you so much. Works like a charm!!
Thanks! Saved me a ton of screwing around looking for the fix.
I switch to manual calculation a lot because I use a lot of very large files. I have found it extremely helpful to put the Manual and Automatic buttons on the Quick Access Toolbar. Currently those buttons have checkboxes next to them which makes it easy to switch and to note when I have forgotten to put it back to Automatic.
Hi Jon. I have an issue and I couldn’t fix it with any of your tips. When I make many scans at once, the calculations on the cells that depend on them work randomly. Some show the result, some not. I couldn’t find a pattern but it is very frustrating. Even calculating manually doesn’t work. Strange is that the file was working perfectly yesterday and nothing change on it.
I noticed that in the status bar appears “Calculate”. Clicking on it also sometimes works, some others nothing happenes.
Thank you for your time if you take a look at this!
AutoSum not calculating filtered data, any suggestions why?
I need empty cells as part of that column, could that be messing me up?
The advice in this video was spot on and corrected my problem. Now the big mystery is how the setting for calculation got set to manual as there is no VBA code I am doing and while not beyond the realm of possibility of accidentally turning it on, I was glad to come across this video and the solution!
This was interesting but didn’t help me at all! I’ll share what did, however, in case it helps someone else. I know next to nothing about Excel. I downloaded a free checkbook register that I have used for a long time. Every couple of years, it acts like it has “forgotten” to perform functions, i.e. I’ll put in a credit or debit and, instead of proceeding to give me my running total, it just leaves it blank and my cursor drops to the cell below. Very frustrating when this happens. I checked and I was in automatic mode. So this is what fixed it: I clicked on the last cell that had a running total and found the formula that had produced it. I copied and pasted it into a Word document so I could alter it. There, I added “1” to every number in the formula. I then went back to my workbook, place the cursor where my next running total should be, and pasted the new formula onto the proper line. Not only did it calculate that cell correctly but the workbook resumed doing what it was supposed to do thereafter. I realize this is very basic to those who know Excel but my curiosity is piqued. Why did my workbook stop functioning? And why did this fix it?
This solved my problem! Stupid sneaky little checkmark. Thank you so very much!
You saved me HOURS of banging my head on my desk. The Calculation Options dropdown was somehow changed to Manual – THANK YOU !!!
Need help formula will not drag down to cell below keep getting a #num!
Thanks! It is helpful
Why will excel fail to calculate when you are in a manual mode, even if i use macros or calculate now the sheet wont calculate or update.
ActiveCell.Formula = “=ColorCount(A5:A5004,A4)” is a formula I add in a VBA Macro. It generates “=@ColorCount… However, it does NOT automatically calculate. The ONLY way I can see the result is to select the cell and in the formula bar press ENTER. What can I do to correct this.
Date…….Day………….Time In…………Time Out………..Time In…………Time Out………..Total Hours
5/24/2022………Tuesday…….08:05AM……..12:00PM………..07:00PM ……….12:01 AM ………..#VALUE!
I used this =(E9-D9)+(H9-G9) Formula. But my computer didn’t work. I Go to Region-Additional Date & Time- number-list operator. But didn’t work formula.
I keep encountering this issue. I’m on auto calculate. I hit F9. The number type is set to general. There are no characters before the =. It just won’t calculate. It’s treating fields with formulas as text and it won’t budge.
Ahh, to be using a legacy spreadsheet program well into the 21st century…
Also having this problem, and since it just started recently and I haven’t changed any settings in the files I have been literally using for years without this problem, I have to assume it’s something to do with a Microsoft update.
So what do you do when you have reviewed all those settings and the formula won’t calculate in a Table. Instead it just displays the formula. It’s in automatic in both the ribbon and in the options settings. I have to pull the data out of the table, open a new sheet, past the data, then rewrite the formula, the copy down. It only copied the values and did not calculate until I hit calculate now. Which would suggest manual mode, but again none of those options are enabled. Further when I went back to the table and said calculate now, it still kept the formula visible. No calculation. I had to copy and paste the values from the new sheet back into the table.
The workbook has no macros, and I am using the 2016 version.
So if you have an answer, it would be nice to know. Thanks for the video, it helped me trouble shoot everything else that might possibly be the problem
Mike, I know it has been some time since you posted this question, but I am having the same issue as you were. Did you ever find a resolve to this?
Thanks in advance,
Thanxx dear, it worked !
I expanded my table (stock table, not mine) formulas using Command D. The formula is correct in all cells, but the first expanded cell is not calculating. It reads $0. All the others after that are fine, which obviously a problem since I’m working with an incorrect sum. Seems like there may be something wrong with just this cell, but everything looks good. Help! Thanks!
Can anyone see where I am going wrong please? My IF table is only checking for the fist 2 queries and is not working for the remaining 5 (over 5999)
=IF(G6=2001,G6*B7,IF(G610001,G6*B9,IF(G6>20001,G6*B10)))))
I have tried every combination of numbers but it will not calculate the correct rates after 2001
The formula will work for 0-200 volumes (rate of 1.05 which is B6) it updates for volumes between 200 and 2001 at a rate of .98 (B7) but it will not update for any volumes over 5000 with the remaining rate changes – looking at it for ages but can’t see my error 🙁 the rates are – Up 200 rate of 1.05 (B6)
201 to 2000 rate of .98 (B7)
2001 to 5000 rate of .97 (B8)
5001 to 10000 rate of .96 (B9)
20000 rate of .95 (B10) so the first 2 rates are working fine but nothing after that, have changed rates, etc but cant see the issue – any help appreciated
My spreadsheet is set to automatically calculate there are no macros yet it is still not calculating only displaying the formula. I have changed the formatting of the cells and still no luck. What else can I check?
HI JB. i am having similar issues. Did you receive any answers please?
My spreadsheet is in Automatic but still will not automatically calculate now?
I ran into a similar problem. But in my case the cells with formulas didn’t show the result but the formulas themselves. Turned out I accidentally had hit the CTRL+’ combination and turned the option “Show Formulas” on with that on the Formula’s tab. Found this article while searching for a solution and it pointed me in the right direction. 🙂
I have various cells in more than one different spreadsheet that show the formula and don’t calculate, even though auto-calculate is ON and show formulas is OFF. I have Excel for Mac version 16.86.
I have various cells in more than one different spreadsheet that show the formula and don’t calculate, even though auto-calculate is ON and show formulas is OFF. I have Excel for Mac.
I FOUND THE PROBLEM. If the cell format is “Text”, formulas are not computed. Furthermore, if you change the cell format to something else such as “General”, the formula continues to be shown. However, if you copy and paste it back into the same cell, THEN the formula calculates!
I have various cells in more than one different spreadsheet that show the formula and don’t calculate, even though auto-calculate is ON and show formulas is OFF. I have Excel for Mac. I FOUND THE PROBLEM. If the cell format is “Text”, formulas are not computed. Furthermore, if you change the cell format to something else such as “General”, the formula continues to be shown. However, if you copy and paste FROM THE EDIT LINE (and not directly from the cell itself) back into the same cell, THEN the formula calculates!
Thank you for this insightful guide! I was struggling with Excel not updating my formulas, and your step-by-step troubleshooting tips really helped me pinpoint the issue. It’s great to have a resource like this to refer to whenever I encounter formula problems. Keep up the great work!