Home  »  Excel   »   Why cell displays formula instead of results in Excel?

Why cell displays formula instead of results in Excel?

By | November 12, 2015

Below are the top 4 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
image-1682

Correct formula in Excel
image-1683

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
image-1684

Formula is inside quotes in Excel
image-1685

Correct formula in Excel
image-1683

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
image-1687

Excel 2016 small window – Click to enlarge

Excel 2016 - show formulas - full screen
image-1688

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
    image-1689

    Click to enlarge

  4. Select Category -> General

  5. Excel 2016 format cell 2
    image-1690

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

13 thoughts on “Why cell displays formula instead of results in Excel?

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

    Reply
  2. 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.

    Reply
  3. 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

    Reply
  4. 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.

    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!

      Reply
  5. Rams

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

    Reply

Leave a Reply

Your email address will not be published.