Why Is Your Excel Formula Not Calculating?

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.

Fred Pryor Seminars_ Excel Formula Not Calculating 1

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.

Fred Pryor Seminars_ Excel Formula Not Calculating 4

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.

Fred Pryor Seminars_ Excel Formula Not Calculating 5

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.

Next Steps

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.

 

 

Local Seminars Related to this Topic:

Related Basic Excel Articles

Categories

Monthly Archives

101 Comments

  • Randi says:

    When will you be upgrading your course to Excel 2013?

    • Fred Pryor Seminars Customer Service says:

      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 http://www.pryor.com.

  • waqas says:

    very helpful thanks.
    its work in a jiffy.

  • Ash says:

    I was resigned to having to start from scratch on a new book, thanks!!

  • Dennis says:

    Thank you so much!

  • Peejay Postrero says:

    Thank you very Much.

  • BBlackwo says:

    The second one helped me. Thanks! 🙂

  • hanna says:

    Had to say thanks!

  • Geralyn says:

    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!!

  • Marian Desimone says:

    Thank you SO much.. I was loosing my mind trying to figure out what was happening!

  • Kent says:

    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.

  • Alex says:

    Thanks..Very helpful

  • Pat says:

    Very helpful.

  • Francois van der Bank says:

    Thank you so much. Sorted out my problem immediately. No idea how my workbook changed to manual.

  • kirk says:

    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.

  • sadia says:

    Thank you soooooooo much 🙂

  • ReddySk says:

    Great help! Thank you so much! I was already scaried that my complex spreadsheet will be necessary to re-develop again 🙂

  • Buddini says:

    Item 2 helped, but I found more detail problem descriptions and explanations at –
    http://www.excelforum.com/excel-general/562740-formula-does-not-calculate.html

    It didn’t really work until I closed the spreadsheet and re-opened it.

    Thanks.

  • Keir says:

    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.

  • Kaji says:

    Thank you so much. It’s great.

  • Joyce says:

    you had the right answer in one click! without me having to go thru 20 thousand steps!! thank you!!

  • James says:

    Thanks, Fred. You made my day.

  • Robert Phillips says:

    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.
    Thank you,
    Robert Phillips

  • shyamasundar t s says:

    thanks a lot this helped me a lot

  • Joe Excel says:

    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!

  • Steve says:

    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.

  • Sharon says:

    Another thank you for this simple answer to a problem that has been driving me nuts 🙂 you are my best friend today!!!

  • Binoey Charles says:

    Thanks very much. I was able to solve the problem which was driving me crazy.

  • TVS says:

    Thank you for making this information available in such a helpful, easy-to-digest (and free) manner. Much appreciated.

  • P says:

    Nice one my friend thank you.

  • PRiyanka says:

    Thanks so much. You saved my day 🙂 It was really helpfull.

  • Steve says:

    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.

  • Fabrice says:

    perfect. thanks

  • Pyvnd says:

    thank you so much, the first one helped me 🙂

  • Stan says:

    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

    • Excel Tips and Tricks from Pryor.com says:

      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.

  • Nicole says:

    LIFESAVER! Thank you this is brilliant!

  • Erika says:

    yay!Thank you so much! I almost had an anxiety attack! lol =)

  • vikram says:

    Thanks a lot for the help

  • John Heitmuller says:

    This was driving me crazy. THANKS!!!

  • Rose says:

    Thank you! This was very helpful

  • Ammir Ali says:

    1st option helped me. Thank you for your support

  • Shelley says:

    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?

    • Excel Tips and Tricks from Pryor.com says:

      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.

  • Kevin Garrelts says:

    Very helpful, it was the circular reference!

  • Doraine Raichart says:

    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.

  • Rema says:

    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????

    • Excel Tips and Tricks from Pryor.com says:

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

  • Safeer says:

    Thanks a lot 🙂

  • Janay says:

    Thank you!!!! I really appreciate the tip provided. Calculate Options!!! I never would have guessed!

  • Bob Kuchta says:

    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??

    • Excel Tips and Tricks from Pryor.com says:

      The “show formulas in cells” option could have been tripped. There are two ways to fix this problem.

      1. You can use the keyboard shortcut: CTRL + `
      2. The keyboard shortcut allows you to toggle this options on and off and is typically how this feature is tripped.

      3. You can go to file, options, advanced and scroll down until you find the “show formulas in cells instead of their calculated results”
      4. 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

  • sam says:

    thank uuu it worked …

  • Newman says:

    Calculation to Automatic is very useful

  • Vimal Kumar Goel says:

    Thanks a Ton ! Really appreciate it ! Had gone mad resolving this issue !

  • anshita says:

    Thanks a lot 🙂
    my problem is solved.

  • Roshith says:

    Thanks a lot.
    Calculation to Automatic is very useful.

  • Gongura Chicken says:

    Thanks a ton! 1st option is the deal. Much appreciated.

  • Srinath says:

    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.

  • Dave says:

    Thank you!Really helped as I was confused

  • Caren says:

    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!

  • warache says:

    Excelent! thank you very much

  • José M. Suazo says:

    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!

  • Howard W. says:

    Also, make sure Formulas/Show Formulas isn’t selected. You can toggle between the formula and the result in the cell.

  • Christian R. says:

    Hi,

    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.

    • Excel Tips and Tricks from Pryor.com says:

      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:

      1. The Macro calculations do not sufficiently imitate the formula.
      2. The Macro does sufficiently imitate the formula but does not copy the result to the cell in question.
      3. The Macro is completely correct but isn’t actually launching – starting. (Macro’s normally launch with a user-specified key combination like Cntrl/A)

      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.

  • Nicki says:

    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!

  • Rambabu says:

    Thank you very much.. Helpuful in need.

  • DC says:

    I would have saved myself 2 hours of frustrating aggravation if I had just looked this up sooner. Thanks for the info.

  • Claire says:

    thank you so much! the first answer solved my problem in less time than needed to comment here:-)

  • alan says:

    I also do not know why ‘automatic recalculation’ got turned off, but that was it.
    THANKS!

  • Yakoo says:

    Gotcha! Circular Reference is hiding… Hahaha! Thanks man.

  • Timothy Leavitt says:

    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.

  • Chris.West says:

    Can anyone explain why the VLOOKUP function does not work in following statement

    =SUBSTITUTE(“=VLOOKUP(671,xx,7)”,”xx”,”CA12:DP33″)

    • Excel Tips and Tricks from Pryor.com says:

      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:

      =SUBSTITUTE(=VLOOKUP(671,xx,7)”xx”,”CA12:DP33″)

      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.

  • shmuel gonen says:

    thanks te second one helped me

  • Shane says:

    This was driving me NUTS! thank you very much.

  • Roksolana says:

    Thank you a lot!!! I fixed the problem

  • Manish Khemka says:

    Thanks! Circular Reference point help resolve the situation for me.

  • Owen says:

    Thank you, i was stuck

  • Tonya says:

    I had no clue there was an option to ‘not’ automatically calculate formulas! You learn something new every day! Thank you!

  • Shannon says:

    Thank you!! My issue was resolved with solution #1.

  • Sara says:

    #1 was my problem. Not sure how it changed to manual calculation but this was a life saver!

  • Fazlan says:

    Thank you so much. It’s helpful tips

  • Khietung says:

    Thanks, tips no 1 has solved my problem.

  • JURAIJ PULKUZHIYIL says:

    Thank You Very Much

  • Nomanz says:

    I forgot to sort my lookup data. That was the reason for my N/As.

  • Michelle says:

    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.

    • Excel Tips and Tricks from Pryor.com says:

      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.

  • Monica Adriano says:

    Thank you! Automatic-that was the answer!

  • Jijie says:

    Wow! Thank you so much! Circular References, never knew that before 🙂

  • yatnesh says:

    thanks a lot

  • D Vance says:

    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.

  • ruffy says:

    Thank you for the help!

  • Rhonda says:

    Thanks for the help!! Quick and easy fix that would have taken me hours to figure out without your website.

  • Clare says:

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

    So grateful.

  • Dave says:

    Huge help.

    Thank you!

  • Rochelle says:

    Awesome!!! I would have never figured that out! It was #1.

  • Hardik says:

    Thank you so much.

Leave a Reply

Your email address will not be published. Required fields are marked *