#Excel: Using Conditional Formatting to Highlight Cells That Contain Array Formulas Using the FORMULATEXT Function by David Hager

A common story among Excel developers is the easy fixes they made for their clients to get a formula in a cell to calculate correctly. In many cases, the problem was corrected by simply entering a formula as an array formula (Control-Shift-Enter). This only takes seconds to perform, and usually the developer received a heafty amount of money relative to the task. So, if a conditional format can be applied to worksheet cells that potentially contain array formulas, the CF highlight will indicate which are array formulas.

Originally, I thought of creating an UDF with VBA that could be used as the CF Boolean formula. Then, I remembered the new Excel FORMULATEXT function and wondered whether it would return the “curly brackets” from a cell containing an array formula. I doubted that it would, but it did! 😊 So, I made a defined named formula (with E6 as the active cell) as shown in the figure below.

IsArray2

The formula, IsArrayFormula=LEFT(FORMULATEXT(E6),1)=”{“, is True if the first character in the string is a left curly bracket. Then, I was able to use that formula as a conditional format, as shown below.

IsArray1

Now, if this technique is used in auditing Excel workbooks, the cells containing array formulas will be able to be easily viewed.

The example workbook can be downloaded here.

IsArray

Advertisements

5 thoughts on “#Excel: Using Conditional Formatting to Highlight Cells That Contain Array Formulas Using the FORMULATEXT Function by David Hager

  1. Pingback: #Excel Super Links #29 – shared by David Hager | Excel For You

  2. Pingback: #Excel For You Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

  3. Pingback: #Excel Super Links #60 – shared by David Hager | Excel For You

  4. Pingback: #Excel Super Links #139 – shared by David Hager | Excel For You

  5. Pingback: #Excel Super Links #150 – Special Edition | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s