Documenting Formulas in an #Excel Range Using the FORMULATEXT, ISFORMULA and TEXTJOIN Functions by David Hager


Documenting Excel formulas is an important step in the auditing of spreadsheets. Presented here is a new technique to aid in that process.

The utility of the recently added FORMULATEXT, ISFORMULA and TEXTJOIN functions is sometimes underestimated. The FORMULATEXT and ISFORMULA functions were introduced with Excel 2013 and the TEXTJOIN function is available only the Excel 2016 version which comes with an Office 365 subscription. In the technique demonstrated in this article, al three of these functions are used in a single formula. For the result, see the following figure:


The key formula (in cell K1) is


To understand how this formula works, lets break it down into its main parts.

ADDRESS(ROW(fRange),COLUMN(fRange)) returns an array of cell addresses for the desired range (in this case, fRange, which is E1:F13).

FORMULATEXT(fRange) returns an array of formulas for that range.

When these two formulas are concatenated with a colon, you get an array that looks like this.


In this example, formulas only exist in cells E13 and F13, with the rest of the array elements as #N/A. When the ISFORMULA function is applied to the range is question, the result is


So, the final array created by the IF function looks like this.


When this array is processed by the TEXTJOIN function, it affords a string of formulas with their corresponding cell locations.

Note that this technique can also be used with non-contiguous ranges. Since the TEXTJOIN function can use additional arrays as arguments (see ), the same array formula used in the 3rd argument of this TEXTJOIN example can be used in subsequent arguments, provided that the desired ranges for those arrays are different.

You can download the example file here.



#Excel Super Links #129 – shared by David Hager


Excel Magic Trick 1455: Convert Text Month Criteria to Upper & Lower Dates for SUMIFS Function

Excel Find and Remove Duplicates

Find last value in a column

UnicodeBox ()

Extracting Ingredients from an #Excel List by Cost Category by David Hager

#Excel Super Links #128 – shared by David Hager


Unique distinct list sorted alphabetically and based on a condition

What is DAX?

How to Easily Manage Your Inventory in Excel

Feed the dragons in Game of Thrones with Excel 365 TextJoin

Using the #Excel Advanced Filter with a List Having Internal Numbers in a String By David Hager


Extracting Ingredients from an #Excel List by Cost Category by David Hager

Disclaimer: This technique uses the TEXTJOIN function. You need the Excel version included in Office 365 for the TEXTJOIN formula to work.

There are quite a few examples of a material based on ingredients. These include recipes, blends, paint mixtures, etc. Presented here is a method of extractiong from a list the ingredients for each mixture based on whether they have a low, medium or high cost.

The main list has 3 columns: Mixture, Ingredient and Cost. The lookup table has a list of ingredients based on cost category, as shown in the figure.


The main formula from cell G2 is

=TEXTJOIN(“, “,TRUE,IF(((Mixture=$F2)+(Cost=G$1))=2,Ingredient,””))

This formula is filled to complete the lookup table.

If the mixture and the cost are correct for the formula based on its position in the table, it will return the corresponding ingredient into an array. The other array elements are left blank. Then, the TEXTJOIN function concatenates the elements of the array, excluding blanks.

I hpe that you find this useful!

You can download the file here.



#Excel Super Links #127 – shared by David Hager


Best Practices for Sharing Power BI Reports

xlRelease ()

AutoRecover Excel File

Build your own usage report with Power BI

Using #Excel VBA to Create a Filter Criteria User Defined Function by David Hager


#Excel Super Links #126 – shared by David Hager


Use VLOOKUP and return multiple values sorted from A to Z

Excel to Alglib via xlwings

How to Auto Adjust Combo Box or List based on Dynamic Data Ranges in Excel

The 7 most dangerous Excel features

Over 150 Articles with Valuable Excel Content


#Excel Super Links #125 – shared by David Hager


New M-function: Table.TransformColumnTypesToFirstRowsTypes for PowerBI and PowerQuery

Employee Performance Panel Charts in Power BI with R

Excel Magic Trick 1454 DAX Measure Count Customer Totals Between Upper Lower Limits, Each Month

You Can Now Put Values On Rows In Power BI

My Collection of Excel Tips by David Hager