I have a big table where one column contains a formula each row (an array formula, to be specific), which returns with a string. But some of the formulas are overwritten with a similar string value.
I would like to create a conditional formatting to color the cells which are overwritten.
How can I create a formula to determine if the cell contains an array formula or a simple text value?
I have already tried =CELL("type", A2), but it returned "l" even if the cell contained a formula.
Thankfully the function FORMULATEXT does just that in the 2013 version of Excel.
It sounds like you want to determine if there is or is not a formula vice return the text so I would recommend adding an IFNA function as well like the example below.
=IFNA(FORMULATEXT(A1),"No Formula")
You could even take that one step further with an IF statement around that.
=IF(IFNA(FORMULATEXT(A1),"No Formula")="No Formula","This is simple text","This is a formula")
Check out Bill Jelean's podcast on doing this in previous versions of Excel.
Learn Excel 2010 - "=FORMULA TEXT() in Excel 2010": Podcast #1691
