You’ve created the reports for your management meeting, and, just before you print copies for the executives, you discover that the totals are all showing last month’s values. How do you fix it—fast?
1. Check for Automatic Recalculation
On the Formulas ribbon, look to the far right and click Calculation Options. On the dropdown list, verify that Automatic is selected.
When this option is set to automatic, Excel recalculates the spreadsheet’s formulas whenever you change a cell value. This means that, if you have a formula that totals up your sales and you change one of the sales, Excel updates the total to show the correct sum.
When this option is set to manual, Excel recalculates only when you click the Calculate Now or Calculate Sheet button. If you prefer keyboard shortcuts, you can recalculate by pressing the F9 key. Manual recalculation is useful when you have a large spreadsheet that takes several minutes to recalculate. Instead of waiting impatiently while it recalculates after every change you make, you can set the recalculation to manual, make all of your changes, and then recalculate at once.
Unfortunately, if you set it to manual and forget about it, your formulas will not recalculate.
2. Check the Cell Format for Text
Select the cell that is not recalculating and, on the Home ribbon, check the number format. If the format shows Text, change it to Number. When a cell is formatted as Text, Excel makes no attempt to interpret the contents as a formula.
After you change the format, you’ll need to reconfirm the formula by clicking in the Formula Bar and then pressing the Enter key.
Note: If you format a cell as General and you discover that Excel is changing it automatically to text, try setting it to Number. When a cell formatted as General and the cell contains a reference to another cell, Excel copies the format of the referenced cell. Choosing any format other than General will prevent Excel from changing the format.
3. Check for Circular References
Look at the bottom of the Excel window for the words CIRCULAR REFERENCES.
Like circular logic, a circular reference is a formula that either includes itself in its calculation or refers to another cell which depends on itself. Be aware that a circular reference can, in some instances, prevent Excel from calculating a formula. Correct the circular reference and recalculate your spreadsheet.
You can fix most recalculation problems with one of these three solutions. Now, fix that report, and get ready for your meeting.
Or continue your Excel education here.
When will you be upgrading your course to Excel 2013?
Thanks for asking, Randi! We currently offer Excel 2013 courses. Check our website to see when it is coming to a location near you! We also offer unlimited training access when you become a member of our Training Rewards program. It includes over 2,500 online courses, many are specific to Excel training. Visit our website for all the details at https://www.pryor.com.
very helpful thanks.
its work in a jiffy.
I was resigned to having to start from scratch on a new book, thanks!!
Thank you so much!
Thank you very Much.
The second one helped me. Thanks! 🙂
Had to say thanks!
Wow. I knew my problem had to be an easy fix, but I didn’t think I’d find it so soon. I have no idea how the Automatic got un-selected under my Calculation Options, but selecting that again got my problem fixed. You just saved me so much time and frustration! Thanks so much!!
Thank you SO much.. I was loosing my mind trying to figure out what was happening!
I changed spreadsheets to automatic several times yesterday, but then when I opened the next spreadsheet I kept having to do it again…and again. I need to know how to lock the automatic button on so that I do not have to go and reset it for every spreadsheet.
Thank you so much. Sorted out my problem immediately. No idea how my workbook changed to manual.
I noticed that a previously saved file with more than one tab selected, also known as group mode, when opened again will default to manual calculation. If this happens you will need to set calculations back to automatic by any method mentioned above.
Thank you soooooooo much 🙂
Great help! Thank you so much! I was already scaried that my complex spreadsheet will be necessary to re-develop again 🙂
Item 2 helped, but I found more detail problem descriptions and explanations at –
It didn’t really work until I closed the spreadsheet and re-opened it.
Very useful, thank you. In my case although the cells were General, not Text, it was item 2. Reformatting to number, and pressing enter in the formula bar made it work. As I had a lot of formulas, the quickest way to get them working was to set formulas to Manual update, select the region, and then repeatedly hit Enter until theyy were all “re-Entered”. Then set it back to Automatic.
Thank you so much. It’s great.
you had the right answer in one click! without me having to go thru 20 thousand steps!! thank you!!
Thanks, Fred. You made my day.
HELP!!! I added a couple of formulas to my budget spreadsheet and now I can not get the spreadsheet back to showing my numbers. It seems to be stuck showing formulas. I cannot afford to lose my work, what can I do to fix this.
thanks a lot this helped me a lot
You are a life saver! This was driving me NUTS as to why the formulas were not working. Yes, the cell was formatted as text. Once I changed it to the NUMBER, it still wouldn’t work until I went to the formula bar and reentered the formula. THANK YOU SO MUCH for this tip!
Adding to the long list of thanks — I was just about to blow a gasket as my formulas seemed to be mysteriously turning to text, and even switching them back to General format or Number didn’t do the trick. Finally followed your advice of clicking in the formula bar and hitting enter. Much much much appreciated, with an F grade to Microsoft for usability.
Another thank you for this simple answer to a problem that has been driving me nuts 🙂 you are my best friend today!!!
Thanks very much. I was able to solve the problem which was driving me crazy.
Thank you for making this information available in such a helpful, easy-to-digest (and free) manner. Much appreciated.
Nice one my friend thank you.
Thanks so much. You saved my day 🙂 It was really helpfull.
Thanks, great tip for the Calculation Options.
You would think Microsoft would put a big yellow warning on the toolbar to indicate manual calc mode, so it stands out. Click it to get dropdown for auto calc, not hidden away in the ribbon somewhere.
thank you so much, the first one helped me 🙂
I NEED HELP, PLEASE!!! My excel formulas SUDDENLY went gaga and I have been sweating in the last 6 hours ever after!
When you highlight a column to SUM, instead of giving result in the cell immediately after the last number to be summed, IT GIVES ME FORMULA ON THE CELL OF THE LAST NUMBER TO BE SUMMED. If I re-do and run through the routine again, IT DOES SAME THING BUT THIS TIME, IT GIVES FORMULA ON THE FIRST CELL OF THE COLUMN TO BE SUMMED and on and on it keeps rotating. I have tried everything I read here BUT TO NO AVAIL! PLEASE HELP! We can Skype if need be, here’s my Skype ID: schrodingerr. Thanks
A solution to this problem could be to turn off the formula-display option. The simple keyboard shortcut to toggle formula-display on and off is Ctrl + ` (control + accent mark by the 1 key).
Options for adding a range of data together:
1. SUM formula
When using the SUM formula you must insert your formula into a blank cell then select the range of cells you would like to add together.
2. AutoSum button
The AutoSum button is found on the home tab, but this only works if you select an empty cell below the data you wish to sum.
3. Keyboard shortcut
The keyboard shortcut to SUM is Alt + = , but this only works if you highlight the range of data you wish to sum.
NOTE: None of these options work if the entire column is highlighted.
LIFESAVER! Thank you this is brilliant!
yay!Thank you so much! I almost had an anxiety attack! lol =)
Thanks a lot for the help
This was driving me crazy. THANKS!!!
Thank you! This was very helpful
1st option helped me. Thank you for your support
I have tried this, and am very new at Excel…at first I was getting a number but now the formula is the only thing showing. I am trying to calculate multiple rows by multiplying two columns =C2*D2
When I copy it in the other rows, the formula changes for each one but won’t give me a numeric answer…what am I doing wrong?
It sounds like you may have tripped the “Show Formulas” toggle.
This toggle allows all the cells to show either the formula in that cell or the result.
Its natural state is: Result Displayed
But you could, accidentally trigger it to: Formula Displayed
In Excel 2010 On the FORMULAS tab, in the “Formula Auditing” group there is a button for “Show Formulas”.
The keyboard shortcut is (Cntrl+`) which is usually how it is accidentally triggered.
When the toggle is off, you will see the results of your formulas, in each cell.
When the toggle is on, you will see the formulas, in each cell.
The cells can only be in one state or the other.
Very helpful, it was the circular reference!
Excellent, thank you!! I thought my file had gotten corrupted. I just wonder if adding a lot of conditional formatting somehow trips the Auto-calc to manual. I don’t know how my setting got changed.
Thanks…the automatic was unchecked…not sure how but thanks
have a cell that still will not sum all of the selected cells, the last cell is included in the formula but the amount is not calculating in the sum cell, i have check the cell format of the sum cell and the cells involved in the sum calculation, i have deleted and clear cell contents an started over in the sum cell and last cell, i have copied another like formula over to cell giving me the problem, still no luck, i have tried all of the above,no luck, thoughts????
Make sure you aren’t including your “SUM()” cell in your formula. Ex: if your SUM() is in cell A11 your formula should read =SUM(A1:A10) not (A1:A11).
Thanks a lot 🙂
Thank you!!!! I really appreciate the tip provided. Calculate Options!!! I never would have guessed!
Not working! I have an existing spread sheet and the reference to a tab to transfer data is the same as the line above except for the correct cells and all it will show is the formula not the transferred data? Have retyped and copied??
The “show formulas in cells” option could have been tripped. There are two ways to fix this problem.
The keyboard shortcut allows you to toggle this options on and off and is typically how this feature is tripped.
By placing a check mark in the box to the left of this option you will turn the formula display on and by removing the check mark you will turn the formula display off
thank uuu it worked …
Calculation to Automatic is very useful
Thanks a Ton ! Really appreciate it ! Had gone mad resolving this issue !
Thanks a lot 🙂
my problem is solved.
Thanks a lot.
Calculation to Automatic is very useful.
Thanks a ton! 1st option is the deal. Much appreciated.
Have been breaking my head over this every month for the last 3 months and recalculating every time I amended the values. Had gone crazy trying to figure this out. Thanks a lot! Really appreciated.
Thank you!Really helped as I was confused
Thank you from the bottom of my heart! For 7 years I’ve been using a spreadsheet with various tabs that all use a simple sum column, and yesterday they all stopped working. In desperation I started searching the net, and you have saved the day with Fix #1.
The question now is HOW it somehow got set to Manual calculation instead of Automatic. It’s a mystery… It’s not something that would really happen accidentally; you have to go hunting for it!
Excelent! thank you very much
I was frustrated with my spreadsheet that when I created it, it was calculating fine. later when I changed some values the formulas were not working.
Thank you for the explanation, I changed the Calculation option to Automatic and problem solved!
Also, make sure Formulas/Show Formulas isn’t selected. You can toggle between the formula and the result in the cell.
I inserted a macro that should deliver a result greater than 0, but the number that appears in the cell is 0. When i insert the formula manually it gives me the correct result.
Why would it be?, thank you for the answer.
If you’re getting a correct answer from a formula, you can be sure that you have the right calculation in mind and the right arguments for it. For this kind of problem, that’s an excellent start.
If the Macro, itself, is not correctly mimicking the formula, there are several possibilities to explain a problem:
Since you are getting a correct result with formula, this may not be worth the effort to mimic as a Macro.
However if that’s the need, launch the Marco with a debugger breakpoint at the start of the code (F9) and walk through each statement to determine the error.
Removed all circular references and now the formulas are working…thank you. An hour or less of work vs. recreating the lengthy spreadsheet made my day!
Thank you very much.. Helpuful in need.
I would have saved myself 2 hours of frustrating aggravation if I had just looked this up sooner. Thanks for the info.
thank you so much! the first answer solved my problem in less time than needed to comment here:-)
I also do not know why ‘automatic recalculation’ got turned off, but that was it.
Gotcha! Circular Reference is hiding… Hahaha! Thanks man.
I had to say Thank You. This problem was driving me out of my mind. I just received a new work computer and couldn’t figure this out.
Can anyone explain why the VLOOKUP function does not work in following statement
Here are the needed arguments for the nested functions:
SUBSTITUTE( text, old_text, new_text, [nth_appearance] )
VLOOKUP( value, table, index_number, [approximate_match] )
In the nested functions below:
The internal “=” sign, just before VLOOKUP, is unnecessary.
If “xx” is a named range and it has at least seven columns, and the “=” is removed, it should work fine.
thanks te second one helped me
This was driving me NUTS! thank you very much.
Thank you a lot!!! I fixed the problem
Thanks! Circular Reference point help resolve the situation for me.
Thank you, i was stuck
I had no clue there was an option to ‘not’ automatically calculate formulas! You learn something new every day! Thank you!
Thank you!! My issue was resolved with solution #1.
#1 was my problem. Not sure how it changed to manual calculation but this was a life saver!
Thank you so much. It’s helpful tips
Thanks, tips no 1 has solved my problem.
Thank You Very Much
I forgot to sort my lookup data. That was the reason for my N/As.
I had a formula not calculating correctly. It was counting instances of a certain set of letters, a Countif formula where the set of letters it was looking for was in different cells. I was updating the data set and knew my formulas already worked. It wouldn’t after updating the data. After a bit of investigating, it turns out my source for the new data added a space after each set of letters when I imported it to Excel. Needed to add the same space to the references in my spreadsheet. Extra spaces in cells can really mess with formula calculation.
Yes, COUNTIF() can be extremely picky about “TURTLE” being different from “ TURTLE” or “TURTLE “.
But it’s also flexible about case (upper or lower case don’t affect the outcome)
You may wish to consider applying a FIND/REPLACE for spaces on the data range the COUNTIF() is searching.
Thank you! Automatic-that was the answer!
Wow! Thank you so much! Circular References, never knew that before 🙂
thanks a lot
Thanks that was a quick fix. Several of my Sales team were having to adjust formulas manually. Seems when we switched to a new server settings defaulted to manual.
Thank you for the help!
Thanks for the help!! Quick and easy fix that would have taken me hours to figure out without your website.
Thanks so much for you clear information for changing my automatic autosum calculations. I use this all the time.
I thought there was something wrong with my newish hp laptop and I would have to take it to my repair guys. Time and $$.
Awesome!!! I would have never figured that out! It was #1.
Thank you so much.
Thank you so much..
Thank you so much!
Thanks. It was simple but yet helpful.