Formula to indentify if a cell contains a formula?

[DanD]

I've a spreadsheet in which a selection of cells between A1 and BC5 should contain a formula, is it posible to create a conditional format or cell(or multiple cells) to confirm if the range contains formulas in each cell

Thanks

Dan

 

[njimack]

You can use the below user-defined function:

Function Equals_Formula(myrange As Range)
Equals_Formula = myrange.HasFormula
End Function

Your CF formula applied to each cell in the range would be:
equals_formula(A1)=TRUE

Or you could enter the following formula into an empty cell:
=equals_formula(A1:BC5)

If all cells contain a formula, this will return TRUE. If they all contain values, it will return FALSE. If they contain a combination of formulae and values, it will return 0.

 

[DanD]

Very neat solution, I was hoping for a solution that didn't involve VBA but this is a perfect alternative.

 

[njimack]

Glad to help, thanks for the feedback.

I've tried without success to find a non-VBA approach, which is why I devised this.

 

[brettdj]

A couple of other options

My Mappit! addin maps files to highlight potential inconsistencies in changing formulae, hardcoded cells etc. So it will show graphically in a separate file which cells are formulae or not

You can use XLM with conditional formatting to highlight formulas, see David Hager's solution
Cheers

Dave

 

[Mel Smith]

Using Ctrl + `(grave accent) is the toggle that shows them all and hides them all.

HTH

Mel

 

[brettdj]

A couple of other options

My Mappit! addin maps files to highlight potential inconsistencies in changing formulae, hardcoded cells etc. So it will show graphically in a separate file which cells are formulae or not

You can use XLM with conditional formatting to highlight formulas, see David Hager's solution
Cheers

Dave

link fixed

David Hager's solution