Excel: Combining Subformulas Using Excel Labs

But joining all those subformulas into one may result in something that’s impossible to understand. The new Excel Labs add-in can simplify working with long formulas.

The traditional method of combining subformulas would be to copy the formula characters from the formula bar for cell B2 and then paste those characters anywhere that B2 is referenced in later formulas. Repeat this for C2, D2, and so on until there are no remaining references to the subformulas. Unfortunately, that can result in one long formula that’s extremely difficult to decipher, where even finding the start and end of one subformula is a challenge.  

Earlier this year, the Microsoft Labs team in Cambridge, England, released a free add-in for Excel called Excel Labs. Created to help people build new formulas with the LET and LAMBDA functions, this add-in offers a new formula editor designed for use with very long formulas.

I was amazed by the bit of functionality offered in the Excel Labs tool. With the Import from Grid icon, you can ask the add-in to analyze a system of subformulas and to automatically combine the formulas into a single easy-to-read formula.

USING THE ADD-IN

From the Insert Tab in Excel, select Get Add-Ins. Search for “Excel Labs.” After the add-in downloads, you’ll have a new pane on the right side that offers Advanced Formula Environment and one or more other tools developed by the Microsoft Labs team.

Currently, there are three tabs in the Advanced Formula Environment: Grid, Names, and Modules. Select the Modules tab. The black bar offers three icons: Save, Import from URL, and Import from Grid.

Select the cells that contain your formula logic (see Figure 1). In this case, it includes the Input cell in A2; subformulas in B2, C2, D2, and E2; and the final answer in F2. Choose the Import from Grid icon. Because you preselected A2:F2, the pane will assume that A2 is the input cell and F2 is the output cell. 

The tool is able to deal with any system of subformulas that build up to a single result. The actual purpose of the formulas used in Figure 1 isn’t important—they’re merely for illustration of the tool. In this particular case, the input phrases contain an unknown number of words followed by an account number and an amount. The subformulas are designed to isolate the account number by finding the next-to-last word.

Click the Preview icon at the bottom of the pane. In less than a second, the add-in will build a brand-new formula. The headings from row 1 are used in the LAMBDA and LET functions. Because the heading above cell F2 contains the word “Account,” the add-in is proposing creating a named formula called Account. The name of the formula can be used in the grid. In this case, the formulas in B2:F2 can be replaced with a single formula of =Account(A2). The name should be short and descriptive. You’re welcome to change the proposed name.

Once you’re satisfied with the name, click the Create button.

The Excel Labs add-in will create a new Named Formula in the Name Manager. To use the formula in the grid, simply write a formula such as =Account(A2), as shown in Figure 2.

If you look in the Name Manager or in the Excel Labs pane, you’ll see this definition for the Account formula:

Account = LAMBDA(Phrase,

LET(

No_Extra_Spaces, TRIM(Phrase),

WordCount, LEN(No_Extra_Spaces) -
    LEN(SUBSTITUTE(No_Extra_Spaces, " ", ""))
    + 1,

AddCarat, SUBSTITUTE(No_Extra_Spaces, " ", "^", WordCount - 2),

PostCarat, MID(AddCarat, FIND("^", AddCarat) + 1, 30),

LEFT(PostCarat, FIND(" ", PostCarat) - 1)))

Because the named formula is stored in the Name Manager, the formula will travel with the workbook. It will continue to work if you send the workbook file to a coworker. There’s no need to have VBA enabled because it isn’t using VBA. The named formula will even work in Excel environments where there’s no VBA, so it will work in Excel Online.