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")

Answer 2

The MrExcel website (link below) has this method which uses old code from Excel 4 (which is still present for backward compatibility)...

Define a NAME such as "CellToLeftHasFormula" and in the "refers to" box put

=GET.CELL(48,OFFSET(INDIRECT("RC",FALSE),0,-1))

Then in column B use the formula =CellToLeftHasFormula which will return TRUE if it has.

Be aware that this will mean your Excel will now contain a macro and so will need to be saved as such (xlsm). I use this in Excel 2010.

For full explanation (and other .CELL options, besides 48) see MrExcel link: https://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html

Answer 3

You can use the Range.HasFormula property.

https://docs.microsoft.com/en-us/office/vba/api/excel.range.hasformula

EDIT:

Text and code from the above link:

"True if all cells in the range contain formulas; False if none of the cells in the range contains a formula; null otherwise. Read-only Variant. ..."

Worksheets("Sheet1").Activate 
Set rr = Application.InputBox( _ 
    prompt:="Select a range on this worksheet", _ 
    Type:=8) 
If rr.HasFormula = True Then 
    MsgBox "Every cell in the selection contains a formula" 
End If

Answer 4

You can restrict the user by protecting the column A.

You can directly check if a cell contains a formula by using a shortcut Ctrl + `.

You can use vba and write a user defined function : 1. Press alt + F11 2. Insert module in workbook 3. Paste this code

Function IsFormula(cell_ref As Range) 
IsFormula = cell_ref.HasFormula 
End Function

4. Now, use Isformula in the cell wherever you want.