Solved

EXCEL shows formula itself instead its result.

Posted on 2013-06-13
12
457 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
  • 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

Expert Comment

by:teylyn
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
 

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

Expert Comment

by:teylyn
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:MaplesITHK
ID: 39243953
Checked and such options wasn't been enabled by default. :(
0
 
LVL 50

Expert Comment

by:teylyn
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:
GasperK earned 240 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

Expert Comment

by:teylyn
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

Expert Comment

by:teylyn
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now