?
Solved

EXCEL shows formula itself instead its result.

Posted on 2013-06-13
12
Medium Priority
?
498 Views
Last Modified: 2013-07-11
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.
0
Comment
Question by:MaplesITHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39243771
Is there a space or single quote before the equal sign?

Kevin
0
 
LVL 81

Expert Comment

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

Kevin
0
 
LVL 50
ID: 39243797
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:MaplesITHK
ID: 39243819
Hi Zorvek,

Try yours one but no luck.

Hi Teylyn,

Will try yours one.

Thanks all!
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39243868
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
 
LVL 50
ID: 39243931
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
 

Author Comment

by:MaplesITHK
ID: 39243953
Checked and such options wasn't been enabled by default. :(
0
 
LVL 50
ID: 39243962
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
 

Author Comment

by:MaplesITHK
ID: 39243986
will try bcos it contains some p&c info
0
 
LVL 10

Accepted Solution

by:
Gašper Kamenšek earned 720 total points
ID: 39244178
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
 
LVL 50
ID: 39244444
@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
 
LVL 50
ID: 39317276
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question