Excel Formulas are not working?!? What to do when all you see is the formula, not result

Posted on April 12th, 2010 in Excel Howtos - 265 comments

Once in a while everyone is bound to come across this problem. You type a formula in a cell, then you press ENTER. Bam! nothing happens. You check if a donut chunk went in to the key board and some how jammed the ENTER key. So press it again, this time harder. But nothing. The formula shows up in the cell instead of the result, like this:

Excel Formulas are not working?!? What to do when all you see is the formula, not result

Now what to do?

Of course, you can be careful when eating donuts. But careful donuts sure sounds like a paradox. So instead lets roll up our sleeves and find out the reason for this mishap.

The top reason for formulas are shown as formulas instead of results:

You may have accidentally pressed CTRL+` (back quote symbol, the key below escape key in your keyboard) or activated the “Show Formulas” mode in Excel.

When you do it, excel shows the formulas instead of their results.

To fix this error and get back the values (or results) just press CTRL+` again.

The next reason why formulas are shown as formulas:

You may have set the cell formatting to “Text” and then typed the formula in it.

When you set the cell formatting to “Text”, Excel treats the formula as text and shows it instead of evaluating it.

To fix this error, just select the cell, set its formatting to “General”. Now edit the formula and press enter. (Alternatively you can press F2 and then Enter after setting format to General).

The less probable reason why formulas are shown as formulas instead of values:

You may have accidentally typed a single quote ‘ before the = sign in the formula.

When you type single quote ‘ in a cell excel treats the cell contents as text and does not evaluate any formulas within.

To fix this error, just remove the single quote.

What is your experience with excel formula errors?

The very first time I pressed CTRL+` by accident, it nearly freaked me out. All the columns became too wide and the formatting went for a toss. Everything looked  weird. It took me a while to figure out that I accidentally pressed the Show Formulas shortcut (CTRL+`). I felt huge relief when I got the results back.

What about you? Did the formula error ever freaked you out? What other things about formulas worry you? Pls. share using comments.

More on Formula Debugging:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

