Bug that shows formulas instead of their results, in SOME columns

[lotsof]

Excel 2016
In some columns, cells show the formula, in other columns, the result of the formula is shown. I have formulas in a new sheet that simply refer to another sheet in the same workbook, called "OriginalData".

Please Login or Register  to view this content.

It happened when I manually changed the row in the reference formulas from row 4 to row 3 to adjust for a mistake. Before that, this problem did not occur. It happens in one column that refers to a date, and in one column that refers to free text. In other columns with free text and those with a date, the content is shown as it should. If I type any other formula, like =1+1 in a buggy cell, it shows "=1+1" rather than the result "2". The format of the buggy F-column is Date.

I've tried to force calculation with shift+F9 and to close and restart Excel, to no avail.
What can I do to fix this, and to avoid having it happen again?

[shukla.ankur281190]

You have to format your cell as date format press on that cell Alt->H->N->S or if the cell show formula instead of result then press Control+~

[TMS]

Probably not a bug. See https://support.office.com/en-gb/art...9-0c1b77a5356f

[lotsof]

You have to format your cell as date format press on that cell Alt->H->N->S

Thanks! It worked for the buggy date column. The column it referred to was Text, not Date.

But it doesn't help for the buggy column. The column it refers was already formatted as Text, and changing the format back and forth doesn't help.

[shukla.ankur281190]

if the cell show formula instead of result then press Control+~

[lotsof]

Probably not a bug. See (your link)

I switch between showing formulas and their results (by clicking on Show Formulas under the Formulas Tab). The buggy column does not react.

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[lotsof]

Here ten rows of data in the workbook are attached.

[TMS]

Is there a space in front of the equals sign?

[shukla.ankur281190]

Your cell is formatted as text so that formula become visible as text not retrieving result instead. You have to press Control+Alt+~ to make C column as general format after that formula would work.

Check then attached file.

[lotsof]

I see now that in the attached file, the buggy column refers to OriginalData!C33 onwards, it should be C3 and it was before I mistyped why experimenting to make this work. C33 or C3 doesn't make any difference for this problem.

[lotsof]

I have Swedish keyboard. I have to use AltGr to get the ~ symbol. What does it correspond to in the menu tabs? Switching Show Formulas on and off under the Formulas Tab doesn't help for this buggy column.

[shukla.ankur281190]

Ok you can simply go to the Home navigation tab and under number format you have tp just make it as general

[lotsof]

It works, great!
What's the theory behind this?
When I edit a formula, the format of the cell is automatically changed from general to text and the problem reoccur.

[shukla.ankur281190]

Perhaps you have downloaded this spreadsheet from any software like SAP, FOCUS etc, and in that software the particular columns specified formatting that is why it is happening may be