Issue in showing formulas/values

Dharmendra_Bharwad....  After clicking Enable Editing, do the following:

1. Press ctrl+a to select all cells.

2. Press ctrl+h to open the Find and Replace dialog box.

3. Enter IFERROR into the Replace field.

4. First, enter _xlfn.IFNA into the Find field, and click Replace All.

5. Then, enter IFNA into the Find field, and click Replace All.

6. Finally, click Close to close the dialog box.

Explanation....

IFNA is supported in only certain versions of Excel.  See https://support.microsoft.com/en-us/office/ifna-function-6626c961-a569-42fc-a49d-79b4951fd461 .

So when I open the Excel file in Excel 2010, I see _xlfn.IFNA to indicate an unsupported function.

(I also see just IFNA in most cells.  They should all be _xlfn.IFNA.  I do not understand why only some references to IFNA are converted.)

Usually, when Excel opens a file, it does not recalculate unless circumstances require it (e.g. "volatile" functions).  So before clicking Enable Editing, you see the last values calculated when the file was saved.

However, when we click Enable Editing, that triggers a recalcuation cycle.  That causes references to _xlfn.IFNA and IFNA to result in #NAME errors.

Replacing references to IFNA with references to IFERROR remedies the problem.

But we must be careful to replace _xlfn.IFNA before we replace IFNA because of the substring similarity.

(FYI, my experience is different.  I see values for cells that reference _xlfn.IFNA both before and after clicking Enable Editing, and I see #NAME for cells that reference IFNA both before and after clicking Enable Editing.  The difference might have to do with the older version of Excel that I use.)