Excel cells don't calculate until I double-click them

Question

I have an excel sheet with three sum functions. The sheet is a template, so the rest of the sheet is blank, except for a couple text descriptions in Column A.

I use an ETL process (Talend Open Studio) to open the worksheet, input some data, append a couple other sheets, and save this different worksheet in a file share.

When I open the worksheet in the file share, the three cells with the sum functions show as zero, but when I double-click the cell, they calculate correctly.

I would like for the cells to show their calculation without me double-clicking them.

The cells are formatted as General, and the Calculation option is set to Automatic. What can I do?

are the formulas linked? or do they refer to data in the same workbook? — Scott Holtzman, Oct 27 '15 at 14:39

score 6 · Answer 1 · answered Dec 04 '18 at 11:22

6

select all, find-replace = with = and excel will see the contents as a formula.

answered Dec 04 '18 at 11:22

Meli

61
1
1

Thanks! Been struggling with a Sage interface that drops out and doesn't refresh cells. Recalculate worksheet didn't work but this work around did! – DWGKNZ Dec 06 '21 at 19:05

score 4 · Answer 2 · edited Mar 22 '19 at 17:42

4

I have seen such problem occurs when exporting data from "NoSQL" databases such MongoDB, etc.

You can insert a column in right side and go to the "Data" tab -> "Text to Columns" option.

Make sure you select appropriate format while separating. Whatever you want to separate will shift to right side. Now delete this undesired column. You are done.

edited Mar 22 '19 at 17:42

sbeam

4,622
6
33
43

answered Mar 09 '17 at 06:21

mohammed ismail

51
3

This option worked for me, though my problem is that the cell not showing the actual date value, thanks! – Suresh May 13 '19 at 07:09

score 1 · Answer 3 · answered Oct 27 '15 at 14:56

It is possible that Excel is seeing the cells as Text, despite the fact that you have formatted as General. This can be the case when dealing with data coming from an ERP such as SAP.

Try these two approaches to see if one works: Approach 1

Transfer your data to another sheet by Copying your data cells and paste special (values) into another sheet
Then copy and paste your formulas into the other sheet and see if that works.

Approach 2:

For all the cells you're having trouble with, enter formula =Value([your cell]) in fresh column
Copy that column and Paste Special Values over it
Point your formulas to this new column

It's hard to reproduce the problem you're having unless you have a datasource that is exhibiting the issue. But one of those approaches may help.

score 1 · Answer 4 · edited Jan 31 '19 at 00:52

How that idea worked for me:

Assumption: Source Data in Column A2

Data is "$4,238.74 "

In B2 = RIGHT(A2, LEN(A2)-1) to clear the leftmost and stubborn character ("$" that would not clear with find and replace)

In C2 = LEFT(B2, LEN(B2)-1) to clear the rightmost and stubborn character (identified as unicode 160 that would not clear either)

After the above cleaning was done the cells still would not work with something as simple as SUM() without double clicking each cell manually.

Since that is not efficient User6574133's divide by 1 idea was applied:

In D2 = C2/1 this called the stubborn (C2) cell that wouldn't calculate and created workable clean data in D2 that would calculate without having to double click each cell manually.

score 0 · Answer 5 · answered Jul 11 '16 at 10:37

0

In a blank column Put divide the same number by 1 (eg = 5.223/1 = 5.223) Problem solved

answered Jul 11 '16 at 10:37

user6574133

11

1

Your suggestion is really not clear - why would that help at all? Does it have to be that number? Does it need manually triggering? More information please. – LordWilmore Jul 11 '16 at 10:43

score 0 · Answer 6 · answered Jun 29 '18 at 23:18

0

It could be that you have calculations set to manual instead of automatic. To fix this, go to the Formula tab/Calculation/Calculation Options/tick Automatic.

answered Jun 29 '18 at 23:18

Omar

89
1
12

score 0 · Answer 7 · answered Mar 07 '19 at 19:54

0

I fixed mine merely by going to formulas, calculation tab, "calculate now". It woke Excel up.

answered Mar 07 '19 at 19:54

Mindy Martin

1

score 0 · Answer 8 · answered Jun 05 '19 at 15:20

0

Actually, go to Formulas, Calculation Options, and you will see it is set to MANUAL, change it to AUTOMATIC. I believe some update must have changed it to Manual.

answered Jun 05 '19 at 15:20

Cindy Miller

11

This is actually worth looking at. – Chris Catignani Jun 05 '19 at 16:47

score 0 · Answer 9 · answered Sep 02 '19 at 12:21

0

I solved in a very simple way: try saving the file with OpenOffice.

answered Sep 02 '19 at 12:21

Marco Gugliotta

1

Excel cells don't calculate until I double-click them

9 Answers9

Linked