Home  »  Excel   »   Top 5 reasons why cell displays formula instead of results in Excel

Top 5 reasons why cell displays formula instead of results in Excel

By | January 5, 2018

Below are the top 5 most common reasons why formula text is shown in cell instead of its results. They are sorted by frequency with most popular at the top.
They apply to Excel 2013 and Excel 2016.

No equal sign before the formula

Make sure that there is an equal sign before the formula.

No equal sign before the formula in Excel

Correct formula in Excel

Formula is inside quotes or apostrophes

Check that you typed formula with no quotes or apostrophes at beginning or end.

Formula has apostrophes in Excel

Formula is inside quotes in Excel

Correct formula in Excel



Worksheet is set to display formula – not results

Hold down theCtrl and push `

The ` symbol key is on the left side of the 1 key.

or
Click on the Formulas Tab then check if “Show Formulas” button isn’t on.

Excel 2016 - show formulas - small screen

Excel 2016 small window – Click to enlarge

Excel 2016 - show formulas - full screen

Excel 2016 full screen window – Click to enlarge

Cell is formatted as text

Sometimes showing formula as text might be due to cell format. Change it to general to see if it helps.

I found that mine was formatted for text. I changed that, but nothing happened. I clicked into the formula, then hit enter and that is when the value finally showed up.
Comment from our reader Suzanne – thank you!
  1. Right click on the cell with formula

  2. Choose Format Cells

  3. Excel 2016 format cell 1

    Click to enlarge

  4. Select Category -> General

  5. Excel 2016 format cell 2

    Click to enlarge

  6. Click OK button



Cell with formula is locked

Found that the cell I had the formula in was “locked” (Format Cells / Protection) – it would seem that this also prevents the value from showing.
De-selecting “Locked” the value appeared immediately.

(tip from our reader Clare – thank you!)

Rating: 5.0. From 1 vote. Show votes.
Please wait...

30 thoughts on “Top 5 reasons why cell displays formula instead of results in Excel

  1. Anonymous

    This is the only article that helped me. THANKS

    Rating: 5.0. From 1 vote. Show votes.
    Please wait...
    Reply
  2. Nick

    I have a similar problem, i am importing text from one cell to another using ‘=AO’ in the cell. Worked yesterday on one worksheet and works on only a couple of cells on this one, no indication why? any ideas?

    No votes yet.
    Please wait...
    Reply
    1. Sebastian Expert Post author

      have you checked all possible reasons from the post?

      No votes yet.
      Please wait...
      Reply
  3. Anonymous

    Try putting TRIM() around your formula

    No votes yet.
    Please wait...
    Reply
    1. Jim

      Thanks for the Trim work around. I worked after everything else failed. πŸ™‚

      No votes yet.
      Please wait...
      Reply
      1. Anonymous

        TRIM finally helped; thanks!

        No votes yet.
        Please wait...
        Reply
      2. Anonymous

        This worked for me as well !! Thank you

        No votes yet.
        Please wait...
        Reply
  4. Alan

    I struggled with this often and went through all of the above many times with no luck. I often just type in my functions and finally noticed that if I used lower case for the first function name it would not display correctly. When I started making sure they were upper case the problem was corrected.

    No votes yet.
    Please wait...
    Reply
    1. Sebastian Expert Post author

      thanks for the info!

      No votes yet.
      Please wait...
      Reply
  5. v

    Thanks it solved my problem… πŸ™‚

    No votes yet.
    Please wait...
    Reply
  6. avantha

    Thanks,
    Clearly explained..
    it helped me

    No votes yet.
    Please wait...
    Reply
  7. CT

    Hi – Great tips.

    I tried all of them but it still didn’t work.

    Found that the cell I had the formula in was “locked” (Format Cells / Protection) – it would seem that this also prevents the value from showing.

    De-selecting “Locked” the value appeared immediately.

    Clare

    No votes yet.
    Please wait...
    Reply
    1. Sebastian Expert Post author

      thank you for the information, will add it to the article!

      No votes yet.
      Please wait...
      Reply
  8. Suzanne

    I found that mine was formatted for text. I changed that, but nothing happened.
    I clicked into the formula, then hit enter and that is when the value finally showed up.

    No votes yet.
    Please wait...
    Reply
    1. Sebastian Expert Post author

      thank you for sharing this information. I have updated the article and included tips from you and Clare.
      Hope this will help other people!

      No votes yet.
      Please wait...
      Reply
    2. Anonymous

      This worked for me! Thanks.

      No votes yet.
      Please wait...
      Reply
  9. Rams

    Thanks for the info provided which was not even provided by Microsoft support!

    No votes yet.
    Please wait...
    Reply
  10. jitendra

    If excel shows formula instead of value then while pressing ” F2 ” tab hit enter.

    No votes yet.
    Please wait...
    Reply
  11. Deibitto

    Tried all these things and it still didn’t work.

    Fudged around quite a bit and found that if you imported (copied) a tab (we’ll call this tab A) from another excel spreadsheet, then, called (used “=” sign) from a cell on tab A, it would display as the formula even if you did all the steps listed above.

    The solution I found was to just copy and paste (as values) all the information on the tab A into a brand new tab (We’ll call this tab B) on my own excel spreadsheet. Then, call from the values in tab B instead of tab A. This fixed the issue. Wonder what the issue is, since the Excel version of tab A and my excel spreadsheet are exactly the same?

    Didn’t find this solution anywhere while searching online so, I hope this helps somebody else that is having the same problem as I had.

    No votes yet.
    Please wait...
    Reply
    1. Sebastian Expert Post author

      thank you for sharing!

      No votes yet.
      Please wait...
      Reply
    2. JΓ­ra

      I had same problem and simple solution was to find all “=” and replace them with “=”. πŸ™‚
      EXCEL recalculated all cells and I got desired result.

      No votes yet.
      Please wait...
      Reply
  12. Anonymous

    Perfect, I knew I hit something but I couldn’t remember what I did. Ctrl+` does it every time. πŸ™‚

    No votes yet.
    Please wait...
    Reply
  13. Verl Humpherys

    This was very helpful. Thank you.

    No votes yet.
    Please wait...
    Reply
  14. AZB

    I found that copying the formula, pressing “CLear All” and repasting the formula worked.

    No votes yet.
    Please wait...
    Reply
  15. Nicola

    The only thing that worked for me was F2 hold and press enter, after exhausting all other possibilities

    No votes yet.
    Please wait...
    Reply
    1. WuzzoUK

      Agreed – this is the only thing that worked for me

      No votes yet.
      Please wait...
      Reply
  16. Basem Khattab

    Hi,
    Thanks a lot for the article.
    as I found that doing this will help

    1- delet the cell content
    2- Change the cell type to General
    3- Rewrite the formula

    Enter it shall work if other solutions didn’t

    No votes yet.
    Please wait...
    Reply
  17. Chris Bouchard

    Hi, thank you for your post. I found my problem quickly returned to my work.

    No votes yet.
    Please wait...
    Reply
  18. Donna Bushey

    This is such a random, critical and frustrating issue that it seems Microsoft should have supported by now! Thank you to everyone who shared solutions! The solution that worked for my system was posted by Jira Oct 2017 explaining to find all β€œ=” and replace them with β€œ=”. I literally tried every tip previous and they failed for me.

    No votes yet.
    Please wait...
    Reply
  19. saral seth

    hey thanks for the solution

    No votes yet.
    Please wait...
    Reply

Leave a Reply

Your email address will not be published.