265 Responses to “Excel Formulas are not working?!? What to do when all you see is the formula, not result”

  1. mikii says:

    sometimes i get strange “not working formula” error – “show formulas” is turned off, there is no single quote before formula and the cell is formated as number. but it still shows formula instead of result. it happened some time ago on xl2003, i haven’t run into this problem on xl2007. bet only thing that helped in that situation was deliting the cell and retyping formula somewhere else (or copy formula only as full copy reporuduced the same error also in the new cell)

    • Jimmy says:

      Formula Values did not get updated in my excel sheet. Found out in formula tab in calculations there is an option to set calculate value manually or automatic.
       

    • Steve says:

      Excellent thanks.  I had the cell preset as text.  Setting it to general didn’t sort it at first, so re-entered the formula and problem solved.  Thanks again.

      • Andrew says:

        I had the same thing. It was a lengthy formula so I copied it to a notepad page, changed the cell to “general” and then pasted it back in so I didn’t have to retype the whole thing.

        • Bernadette says:

          Same here, thanks for the additional info – I had tried everything else, but with no change!

          • Valeria says:

            If any of the above is not working

            1) Make sure there are no circular references which are displayed at the bottom of the spreadsheet. This does not always work though.
            2) Select all tabs in your workbook, go Home–>Find and Select–>Replace with (or hit Ctrl+H) and type “=” in both fields, hit Replace all.

            Step 2 SAVED us a lot of time. Not sure why it works but it does work.
            This was our last resort

    • Kortnie says:

      I am only having issues with one spreadsheet. after copying and pasting a chart into another sheet and eliminating past month’s data. Formulas stopped working. I was only getting the formula in the space after hitting enter. I followed the directions here and got everything set to “general”, redid my formula, hit enter. and now it always says 0 instead. I went into a new spreadsheet and tried out an example of what I have been trying to do and the equation worked perfectly. So I am not entering the formula incorrectly. What else could it be?

    • Elizabeth says:

      thank you

  2. When the problem is with cells formatted as text, I do a find and replace “=” with “=” (without the quotes).

    mikii, I think your problem might have been related to an add-in.

    • Rahul Goswami says:

      replacing = with = actually worked out for me

      ~Thanks Sebastien.

    • Johan says:

      Best tip ever – been sitting with this problem a number of times, and none of the more “rational” solutions worked.

      Now I am in a happy mood which will last for at least a day!

    • Ahmad says:

      I tried the other suggestions. Replacing = with = helped. I feel relieved! Thanks.

    • Greg says:

      WOW! Who’d have thunk it! Replacing = with = WORKED!
      Now, if we could figure out why!?! an entire spreadsheet was this way after importing data from CRM…
      Thanks for sharing, made my day!

      • Hui... says:

        @Greg
        The default character set in Windows has 4 characters that have twin parallel horizontal lines that look like = signs
        Only one of these is the = sign
        The others will simply be treated as text

        Simply select one of the bad ones
        Then do a Find & Replace for a normal = sign

        We get the same problem in the WordPress Posts here at Chandoo.org where from time to time the ” characters are replaced by a similar looking set of characters rendering the formula useless

    • Terry says:

      The global find & replace (Ctrl+ H) of = with = is truly BRILLIANT!!!
      Thanks to Sebastian and Valeria for this!
      the problem happens to me when I concatenate multiple source cells into long formulas to build complicated lookup-up tables and other worksheets where it’s much easier to build dozens of complex formulas as text strings and then copy their values into the working sheet; doing the global search & replace of = for = [char(61)] “activates” all the formulas, which otherwise sit there in perfect syntax but recognized only as ‘constants’ by Excel. My old method was to “F2″ & ENTER in every cell, one at a time; Ugh!
      Thanks, again!!

    • Hans Poulsen says:

      Amazing. I was going absolutely NUTS and then – replacing = with = does the trick. Thanks!!!!! :)

  3. Kevin says:

    Change the number format and don’t panic.

  4. mikii says:

    tnx, Sebastien. It could explain such error. If it’ll happen again, I’ll check what add-ins are running ;)

  5. John Franco says:

    I have experienced the following weird behavior with SUM function…

    Imagine you have the following values on the column A
    1
    1
    And you use the formula =SUM(A1:A2)
    And the result you get is 1 instead of 2.
    Then you use the following formula: =A1+A2 and you get 2

    I figured out that one of the cells contained the number stored as text.

    I also figured out that the SUM function doesn’t convert such numbers to text as the plain formula do.

    Try it by yourself.

    I wrote an article that summarizes the problems of the Excel Sum formula, it may help you to avoid those errors and more in the future.

    http://www.excel-spreadsheet-authors.com/excel-sum-problems.html/

  6. Mel says:

    This happens to me when I’ve exported data and the format gets messed up. It thinks it’s text, but just changing the cell format doesn’t fix it. I have to go to an unformatted cell and paste special format to “reset” the cell. After that, the formula will work properly.

    • Glenn says:

      I have the problem where I imported data from a tab delimited text file into Open Office Calc, and then saved the file and opened it up in Excel 2007. Some cells read as numbers and will sum up, but others don’t read as numbers and I can’t get a sum for them. The computer thinks they are text. I’ve tried copy/paste-special the format to number but that didn’t fix it. Any suggestions?

  7. Amy says:

    Thank you so much for this fix. My issue was cells formatted as text.

  8. These tips really save me a HUGE amount of time. Thanks for sharing!!

  9. Chris says:

    THANK YOU.. I was going crazy. Half my formulas were working, the other half were showing the formula instead of the value. Silly excel.

  10. Roger C says:

    A related problem, the formula total does not update when a variable is changed.

    Problem: In Excel Options, Under Formulas, “Calculation options” has been turned to “Manual”. Switch back to “Automaticl”.

  11. G Kinal says:

    I imported an old XLS into Excel 2007 and the default condition was to show the formulas.

    WHY did MS make this SO OBSCURE ?????

    GK

  12. shankar says:

    2.00 0.00 =K1685*0/100 =K1685+M1685-L1685
    this formula not working in my excell sheet model 2003

  13. TP says:

    Thank you so much!

  14. Chad says:

    I have been working on this practice assignment in Excel for an hour & just tried all the steps you suggested and it is STILL showing the formula in the cell and not the Value!!! Ready to pull my hair out…I have never used Excel & this class is a REQUIREMENT! No idea what is going on!!!!!@

  15. Hui... says:

    @Chad
    Is it just one cell or all cells?
    If it is all cells try Ctrl ~
    Delete the cell contents and reenter the formula
    Make sure that you re-type the quote characters ” if you have copied the formula off the net
    Make sure the first character is a = or a + character and not anything else including a space

  16. Jeff Hines says:

    +1
    My formulas are working now. My issue was the cell format being text instead of general.

  17. Pradeep says:

    I have a web application which allows user to donwload reports in excel 2003 format and 2007 format.
    Now when i test the applciation in my system which has office 2010, and open excel in 2003/2007 format, initially those cells which has formulas are not displayed.
    If i click the protection level alert, then they will be displayed. How to avoid this. Can any one suggest.

  18. Val says:

    Thank you! I was giving up on googling my stupid stupid excel problem (my columns had been set as text format and I didn’t realize that…and I didn’t know that formulas won’t work on text formatted cells)…until this very straight forward post caught my eye and salvaged my sanity. Seriously, THANK YOU.

  19. Jim says:

    I have seen this problem before. Never fixed it, always inserted a new column copied from another location within the same sheet.
    However this time I changed the format of the cell to “General” and reentered the formula. That worked.
    Previously I would change the format to a number, which has never worked for me.

  20. Dave says:

    Thanks!! Simple problem but like a needle in a haystack if you don’t know what it is. Cheers.

  21. matt says:

    What if I need it to be text? For example:
    2-6

    If I format anything other than text it tries to calculate 2 minus 6 or returns gibberish.
    I am currently adding ‘ before everything so I can keep it in general format and still have excel treat it as text. This is a tedious work around though.

  22. Hui... says:

    @Matt
    Enter it as text ie: ’2-6 or Space 2-6

  23. Charlene says:

    What a relief! Thanks so much for the help!

  24. Kelly says:

    Roger C – you are my hero thank-you!!! I’ve had 2 giant spreadies which I thought needed to be rebuilt as the formuals had totally stopped. You have saved my day. Thanks

  25. Wouter says:

    Had same problem with Excel 2007, none of the issues above. It was resolved when I manually added the ‘ (text indicator), and then removed it again. Weird.

  26. Scarlette says:

    Thank you! I thought I was going insane. Very helpful info with clear, concise instructions.

  27. Arno says:

    Hi, I’m having a problem with my excel. When I press the function button a list appears and then I select a formula. For example, if I choose the IF function it pops up and usualy every argument has its own edit box where you tipe in the argument for logical test, true and false. Currently there is only one box to tipe in everything. Is there maybe a setting that I need to check/uncheck. I hope you understand my problem

  28. Tom McLaughlin says:

    Your “fix” was right on, my friend. Thanks.

  29. Ping says:

    My problem is the formula works fine at the beginning but the cell just shows up the formula after any edits made. I’ve tried any possible ways to find the reason or solve the problem but no luck.

    Any clue or suggenstion? Appreciated.

  30. Mieka says:

    I have a similar issue, with different symptoms.

    When I add a calculation … nothing happens, it just displays as 0.

    I use this spreadsheet daily and this problem just seemed to come out of nowhere. An hour ago it was fine. What have I done?!

    I’ve checked everything: it’s set to auto-calculate, the cell is set to ‘accounting’.

    Help!

  31. Hui... says:

    @Mieka
    Do you have any messages in lower left corner of Excel Screen like
    Calculation
    or
    Circular Reference Error

    Anything else?

    • olumide says:

      Came across your thread while looking for a solution to a similar problem – summations adding up to/displaying as 0

      …yes, i do see a circular reference error in the lower left corner of the Excel screen.

      Any suggestions?

  32. Mieka says:

    Wow! Thanks very much for your quick response! I will look at that for next time.

    For now, I tried restarting my computer, and lo and behold, everything is back to normal!

    Thanks very very much for replying! :)

  33. sathish says:

    Thanks a lot . Problem Solved :)

  34. JennyD says:

    I need this to be english. 1/2 the sheet looks like this!Help!
    jpSaí¾¤°S!Õ–¡

  35. Hui... says:

    @Jenny
    It looks like you’ve impoorted a Word or other binary document into Excel or vise-versa.
    Excel can only import Excel and a small list of other files.
    Make sure you select the correct file type.
    Also Excel 97-03 cannot import Excel 2007/10 files without a convertor, available from Microsoft.com

  36. yang says:

    Thank you for posting this seemingly “basic” excel problem! This problem drove me mad!

  37. dan says:

    If the formula is an equation (=) to another cell (for example to repeat a value in multiple work sheets) and that this source cell is a merge cell, it will display unpredictable results depending on the cell format. These include displaying the formula instead, or a zero, or a #VALUE!.

    To fix this, either un-merge the source cell so your formula contains a single cell reference not a range, or simply Define a Name for the source cell and use this Name in the formula rather than the range.

  38. Benoy says:

    @Chandoo: I’m using an IF formula =ROUND(IF(AG3=0,0,((AI3/AF3)*100)),2).
    Cell AG3 has =IF(ISERROR(Query_Actual!K3/’No of proposals_Actual’!K3),”0″,Query_Actual!K3/’No of proposals_Actual’!K3) formula in it.
    However, even if AG3 is 0, excel reads it as ‘FALSE’ and does the division!

    One more thing. The same formula works for other cells with only a 0 in it. But when the above said formula (in AG3) is referred it does not work.

    I’m sure this is some excel error because sometime back I had used the same formula and it used to work perfectly. It was in Excel 2007 and now I’m using 2010.

    Please help me with this.
    Thanks much.

  39. Hui... says:

    @Benoy
    In AG3 change ”0″ to 0
    .
    of course in 2010 you could just use the following in G3:
    =IFERROR(Query_Actual!K3/No_of_proposals_Actual!K3,0)

  40. Benoy says:

    @Hui: Thanks a bunch. I should have realized “0″ is a text and it should be 0 only..!
    The new IFERROR is too good. Otherwise the formula’s ran pages!!
    Thanks again.

  41. Carolyn says:

    I am using a large spread sheet converted from an older excel program. I am calculating in 3 columns. Sometimes one of the two columns will have the same calculation in it and I noticed that the answer is different. I checked the cell formulas and they are the same. I have tried to cut and paste and it won’t over ride what is in the cell and still calculates incorrectly. I was just upgraded to 2010 so am struggleing with the new options. What could this be? I checked and “set percision as displayed is NOT checked”

  42. Hui... says:

    @Carolyn
    Is calculation set to Automatic not Manual?

  43. Scott says:

    Thanks! The General / Text thing worked for me!!!

  44. Mike says:

    Maybe someone can help. I have the below formula

    =IF(G4=A3,B3,IF(G4=A4,B4,IF(G4=A5,B5,IF(G4=A6,B6,IF(G4=A7,B7,IF(G4=A8,B8))))))

    However, I also have a formula in cell in G4 which reads I4/F4. My result is showing False. If I just type in the result, and not have the I4/F4 formula it does the calculation. Hope I explained that well. I am confusing my self. Thanks for your help.

  45. peter says:

    Mike,
    Your inner most IF(G4=A8,B8) has only 2 arguments. If this is the intended purpose, then sorry I don’t know. Otherwise, its possibly because when G4 doesn’t equal A8, it returns FALSE. I guess it does the calculation (=A8) when you type in the result because the value type typed in G4 equals A8?

  46. Ajit says:

    How the problem solved.
    Problem: When I put the formula A1+1=2 ( If A1=1), But does not responding.It shows A1+1=1 instead of 2.

    Solution :
    Go to Formulas Where shows a calculate option and click on automatic as a first option.

    Regards
    Ajit Kumar

  47. FAraz says:

    Right Click> Paste Special > Values

  48. Hui... says:

    @Ajit
    Your formula should be
    =A1+1
    make sure A1 has 1 not ’1 or space 1

  49. Okkitrooi says:

    Just to add to the pile, I have a list of codes, could be numeric, could be text.
    I want to do a lookup on them and use the TEXT(A1,0) function to make them all text.
    Most rows show me the correct value, some show me values that are totally unrelated.
    i.e.:
    A1 = ’5-1800
    A2 = ’5-1900
    Text(A1,0), gives me “5-1800″
    Text(A2,0), gives me “122″

    What am I doing wrong?

  50. Hui... says:

    @Okkitrooi
    .
    Excel is interpreting these as dates
    ie: ’5-1900 is Tue 1 May 1900
    it isn’t doing that to ’5-1800 as Excel dates start at 1 Jan 1900
    Dates entered as text prior to 1/1/1900 aren’t interpreted as dates
    .
    In regards to doing a lookup you can use a Match() function to lookup Text and so should be able to work around this.

  51. Okkitrooi says:

    And all of a sudden the world was in balance again!!!
    Thank you sooooo much this would have taken me ages to figure out!

  52. Doug says:

    Thank you so much for this fix! I was getting really frustrated that my formulas we showing up instead of the value (obviously I couldn’t figure out why it worked in some cells an not others)!

    You are a life saver!

  53. Terisanna says:

    Thank you so much, your site is now bookmarked. I spent three days and believe me I Wasn’t Looking Forward to telling my boss that I couldn’t get my spreadsheet to work properly. You saved my butt. THANK YOU! THANK YOU!

  54. jay says:

    this is probably a stupid question, but i’m new to working with excel 2010.

    what if i WANT the spreadsheet to show ONLY the formulas and not the answers? how do i do that and save it, and then send it to someone? i tried a couple of times, and every time i open the sheet back up, it doesn’t show the formulas, only the answers. i only want the formulas to show.

    thanks.

  55. Hui... says:

    @Jay
    I use Excel 2010 and when I show Formulas using Ctrl ~ and save a file
    it opens with formulas showing as formulas
    .
    Can you instruct them to use Ctrl ~ after they open it to toggle between formulas and values

  56. ted says:

    In Excel 2007 my formulas are suddenly copying as values, not as appropriately offset-adjusted formulas. In other words, copying is now behaving just as if I if I had done the corresponding “Paste Special” and selected “Values”, but I am using simple “Copy” + “Paste” (or, with the same wrong results, CTRL+c and CTRL+v).

    This failure now occurs in entirely new spreadsheets (new workbooks, not just new worksheets), and not just in the worksheets where formula pasting was previously working normally.

    My next move is to OOCalc and/or to other machines, but I would appreciate any help. (I’m a programmer with 40+ years’ experience, and this is going to bug me [maybe that's where the term "bug" comes from?] till I see it fixed.)

    Thanks in advance.

  57. ted says:

    Supplemental re “In Excel 2007 my formulas are suddenly copying as values…”

    I just noticed that if I do try to use “Paste Special”, now all the numerous usual choices are missing and I get the entirely different window on which the only choices are:
    “HTML”
    “Unicode Text”
    “Text”
    –or– (when I just tried that again), just
    “Unicode Text”
    “Text”

    GROWL… I hope someone will point out that I’m just doing something entirely stupid (rather than discovering that my machine is now haunted by a particularly devious virus).

  58. ted says:

    Supplemental #2 re “In Excel 2007 my formulas are suddenly copying as values…”.

    Copy just started working probperly in all my Excel instances (as far as I can see, at least), AND I MADE NO CHANGES ANYWHERE IN ANY OF THOSE INSTANCES, INCLUDING NOT IN “EXCEL OPTIONS”.

    Somehow this is much more disturbing than a problem that persists consistently but is ultimately explained, even if that takes a long time.

    Maybe it was my mentioning OOCalc that did it.

  59. Hui... says:

    @Ted

    I’d be checking for
    - Viruses
    - Check the Excel Installation is sound and update if required
    - Check somebody hasn’t played a prank on you

  60. Brownies says:

    Hey guys. How do you remove quotations when a function within a function when it returns a text. Here is my formula, =OFFSET(address(2, 6, 4, 1), MATCH(I5, F3:F17, 0), 1). The ‘address(2, 6, 4, 1) returns an “F2″ in quotations and thus the whole formula will not calculate. I would really appreciate any help!

  61. Hui... says:

    Brownies
    I think the problem is that Address() returns a String not an Address
    Simply adding an Indirect to your formula, fixed it.


    =OFFSET(INDIRECT(ADDRESS(2, 6, 4, 1)), MATCH(I5, F3:F17, 0), 1)

  62. polska says:

    cells formatted as text – thanks! i was about to jump out my basement window, could’ve hurt my shoulder or something…

  63. Brownies says:

    @Hui…

    Wow, thank you so much. I literally worked on it for hours and couldn’t figure it out. Thanks again!

  64. Gaston AGON says:

    Excel 97-2003 to Excel 2010 migration problem:

    Hi all !
    I’m sure that many of you has allready experienced the following problem :
    I have developped an application in Excel 97-2003 under windows XP using workbooks with a lot of formulas linking many of the workbooks.
    I’m now trying to migrate the application, thus the workbooks from Excel 97-2003 to Excel 2010 under windows 7.
    The procedure I’m using is to load the workbooks under Excel 2010 and “save as” them using the format option Excel 97-2003. But when I open the workbooks with the biggest number of formulas and links, I get the following message box and excel terminetes when I click on the single button [Close the Program] appearing on the window:
    “Microsoft Excel has stoped running
    A problem caused the stop of the program.
    Windows will close this program an tell you if a solution is avialable”

    Do some of you have an idea how to solve my problem?
    Thanks in advance for your suggestions.
    Gaston.

  65. DH says:

    Having some issues with my formulas–you know when you hit F2 to edit and excel shows the nice colored boxes around the cells used in the formula? You can generally click and drag them to change the range used in the formula; however, my excel application quit allowing that and now only shows the colored boxes but without any nibs (i.e. a solid box instead of a box with little squares). Any help would be welcome! Using Excel 2007 and got nothing with google searching.

    • Chandoo says:

      @DH… I think you have accidentally disabled “fill handle” functionality. To re-enable it,

      1. Goto Excel Options (from office button or file menu)
      2. Goto Advanced
      3. From Editing Options, check “Enable fill handle” option.
      4. Click ok.

      Enable Fill Handle from Excel Options

  66. cowboy joe says:

    I am using a quotient formula. I would like the result to display one (1) when the answer is zero (0).

  67. DH says:

    Thank you Chandoo! That was killing me. Thank you for your response–you’re making me an even bigger fan than I was already.

  68. Hui... says:

    @Cowboy Joe
    You can apply a custom Number Format like:
    #,##0;[Red]-#,##0;(1)
    Note: that the cell still contains zero, but it displays (1)

  69. Anna says:

    Whenever I use a formula which includes a paste linked cell, I get an error message that says #VALUE. I cant seem to carry out any sort of formatting on the paste linked cells or the original file from which I copied the cells eg. changing the currency from $ to euro etc. Help please! and thanks in advance :)

  70. Hui... says:

    @Anna
    I can’t replicate this
    Does the cell which is linked to have an #VALUE error ?

  71. Anna says:

    @Hui
    The #VALUE error comes up when I use the paste linked cell in a formula.Example-I paste link the unit cost of an item from one Excel sheet to another and then multiply the paste linked cell with say..the number of items being sold in order to find the total cost. It is the cell where the total cost should have been shown that displays the #VALUE error.

  72. Hui... says:

    @Anna
    I’ve never encountered this
    Can you email me the file ?
    Email is at bottom of page after you click my name

  73. Brownies says:

    Hi again,

    Got an interesting question.

    In a nut shell. I’ve extracted data from the web into a worksheet. I had to sort the data using formulas to extract things like the “mill” out of “125 mill” among others because it would be infeasible to manually do so. Then, I used a rather large offset function to extract certain type of values from my modified data like “Total Sales”, “Yield”, etc. That works fine, but when I create a chart from the offset function values nothing comes up. Is it at all possible to extract data into a chart from two separate formulas?

    Side note: There is all kinds of different data formats like percent, dollars, and regular numbers.

    Greatly appreciate any help.

  74. Hui... says:

    @Brownies
    Often numbers copied and pasted from web sites or linked to can appear as numbers but are in fact text
    Clean up all your numbers by using Trim and Search/Replace Spaces for nothing
    Then Copy a cell with 1.0 in it and Paste Special Mulitply values to all the number ranges
    This converts all the cells to numbers
    Apply a consistent number format to the cells as well
    If that doesn’t help can you post or email your data to me

  75. LW says:

    Thank you so much!!!! I was having such a hard time with formulas showing as text. Now I know how to fix it.

  76. Brownies says:

    Wow thank you so much Hui. It amazes me how the simplest things fixes seemingly complicated problems.

  77. Ardneran says:

    Can i send my excel file to you for correction. if yes, please advise me via my email id.

  78. Hui... says:

    @Ardneran
    Click my Name, email is at bottom of page

  79. Pamela says:

    Hi There I consider myself a pretty good excel user and I have come across an issue that I can’t figure out. I am linking 2 different files (both 2007) and the workbook that I am trying to establish a link does not recognize the “linkee” worksheet as something to link to. sorry, hard to explain. I simply want to have the contents of one cell link to another cell in a different work book. However, when I add the “+” and go to link, it doesn’t recognize the cell as anything it can link to. I have tried changing all the cell format, the “allow link updates” in the security centre and have tried linking different cells and workbooks. Everything works as expected except for one source spreadsheet so that tells me its something to do with that spreadsheet. Any advice would be great.

  80. Hui... says:

    @Pamela
    Have both workbooks open before you start your formula
    When you start to construct your formula eg: =Sum(
    At this point select View, Switch Workbooks
    Select the other workbook and navigate to the Sheet & Range
    Complete your formula.

  81. JK says:

    Oh my goodness — thank you! This was driving me *insane*.

    But then…MS Office quirks often do, don’t they?

    Thanks again!

  82. AK says:

    I m not able to in use formula =A1/A2 in excel 2010 it shows #VALUE! error. i have checked cell formats they are in number formats. all other math operators(*,+,_) are working fine, problem only with / operator. an advice will be of great help. thanks in advance.

  83. Chandoo says:

    @AK… What values are there in A1 and A2?

  84. iceman says:

    i am using excel 2010, copy the formula and returning value which is fine.
    however, when i press F2 (just try to edit the formula without changing anything)
    it shows the formula instead of ‘value’

    i tried ‘ctrl’ ‘~’, i tried ‘show formula’ –> doesn’t work
    i tried replace “=” with “=”, it’s a temporary fixed, but still want to wonder why…
    anyone can help?

  85. AK says:

    @Chandoo.. any number say A1=25 and in A2=5. problem occures only with / operator others say +, – and * are working as expected.
    Regards,
    AK

  86. Hui... says:

    @AK
    Excel will happily perform maths on 2 numbers entered as text, even though it technically probably shouldn’t.
    .
    2 Questions?
    Are you using Excel or Open Office or something else?
    If Excel what version ?
    .
    Can you email me the file ?
    Email add is at bottom of page after click on Hui…

  87. catherine says:

    Hi, need help. I put in a password using the general options while saving my work sheet and after that i’ve noticed that the formulas on my worksheet is no longer working, I have a formula to compute average handling time and the sum and the average is not computing and that i need to manuall click on the function menu to compute each and is time consuming. I am using excel 2003. I am not sure if putting a password affected my work sheet or what, but I need to it to be back up and working the same way as it was. Thanks.

  88. Hui... says:

    @catherine
    The passwords should have no impact on the calculation of formula
    You may want to check if Calculation is set to Automatic or Manual
    Goto the Formula Toolbar and select the Calculation Tab, change to Automatic

  89. Hassan says:

    I am copying the formula from C17 to D17, as per excel rule it should update and it is updating the formula but showing the value of C17 instead of D17 unleass I press F2 and Enter key it is not updating the value.

    Can anybody help me out with this problem , why excel is behaving like this?

  90. sowmya r says:

    hi
    i need a if condition formula in Excel Sheet thanks

  91. KathyZ says:

    I think I have the reverse problem to the above example. Sometimes I will be typing a simple sum eg +2593/3 and although it return the correct answer 864.33 however the formula is no longer in the the cell so I can not adjust it. Can you help?

  92. ahmed says:

    Hi,

    I have a formula =if(‘a’!a1=’b’!a1;”matching”,”notmatching”) and i have dragged it over 10,000 rows….now each of the rows will show “matching” or ” not matcing” based on the comparison.

    If i have “not matching” in 8976th row….how would i know?

    Using Ctrl+f does not help.

  93. Monkfish says:

    THANK YOU! I really like you! You saved me! HUZZAH!!!!

  94. Swetha says:

    Am trying to use average function….but sumtimes it doesnt work…merging cells and trying to apply it….is it the reason?….plz sort it out

  95. Kunal says:

    I’m facing a issue- when I open a particular file I only see a #Value error (cells in this file are linked to other files which are not on my system) . However, when my peers open the file they see the calculated values (without error). I’m using 2010, my peers are using 2003/2007. If I set the calculation option to manual, I too am able to see the value correctly (however, others do not need to do this). What’s different in 2010 thats causing this? Can this be fixed?

    Appreciate any help!
    Thanks!

  96. Walter says:

    was set to Text…. never had that problem happen before…. Thanks!!!

  97. Hui... says:

    @Kunal
    Do you have the correct access to the network location where the file is stored ?
    Is it mapped correctly as say M: on their PC’s but N: on yours ?

  98. Kunal says:

    @Hui, We (me and my colleagues) do not have access to the network location of the linked files- We are an Analytics team while the file comes from Finance, there’s no way we can access those systems :). Still, I do used to see the proper values in the file earlier.
    If I open the old files now it shows an error, however, I’ve used these files earlier without any issues.
    Also tried opening the files in other ‘MSO 2010′ systems, it does show an error, while anyone using 2003/2007 doesn’t see these errors.
    Another thing, this started happening only in the last few days. All was working fine until, say, Nov end. I’ve checked the ‘trust center’ settings and everything looks normal. I wonder if any recent update to Office 2010 is causing this.

    Thanks,
    Kunal

  99. Hui... says:

    @Kunal
    I would still check to see if maybe you used to have a network access to accounts and now you don’t or it has changed from Read Only to No Access or something similar.

    There is not any backward compatibility issues like this, that I am aware of within 2007/10.

  100. Kunal says:

    @Hui, thanks for your time on this!
    I’m sure about the network access- we certainly don’t have access to it, unfortunately though! :) (I’ve worked on these reports ‘offline’ quite a few number of times)
    Backward compatibility issues- Yeah, I too doubt that- just that I’m unable to find another reasoning, so, it came to my mind.

    I would post back if I find out something!

  101. sushil says:

    I have export my excel file from google docs when i open it formullas not working
    instead of a formula it shows Sheet=Sheet1Sheet5Sheet9Sheet6Sheet7Sheet0Sheet+Sheet2Sheet5Sheet1Sheet2Sheet6Sheet

    what to do please help me
    Thanks

  102. Emily says:

    Many thanks for your website–brilliant!

  103. na says:

    Hui,

    I’ve tried all your solutions to my formula showing problem with no success.

    I’m running 2003 and it is simple columns adding the numbers in the columns.

    Any chance I could send you my sheet and you might be able to sort it. There are only 7 columns.

    Thanks and great site. I too have bookmarked it.

    Nat

  104. Hui... says:

    @Nat
    Click on my name Hui to the left
    My email is at the bottom of the page

  105. Theaty says:

    Thanks for the help! This saved me some time digging around the subpar Microsoft help pages.

  106. dxb says:

    I have a 2010 spreadsheet that has 3 nested formulas and macros. The cells return a zero value on my pc. I tried the same file (no modifications)on four other pc’s in the office and it works fine. I un-installed and re-installed excel and the problem persists. I compared most of the settings in ‘options’ with another pc and they look similar. I am really worried my reports are showing wrong figures!

  107. gary says:

    my auto sum is screwed up, when i total a column then cut and past the total cell to the next column, the sum is wrong, it is the sum from the cell i copied. Example: cell a11′s formula is =sum(a1:a10) and the total is 20. In column b, the 10 numbers total 25. If copy a11 and paste into b11, the total will show 20 (a11′s sum) even though the formula shows =sum(b1:b10). its driving me crazy. please help

    • Chandoo says:

      @Gary…

      Welcome to chandoo.org and thanks for your comments.

      Can you check if your formula calculations are on “manual” mode? If so, change them to “Automatic”. You can do this from Formula ribbon.

  108. Ayo says:

    great stuff

  109. Toby says:

    I’ve been dinged by having my cells formatted as text more than i care to admit. Thanks for the helpful post.

  110. CSM_Master says:

    Hi All,

    I was having issues with a Summary Report designed to compile individual summaries from a variety of scopes in one large project. I linked all the individual summary sheets into the Summary Report, placing each as its own table onto one sheet. Everything looked great until I tried to tally all the individual costing into an overall project total – only one of the sheets’ values totalled correctly!!

    After much frustration and trying all of the examples listed here, I discovered a simple proble – the import turned all of the “$” signs from a text formatted condition into an actual cell entry. This cancelled out the number when I tried to SUM the values. I performed a simple “Find/Replace” command with nothing entered into the “Replace” field to get rid of all these symbols and all of my calculations work as they should.

    I didn’t see this posted anywhere else on here, so thought I would add my discovery to this wealth of information.

    Regards

  111. Peter says:

    Life saver. Thanks.

  112. praveen says:

    A cell conatains date as mm/dd/yy and i need to change its format to mm/dd/yyyy. after changing the format the cell does not show the effect untill i press F2 and Enter key. i am using office 2010. can someone please help on this. i want the format change to take effect immediately.

    Thanks

  113. Nitin says:

    Start > Control Panel > Region and Language > Additional Settings > Change List Seperator Value to , instead of /. Click Apply > OK

  114. praveen says:

    @Hui

    Thanks, i have sent the mail with excel sheet

  115. Sebastien says:

    Thank you so much ! This helped me quickly ;-)

  116. Denisse says:

    Hey, just wanted to let you know this fixed my problem, never noticed the text drop down. Thanks awesome website for help.

  117. Trish says:

    Cell format was text, changed to General… Thank you very much!

  118. David says:

    Hi, i’m new new to exel and have an issue with some cells! I have a traffic light system were a formula is added to chage the colour depending on the date on the cell.The formula works great on 95% of the cells bar a few! Bit of a head scratcher ! I’ve checked the cell format and it’s all ok ,I’ve pasted the formula on the cell but it does not change colour with the formula like the rest of the cells do.
    So any help would be great.

    Many thanks

  119. John says:

    These solutions don’t work for my spreadsheet.

    changing format to general does nothing – still no calculation.
    there are no apostrophe’s or spaces in the cells.

    When I enter =2+2, I only get the text, and not 4.

    Looks like I’ll have to start from scratch….

  120. Shantanu says:

    Hi Guys,
    I need Help for excel 2010. I am importing some data from our official website. It is daily analysis sheet of our store. its shows all figures are in $ sign. by the way this data is get update in every 1 min as our store is busy. Now when i import data on my excel sheet it shows figure with $ sign ( $ 421.20). but when I want to add two cell data (=A1+A2) it shows “# value Error”. As i tried to change the format from “General” to “Currency” but as data get updated every 1 min that format get reset…. this problem just occurs in my laptop. on my office desktop (Office 2010) its work fine. on my colleague’s laptop (Office 2003) its works fine.
    please help me .

  121. Kelly says:

    Thank you, very helpful

  122. Felipe Lugo says:

    U So Awesome! thanks

  123. Nate says:

    I’m having a problem where my formula is showing a result of only 0 or 1, whereas the arguments box is showing the correct value. The formula is =SUM(–(K2:K500<J2:J500)), essentially returning a value of the number of times that the J cell is greater than the K cell. I've ensured my formatting is set to General. Any insight to this?

    • Hui... says:

      @Nate
      Your formula is close
      It should be:
      =SUM(- -(K2: K500 < J2: J500)) Ctrl Shift Enter

      Note the 2 - signs
      It is also an Array Formula so must be entered with Ctrl Shift Enter

  124. Melonie Brock says:

    I am using Excel 2010, when I first turn on my pc, the copy and paste functions to copy formulas and what not work fine. After working in it for the next few minutes the only thing it will copy is text. I have tried everything. What do I select to ensure I can still copy and paste all of the data with the formulas included?

  125. Susan says:

    Everything on the sheet is fine. The next order of business is create a new sheet with formulas. Sure ok no problem; I double-click on the column divisions to make everything fit and look pretty.
    My dilemma:
    Before turning any work, I like to look at everything in preview. When in preview mode, not only is the formula showing but the file name appears before and after the formula. Ugg what happened, I have never seen these included in the formula and cannot figure out how to reduce it to the formula alone and hence, fit on the sheet. Any suggestions?
    formula in non preview mode:
    =COUNTIFS(Table1[Bedrooms],5,Table1[Baths],”>=4″)
    formula in preview mode (of course I cannot copy it…)
    =COUNTIFS(‘Excel_LA8-31myname.xlsx’!Table1[Bedrooms],5,Table1[Baths],”>=4,’Excel_LA8-31myname.xlsx’!)

  126. Luis says:

    THANK YOU very much! :)

  127. Rob says:

    Got a doozy of excel issue: (Linking value from FileA to FileB)
    *1. Going to cell C4 in FileA and typing: =
    *2. Switching to FileB and selecting G4: (formula bar now reads =G4)
    Here’s the doozy
    upon hitting enter – the Value shows up in FileB C4
    (overwriting C4 with value from G4)

    Note: 100% single instance of excel, not 2 running.
    Never seen this before – any ideas?

  128. Kathy says:

    Thank you for this info, it is the first result from my google search.  Thanks!

  129. NPM says:

    Superb! fixed it than you

  130. prenesh says:

    I have a workbook in which one sheet importes data from msaccess queries and another sheet has formulas and lookups to the first sheet. everything works perfectly untill i go to Data tab and give refresh to the get fresh data from access. The formulas in the second sheet shows #NA and i have to manually enter f2 on each cell with formula to recalculate the results.. Any solution for this?

  131. Lande says:

    THANK YOU THANK YOU THANK YOU. I have always recreated the entire worksheet.

    This is the very best solution.

    Thank you again  

  132. Jetho says:

    I knew my problem was related to a Text vs. Number issue but still could not figure it out.
    I was trying to combine two cells with text into one using a simple =A1&B1 formula. I did not realise that also the cells the formulas was computing had to be general (they were defined as text). Once this was corrected the formula worked.
    Thanks for leading me on to the solution. 

  133. Dave says:

    Hi, might be a simple sollution but one of my users has a cell and they are trying to add up from that cell to give a total for euros so its in number format.
    The formula used is =B8+B9:B19 and when I hit enter it gives me #VALUE!

    Any ideas on this would be greatly appreciated.

    Thanks     

    • Hui... says:

      @Dave
      You can’t add a range like that
      It possibly should be: =B8+Sum(B9:B19)

      • Dave says:

        Aaahhh it makes sense,
        As I am dragging the cells with my mouse to highlite them it enters in the rest when I put in =B8+ —–>Then it puts the rest in but I was not sure that was the correct formula!
         
        Thanks Soso sooooo so much Hui!
        Our department are all very happy now and all because of you!
        Much appreciated you are a true gentleman!
        :-)))

  134. Afy says:

    Hi, I am having a similar problem re formulas entering #value! error after copying data from an HTML page and converting text to columns.

    I notice that the columns containing the numbers have “space” before and after the number and only if I manually go into each of the cells and remove this space then will it be recognised as a number.

    Also tried to do find & replace to remove the space, but wasnt recognised. Any ideas on the best way to solve this problem.

    Also, what would be the best way to convert an HTML table to Excel?

    Thanks

  135. rahul says:

    =INDEX(A2:G1080,MATCH(J2&L2&J3&L3,A2:A1080&B2:B1080&C2:C1080&D2:D1080,0),6)
    not getting the oputput because of enter command is not working
    is their a way to use { } also to execute including  any other key with enter button
     
    plz help

  136. j hastings says:

    wow.  you are amazing.  seriously.  Look in the mirror and tell yourself today.  :-)

  137. venkat says:

    I have experienced the following weird behavior with SUM function…
     
    Imagine you have the following values on the column A
    1
    1
    1
    And you use the formula =AUTOSUM(A1:A5)
    And the result you get is 1 instead of 3.
    when i change the values in between there is no change in sum value.
     

    • Chandoo says:

      First, change the formula to SUM(A1:A5), there is no such formula as AUTOSUM()
      Second ,check if the values are actual just 1 or 1 with some extra spaces in front or end
      Finally, make sure your formula calculation mode is set to automatic. You can do this from Formula ribbon

  138. Analysis was stuck! says:

    Thank you! Formatted as text – arrrrrgggghhhhh!
     
    Great tip, thanks very much.

  139. Kevin says:

    I work at a business doing payroll and my excel spreadsheet will not sum up the selected cells. I changed the result cell from text to general and then when i press enter the formula is shown not the result. When i go back and format cells again it is back on text. I don’t know if you have covered this already but i would really appreciate some help.

  140. Martin says:

    Hi: I have copied and pasted a list of stock dividends from a website. The amount of dividends column shows its format as ‘text’, which I subsequently changed to ‘General’. All I want to do is convert the column to a number – I have done this so many times before in other spreadsheets by using “Value(Cell Ref)”. No such luck this time! I have tried the myriad of solutions on your site, but to no avail. Can you think of anything else to try? Thanks a lot.

  141. Martin says:

    Sorry, but that doesn’t solve the problem. The result is ’1′ – I have tried the same idea in other cells by entering data (both as Text and as General) and then doing the ‘Paste Special’ idea, and it works fine. However, I can make no such procedures work on my data – always a #VALUE answer.  Appreciate your response, though.

  142. Robert says:

    Thanks, the fix was buried in the Ribbon of Confusion. Microsoft Excel help is never useful.  Funny that you have to google to get real microsoft software help.  This company sucks and their software (especially Office 2010) gets suckier every year.  It is sad. 

  143. Dave Helmuth (@adlib247) says:

    I love you (thanks!)

  144. Excel had me totally confused.  I searched for a pretty good while looking for this info.  Now after reading this, I’m an expert at the whole formula as text thing.
     

  145. rock says:

    it was the ‘General’ formatting problem that’s been tripping me up across spreadsheets.  thanks for the answer!  i just thought excel was bonkers.

  146. viocrystal says:

    thanks, after i push Ctrl + ~  ( all my problem is solve.. !!!!!)

  147. [...] I stumbled across a great blog post from Excel MVP, Purna Duggirala (aka Chandoo), entitled “Excel Formulas are not working?!? What to do when all you see is the formula, not result“. Today, I needed to find it again, so I thought I’d blog it this [...]

  148. Rick says:

    thank you so much! Worked like a charm (format as text was selected somehow….)   happy camper    :-)

  149. Iñigo says:

    Just great!!!, thanks a lot!!!

  150. bdgrl651 says:

    can someone please fix this formula??
     
    =IF(AND(B12>=F11+0.2*(F11)),”A”,IF(AND(B12>=F11+.10*(F11))-<F11+.20*(F11),”B”,IF(AND(b12>=f11-<(f11+.10*f11),”C”,IF(AND(b12>=(f11-.1*f11)-<f11,”D”,IF(AND(b12>=0-<(f11-.10*f11),”F”,”NO GRADE”))))))

    • bdgrl651 says:

      f11 is an average grade of 81

    • Hui... says:

      @Bdgrl651
      try:
      =IF(B12 > =F11+0.2*F11,"A",IF(AND(B12 > =F11+0.1*F11,B12 < F11+0.2*F11),"B",IF(AND(B12 > =F11,B12 < (F11+0.1*F11)),"C",IF(AND(B12 > =(F11-0.1*F11),B12 < F11),"D",IF(AND(B12 > =0,B12 < (F11-0.1*F11)),"F","NO GRADE")))))

      I think the error was in your format of the And() functions
      They require =And(Condition 1, Condition 2, Condition 3 etc)
      Where the Conditions will be something like B12>=0 or B12 < F11+0.2*(F11) etc

      You may need to retype the " characters

  151. Tony says:

    Your site is very much appreciated. I was caught in the formula/text scenario for hours until I stumbled across your solution. Everyone on all the other sites just guessed what the problem was caused by. You were the first place that actually knew.
     
     

  152. George says:

    I have a tekst (of numbers) like this “950012-435″ in A1 og want to have a copy of this in B1. 
     
    Now, it I define A1 as <text> and B1 as <General> it works ok. 
     
    If A1 is <number> a calculation is done. If B1 is <text> the B1-”text” will be “=A1″
     
    However if I make a downflow, A2 (empty) will generate a “0″ i B2. Not what I want. I want B2 to be empty if A2 is empty
     
     

  153. Teresa says:

    Thanks for posting this! it resolved my problem..
     
    thanks a lot!

  154. Sergey says:

    You’re awesome!

  155. Graham says:

    Savior !! I didnt know text cells would mess with my Fx. Thanks for the tip!!

  156. Vikas says:

    Thanks a ton for the help. You saved my time nd my hardwork !! :)

  157. Nancy Aronson says:

    Thanks; that was incredibly helpful!

  158. Steve says:

    Hello,
     
    The following formula works in cell B1 but until a selection is made from a drop down list in C9 it shows “FALSE” in B1. How can I get B1 to either be blank or show another value until a selection in c9 is made?
    =IF(C9=FA8,”A”,IF(C9=FA9,”B”,IF(C9=FA10,”C”)))
     
    Thanks

  159. Britta says:

    Thanks chandoo!

  160. Sri says:

    Thanks that worked like a magic…

  161. Phil says:

    Nice work, just the info I needed. Cheers!

  162. mary grace says:

    thanks . its only in this blog i found solution.I accidentally hit the ‘show formulas’ now it’s working.

  163. Sarah says:

    Hi ya, CAN ANYONE HELP PLEASE?!?

    I have a basic statement that isn’t working:
    =IF(G21=$M$12, “Yes”, “No”) This is in cell CI21 M12 is an absolute cell while the column G reference will need to change within each row

    G21 is populated by a VB .Value = “”
    M12 is a Validation list

    All cells are set to ‘General’ – the Show Formulas is off – and the Automatic calculation is on

    But it still doesn’t update when the value of M12 is altered from the list?

    I have tried typing long hand with more brackets, copying another IF that does update and amending the cell references, switching the order to M12=G21, creating the formula in a ‘nearer’ cell (incase it was a capacity issue) and checked that all three cells are comunicating with =SUM(G21-M12)… which does work and display the result. I’ve also tried physically selecting the cells rather than typing their references in the formula —- but nothing has worked.

    PLEASE HELP!!

  164. Kerin says:

    Thanks so much for leaving this on the web. Thankfully found this while I still had hair left!
    Signed,
    Formatted as Text not General

  165. Jovan says:

    BRAVOoooooooo!

  166. Mark Duncan says:

    Running Excel 2007 under XP.

    I just discovered that if the cell is locked (as in Format Cells > Protection), the formulas, when edited, appear as text no matter the formatting of the cell is…
    Simple answer is:
    Remove the lock, edit the formula and then lock it again. At least, it worked for me… :-)

  167. Helder says:

    Hi

    i have downloaded excel spreadsheets from the net, and now all my other excel docs do not use there formulas anymore, can anyone help me?

  168. brady says:

    Thanks… CTRL- ` is a killer! Thanks for the tip. It fixed my issue. I’ve never even heard of formula auding. I know it NOW>

  169. Joaquim Pires says:

    this post made more for world peace than any polititian ever ;)

    THAAAAAAANK YOOOOUUU!

  170. Jennifer says:

    I tried so many things, made sure the cell was not set to text, copied the formula from another cell where it worked, thought I tried everything for the last couple of hours and then found your site. There was not a ` before the = sign, just a space. I backspaced in the formula bar to remove it, and the whole thing works again, yeah!! I can’t figure out how it was adding the space, but that tiny space threw a money wrench into the formula big time. Thank you for helping me not pull my hair out (at least for today!), and for reminding me that most errors are operater errors and fixable.

  171. Patti Peets says:

    COUNTIFS(‘[Kept Appts Detail.xlsx]Page 1′!$B$5:$B$50000,”>01/01/1900″,’[Kept Appts Detail.xlsx]Page 1′!$B$5:$B$50000,”<08/30/2013")

    I want the last item "01/01/1900″,’[Kept Appts Detail.xlsx]Page 1′!$B$5:$B$50000,”<H2")

  172. Steph says:

    I am having a problem with the summation. when i do the sum formula and entered, the value that shows is “0″. What could be the problem and what should I do?

  173. Mehul says:

    Thanks………………………………….

  174. Andy says:

    Turns out (for me at least) that you have to have the cell format set to general BEFORE you start building the formula . . . I had it as text at first, and then went back and changed it to general, and got nothing helpful. It seems you have to begin from a general-formatted cell or else you never get to see the results.

    Works now though! Hooray! Time for a coffee.

  175. PaulT says:

    Thanks for this – I now understand what I need to do to correct this issue rather than trying multiple edits and cell formatting until the formula works!

    The key seems to be double checking the “general” format of cell.

    Regards, Paul.

  176. Mike says:

    One other reason is padding (not spaces) in front of the formula. Sometimes all you have to do is put your cursor right before the first character of your formula and hit backspace to remove the padding….

    I was having the same issue. After none of the steps above worked, I decided to try doing just what I wrote above and it worked.

    Thanks for at least helping me to think my formula was correct.

  177. Sarah says:

    Hi I have a spreadsheet that someone else set up, I’ve had to have it backed up as someone saved it wrong, the formulas now don’t work to change the date of births into ages in the column next to it, anyone know how I can change this so it works again?

  178. Sarah2 says:

    Sarah, input the date of birth in Cell A1. Then in Cell A2, put the following formula:

    =(TODAY()-A1)/365

    It should calculate the age…

  179. […] Source: http://chandoo.org/wp/2010/04/12/excel-formulas-are-not-working/ […]

  180. Marge E. says:

    Thank you, Chandoo! For some reason, the “General” format got changed (elves at work last night??) to “Text,” as you suggested. Now the formulas work again. Super thanx! Also — tho’ I didn’t need it this time — thanks for the Ctrl-accent grave hint — in case that ever happens to my stuff.

  181. Joe Farrah says:

    Yes.
    Thanks…
    It’s worth noting that if you change it back to text the formula still works as it should. Odd indeed. but thanks for the help!

  182. Rashad says:

    ?n microsoft excel 2007 ? have a problem
    I give formula then pressing enter, excel calculates.
    But if I change values in the cells witch I used in formulas the answer is same again.
    for ex:

    =SUMIFS(D5:D17,C5:C17,C20,B5:B17,B20)

  183. […] Excel Formulas home page [Visitors: 109,743] Delete Blank Rows in Excel [Visitors: 198,543] Excel Formulas Are Not Working [Visitors: […]

  184. diana says:

    THANK YOU! I HAVE CHANGED MY FORMULA SETTING TO AUTOMATIC NOW IT IS WORKING FINE AFTER HOURS OF FRUSTRATION:(((

  185. Petit says:

    Thank you so much, I was so shocked with this problem just now (esp with the approaching deadline), so your advice is very helpful.

  186. Betania says:

    I have an issue I could use help with… I have a master spreadsheet that I have transferred information from several other spreadsheets into using a VLOOKUP formula. If a cell was blank with no data to copy, I had the formula return “”. I then pasted the values from the lookup data in column AJ into column C. The issue is that I have a LEN formula that points to column C and if it is blank, it is supposed to point to column A. However, for any blank cells in column C it is returning 0 without moving on to A. If I hit F2 in column C, the formula works, but I have 23K rows of data and none of the other fixes I’ve seen here have worked. Any ideas on how to get them to convert without going line by line?

  187. Jacky says:

    I see all the formulas when pressing the command “Show formulas”, but when I pree Ctrl + ` nothing happens. Do you have any ideas why the shortcut isn’t working?

    • Martin says:

      Hello Jacky
      I’m running excel 2007 and when I press CTRL+’ it copies the value in the cell directly above the one I am in. To try it for yourself type your name in A1 then select A2 and press CTRL+’.

  188. Sukanta says:

    Thanks a lot buddy.

  189. lemuel mawutornam says:

    thanks very much. u’ve being very helpful

  190. hiten says:

    thanks dear

  191. Charles says:

    Thank you! This issue was driving me crazy.

  192. Anelme says:

    Thank you very much, it was a great help.

  193. Saul Espinoza says:

    You rule Chandoo!!!!
    thx

  194. stuart says:

    had a real problem with a s/sheet keep changing to manual calculation! – finally found out, whatever the first s/sheet that is opened in a session, all others follow that, regardless of their own setting! – my problem was that I was opening a s/sheet that had been sent to me, then opening one of mine – once I found out this, just reversed the order of opening – anyone else had this issue??? (Hui… any idea?)

  195. Raj Kumar says:

    Thank u ….

  196. TXTornado says:

    I cannot thank you enough for this post!! You saved me from a very aggravated coworker.

  197. Lucie Jane says:

    Thank you, you solved my problem too. Good vibes and blue skies to you mate.

  198. BettyGoat says:

    Love your website!
    I just had this problem and I:
    1) changed formatting to “General”
    2) added a space between = and cell reference i.e. =(space)CP2
    3) it worked.
    No idea how or why.
    Strangely though, if I change the formula, I have to redo these steps.
    Hmmmm.
    All the same, thanks for the clues!

  199. nagraj says:

    thank you !!

  200. Ann says:

    Praise The Lord! I thought I was going crazy!! THank you Chandoo!

  201. Angie says:

    Awesome! THANKS!!!

  202. Alexandre de Santa Roza says:

    Excellent!! My case was the 2nd one!!
    Thank you very much!

  203. Paul says:

    I am trying to sum some cells from one worksheet and show the results of this sum in another. I’ve tried all the above tips to stop Excel 2010 showing my formulas, but it won’t do it. I’m desperate!

  204. Paul says:

    Well, I’ve managed to fix it, but I don’t really know how. All I did was copy the formatting from an area nearby that was working like it should! Go figure!

  205. Greg J says:

    I use Excel Mac 2011: in a table where all the formulas are two numbers multiplied together from two other tables, I have a formatting glitch where the format of some cells (but not others) cannot be changed / format seems to be locked — I want 187.2 to display as 187, but I cannot change it from 187.2 (cannot make it currency or any other format — the format is truly locked). Yet neighboring cells behave as they should. Re-pasting the formulas has no effect.

    The sheet is not protected, although all cells show as “locked”. The cell contents are not locked, I can change freely. However, if its a number, it will only show one format / one decimal point.

  206. June says:

    I have a question. I am new to excel 2007. I volunteer at a school and I am making a new report card in Excel 2007. I have the spreadsheet done. Now I have used the formula=average(H14,AB14)to get an average. The problem is in the other cells it is giving a rounded number( I want the rounded number) but It is giving me an incorrect number due to the number in format cells function is not rounded. So my final average is not correct. How do you get excel to calculate by the rounded numbers? I hope I have explained my problem well enough.

  207. Jeandre says:

    Excel 2003 do not eval formula that have references to other plan if the column is in TEXT cathegory. Change the cathegory to GENERAL to fix the problem.

  208. Sajida says:

    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

  209. Soma says:

    Fantastic!!

Leave a Reply