Excel Formula - Check if cell has formula

Question

What formula do you use to check if another cell has formula? For example, I have 2 columns, A has cells which contains either a formula or a value.

(Column A usually contains Formulas but other users try to change their values by directly typing and replacing the formula that was previously there)

In Column B I want to add a formula that will say "HasFormula" if the cell on Column A has formula and say "PlainValue" if it contains a value.

I'm thinking maybe using =ISNUMBER() but that may not be accurate.

I am using Excel 2010.

Answer 1

Excel actually has a builtin ISFORMULA() function.

Say A1 has a formula and you want to check that. In say B1, you can use:

=If(ISFORMULA(A1),"HasFormula","PlainValue")

Edit: Per your comment, you don't have ISFORMULA(). An alternative is to create a quick UDF, and use the custom function in the worksheet.

In a workbook module, put this code:

Function isFormula(ByVal target As Range) As Boolean
     isFormula = target.hasFormula
End Function

Then you can call it like this: =isFormula(A1) and it will return TRUE if A1 has a formula.

If you can't use VBA, then you can use this formula: =IF(ISERROR(FORMULATEXT(A1)),"PlainText","HasFormula")