7

I am copying and pasting a formula in excel and instead of showing the value it is just displaying the formula. (the formula is ='G:\Corporate[NewOrder.xls]Sheet1'!Q21 ). Is there some setting that I need to change? I have tried the f9 key but it does not recalculate. It seems like sometimes when I past the formula it will work until I place the cursor within the formula and then it is broken again.

19

Edit your formula and be sure there isn't a ' before the = sign.

The cell may have been formatted as text before the formula was entered. Select the cell, then right-click and select Format Cells. Format the cell as General (on the Number tab), then click OK. Press F2, then Enter.

Another possibility is that you have accidentally toggled the view to display formulas rather than their results. Pressing Ctrl + ` (backtick, found next to the left of the number 1 on US keyboards) to toggle the view back to normal

  • Or a space or anything else – Josh Hunt Nov 11 '09 at 23:39
  • +1 Each of these 3 might be what's causing it. Good answer – DaveParillo Nov 11 '09 at 23:59
  • 1
    Thanks for mentioning CTRL+` I hadn't used that before... but I'm sure I will now I know about it! – Richard J Foster Aug 26 '10 at 15:51
  • Also check out this MS support article describing a bug in cell linking: support.microsoft.com/kb/135506 – Mac Apr 6 '11 at 1:08
  • When I try to use your suggesting with pressing CRTL + ` ... nothing happens Excel 2016. – user3386170 Jan 23 '18 at 18:34
0

I just had this issue pop up, but only for a specific section of cells. I tried every trick in every book I could find online. End result that fixed the issue was this:

  • Highlight the affected cell.
  • On the function line near the top, select in front of your = sign starting your formula.
  • Hit the backspace key like you are erasing a space.

I know none of my formulas had a leading space, but for some reason, this fixed the display issue, even though when I hit the backspace key, nothing appeared to erase, shift or move.

I don't know why (Excel 2010) did this; there is no logical reason this happened in the first place, and certainly no reason this should have fixed it, but it did.

0

I had the same issue on only some cells. The formula referred to another sheet that had a number. The solution for me was to format the destination cell as a number. In my case I needed to format for no decimal places. Hope this helps.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.