Excel Tip – Three clever ways to reveal if a cell contains a formula

It is often important to verify that a cell contains a formula to build an auditing tool that verifies whether someone has accidentally overtyped a formula. From Excel 2013 this is possible using the “=ISFORMULA(reference)” function which returns TRUE if the reference is a formula or FALSE if it is not.

However, this cannot check whether an entire range contains formulas and it will not work in Excel versions prior to 2013. To check entire ranges for formulas in any Excel version you need to write a Custom Function by entering the following VBA code into a module:

 

Function IsForml(rng As Range)

If rng.HasFormula Then IsForml = True

End Function

 

Using the “=IsForml(range)” custom function in the spreadsheet subsequently will return “TRUE” if the range consists of only formulas and “0” otherwise.

 

A quick way to display formulas rather than results in a spreadsheet is to use the keyboard shortcut “CTRL” + “ ` ” (backtick).

Share this tip on