2

I have been facing a rather strange problem in Excel - it's not easy to explain so I have also attached a screenshot to help

ScreenShot

When I type a formula into a cell and press 'Enter', the formula executes but the answer does not appear immediately in the cell. Instead, I have to click on some other cell, or perform some other operation for that number to show up. This does not happen when the cells included in the formula are on the screen. For example, if I were to input "=A1+A2" into A3 and press 'Enter', the answer would appear. However, if I were to input "=A1+A2" into A27 (where A1 and A2 are not visible on screen) and press 'Enter', the cell will remain blank until I double click it or perform some other operation!

This has really been driving me crazy! Does anyone have any possible explanation or solution to this? For what it's worth, I'm on Excel 2016 for Mac.

8
  • There are better tools to do screenshots. It happens to me. Exiting excel and restarting it usually solve it. Sometimes there are some changes in settings (e.g. by pressing wrong key combination), but if this is the case, I left other to answer Aug 4, 2016 at 8:42
  • Hey, thanks for the reply. The issue is when I try to do a screenshot on my laptop, the number in the blank cell appears. Hence I was forced to take an image! I have tried restarting excel a few times, but to no avail.
    – NPJobs
    Aug 4, 2016 at 8:47
  • For me also the cell A27 has a strange formatting. Now I remember: once this was due to formatting of cell. Try to force it as number (Control-1). Sometime excel go crazy with a forced text format. If it works, you can copy empty cells (just below everithing) to the affected range of cells. Aug 4, 2016 at 8:57
  • Yup! When I change all the cells to 'Number' as a format, the problem goes away. Do you know how to make 'Number' as the default format for cells as compared to 'General'?
    – NPJobs
    Aug 4, 2016 at 9:11
  • No, I don't know. Usually General is good. Only few times excel messes up things (especially if files is saved using several different versions of excel). I would recommend you to create a new file, copy all content of actual file, pasting without formatting, and then applying manually formatting. I don't have a real solution, nor I understand what it is the real problem. Aug 4, 2016 at 9:27

4 Answers 4

2

I think there is a problem with cell formatting. It happens seldom (and in my experience, when working on the same file with different microsoft excel versions, or automatically generated by other microsoft tools (e.g. microsoft access)).

To solve the problem, you need to select the cells that doesn't display numbers, and select the cell format as "General" or "Numbers". Note: in general microsoft excel does a good job on interpreting the correct format of a cell, just few time it become stubborn.

See also the answer of Hannu (not the first one, unfortunatelly) in Cells show formula, not formula result.

1

I have been experiencing this same problem. It has been driving me crazy. It only started a few months ago in an ongoing spreadsheet I have, so I'm thinking it's a bug in an Office 365 update a while ago.

But reading some answers above gave me some hints. I have panes frozen so that rows 1-5 remain visible. Sometimes I work on a minimized screen when I don't have a second monitor to connect to so that I can see data to be entered on and then enter it on the excel document. Whenever I do this, and the formula was off screen, and I entered data in a cell that is ON screen, the formula simply would not update. I would have to maximize the document, and copy the formula down the column again.

I couldn't see how this would be the problem, but I tried it anyway: I unfroze all pane, then minimized the screen, and entered data in the cells visible on screen. Sure as anything, the formula - even though it is not visible on the screen - now updates! Tested this ridiculous thing a few times, and that's definitely the cause. Why? I have no idea. But it's a work-around.

Hope this is clear as mud. Give it a try.

0

7/3/2020: I was having same problem. Just figured out how to fix. In my case, I had calculated cells in rows 1 to 13 that were frozen with "freeze pane". My xls showed rows 1-13 at the top then rows 75-97 at the bottom, for example. The first 13 rows were frozen with freeze pane. When I typed F2 to edit a formula in the first 13 rows, then hit escape after not making changes, the output of this cell would go blank, even though it appeared immediately before hitting F2! To fix this, instead if hitting escape when no changes were made, I hit return instead. And problem is gone! Escape normally would be okay if "freeze pain" were removed.

2
  • I don't understand what you're saying.  You seem to be saying that the answer is to hit return (a.k.a. 'Enter').  But the question says that the problem occurs when the user presses 'Enter'. Jul 4, 2020 at 3:04
  • This is perhaps an interesting issue (I haven't tested it), but it doesn't sound like it's the same issue that OP was describing. If you have this problem, perhaps post your own question (and answer it as well)? Jul 6, 2020 at 11:37
0

This problem arises because you moved some cells from one place to another, which causes the Sum formula of these cells expand to cells (up or down) - depends on where you move the cell.

Suppose you have some rows A1 to A10 and you put Sum formula after these rows, and you also have another data at A12 to A20, now you move any cell from first series to below may be to A15 or end of A20, the Total of A1 to 10 expands to A1 to A20 and you face this problem. Just Sum the cells again and it will be solved, hope that answer helpful.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .