Error displaying formulas in excel

Occasional Contributor

I am using Office365 and often encounter formulas in some cells of the spreadsheet.

As shown in the picture, cell AA7 shows the formula while the other cells don't, currently the position cursor is in cell AA9 which is the cell containing the formula (seen on the formula bar) but there is no phenomenon like cell AA7 .Loi.png

I have tried toggling the mode display formula on and off, but to no avail.

Usually I have to copy the cell without errors over the cell, for example, where copying cell AA9 and then pasting it into cell AA7 and then editing the formula, the phenomenon will no longer occur.

Please tell me the cause of this phenomenon and how to fix it.

Thank you.

8 Replies
best response confirmed by Hans Vogelaar (MVP)

@hoacvxd I suspect the cells concerned are, perhaps accidentally, formatted as text. change the format to General, press F2 to enter the formula bar and press Enter to confirm the formula.

Thank you.
Your solution solved my problem.
This workaround only works temporarily, when the formula is updated this error occurs again.
Is there any way to completely solve it?

@hoacvxd Don't understand. Can you share the file? Upload it in the Forum or share it via OneDrive or similar.

@hoacvxd Downloaded your file. It showed this:


Calculation set to Automatic, cell format "General". Needed to go into the formula bar (F2) and press enter. Don't really understand why. Can't replicate it on my own system. Sorry.


I create a formula using VBA similar to the following code:

Sub testFormula()
    Dim strF As String
    strF = "=A1,=B1,=C1"
    Range("A2:C2").Formula = Split(strF, ",")
End Sub

Although the format is general and press F2 to show the value, every time the macro is run, the above situation appears.

@hoacvxd Aha, I'll gladly turn you over to a VBA expert as I prefer not to touch that subject. But it seems your is inserting texts that only turn to real formulas once you re-confirm them in the Excel sheet (F2 and enter).