If you enter a formula / function in Excel but only see the text of that formula and not the result of that formula, there is likely one of two thexcel-formulas-displaying-as-text-show-formulasings happening:

  1. You have FORMULAS tab > SHOW FORMULAS button turned on
  2. You have the cell(s) formatted as TEXT

The first issue is easy to deal with, just click the SHOW FORMULAS button and see if the issue goes away.

excel-cell-currently-being-evaluated-contains-a-constantThe second is only slightly more tricky.  If you have this issue and you click FORMULAS tab > EVALUATE FORMULA you may see the following notice:

The cell currently being evaluated contains a constant

 

If so, you need to:

  1. excel-formulas-displaying-as-textRight click on the cells (or entire columns) exhibiting this issue
  2. Select FORMAT CELLS
  3. Change the CATEGORY to GENERAL
  4. Delete and re-enter any formulas in those cells (no, they will not magically work after you make the change… you have to retype them)

Enjoy!

 

 


7 Comments

Vlad · September 28, 2022 at 3:49 am

Check if your lookup values, or the values from the table array you are referencing have spaces at the end of the value. Those hidden m****f****** gave me a headache until I figured them out.

Binu Balakrishnan · October 9, 2020 at 6:50 pm

Thank you, it worked!

Ben Wall · April 14, 2020 at 3:46 pm

I have re-created entire spreadsheets because of this. Excellent work figuring it out.

Dale · March 14, 2018 at 1:39 pm

After changing the format of the cells as described, it is not necessary to re-type the formulas in each cell. You can just put your cursor on each cell and press and . F2 will put you into edit mode. Enter will leave edit mode and cause Excel to re-evaluate what is in the cell. If you have a lot of cells in the same column with formulas that are being treated as text, you can do this to all of the cells relatively quickly.

Noopur · July 16, 2015 at 10:40 am

This problem had me stumped for so long until I stumbled upon your post. Thank you!

Lyzette · August 5, 2014 at 1:24 am

Hi, neither of these options solved the error…

    Ian Matthews · August 6, 2014 at 8:42 pm

    Sorry, but that is all the information I have.

Leave a Reply

Avatar placeholder

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