EXCEL shows formula itself instead its result.

In Excel 2010

when user tries to insert the cell reference to particular cell

e.g. in cell A10 of sheet1 type =sheet2!A3  

it usually returns 0 (cell A3's format is text from Sheet2)

but it  shows the definition of the formula  "=sheet2!A3" instead of its value. I've change both cells to general but still got the same error.

Any ideas?

Cheers.
MaplesITHKAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Gašper KamenšekConnect With a Mentor Excel MVPCommented:
If I had to guess I would say it's

pic
Formulas/Show Formulas

Somewhere, someone pressed Ctrl + m by mistake...

Hope it helps...
0
 
zorvek (Kevin Jones)ConsultantCommented:
Is there a space or single quote before the equal sign?

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
When you change a cell's format from Text to General you have to re-enter the formula for it to take.

Kevin
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
To quickly re-evaluate all cells in the worksheet without having to manually edit each cell, you can do a find/replace. Open the Replace dialog with Ctrl-H, then in the Find What box enter a = sign. In the Replace With box, also enter a = sign. Hit "Replace All" and all formulas in the sheet will be recalculated (unless they are still in Text format)

cheers, teylyn
0
 
MaplesITHKAuthor Commented:
Hi Zorvek,

Try yours one but no luck.

Hi Teylyn,

Will try yours one.

Thanks all!
0
 
zorvek (Kevin Jones)ConsultantCommented:
Perhaps you have set the worksheet to display formulas instead of values. Try pressing CTRL+`

If that doesn't work, press CTRL+` again to toggle back to show values.

Kevin
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
And the long way for Kevin's keyboard shortcut tip is File > Options > Advanced > Show Formulas instead of calculated values. You want to make sure it is NOT selected.

screenshot
0
 
MaplesITHKAuthor Commented:
Checked and such options wasn't been enabled by default. :(
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Let's recap:

The cell has the formula

 =sheet2!A3  

The formula does not have any leading spaces before the = sign.
The cell is formatted with General, not Text,
The worksheet is set to show values, not formulas
You have updated the formula by editing it, or by doing a replace = with = operation.

It still shows the formula? That is most unusual.

Can you post a sample file with that behaviour?
0
 
MaplesITHKAuthor Commented:
will try bcos it contains some p&c info
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@GasperK, cool! Now we have keyboard shortcut, Excel Options and ribbon command all in one thread. All these do the same thing = change the same toggle switch. Great for people to have options.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hang on, why did you accept that answer? Zorvek suggested to check the formulas/values toggle with Ctrl-~ and I showed you a way to check it with the Excel options.

You said that this setting was NOT the cause.

How come, then, that the ribbon way of toggling that setting is the answer if the setting was not applied???

Clicking the ribbon button is just another way to change that setting. Zorvek and I gave you  two other ways.

Seems like you did not really follow through with the first two suggestions to check that setting. If anything, this should go to Zorvek, because he was the first to point to the values/formulas setting. But if you award points to any answer later than Zorvek's, it should be a split.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.