Excel tips: how to use custom functions without macros

In my December 2020 article, I shared a few custom functions that were created in Excel’s programming language, Visual Basic for Applications (VBA). Back then that was the only way you could create your own functions. This has recently changed with the introduction of the LAMBDA function in the subscription version of Excel (Microsoft 365, formerly Office 365).

The LAMBDA function allows you to create your own custom functions using range names with no VBA (macros) involved. This means the file is a standard .xlsx file.

The LAMBDA function is like the LET function covered in previous articles. By itself it doesn’t perform any calculations. It enables you to capture cells and ranges as inputs and then perform specialised calculations on those cells or ranges and produce a result.

The beauty of this function is that you can capture a complex calculation in a single function and repeat that calculation throughout the file using a descriptive function name instead of a complex formula. This can shorten and simplify formulas making them easier to understand. The custom function’s definition is centralised. If you need to modify the formula you only need to change it in one place.

This is a new feature and is still being developed. I wanted to share two examples and explain a few new features that can assist in the use of LAMBDA. Along with the LAMBDA function, other functions are being added to make the Excel’s function language behave more like a programming language.

GST exclusive

Let’s start with a simple example. Let’s say you have a system that provides you with invoice totals including GST and all the invoices include GST. You need the GST exclusive amount. Figure 1 shows the formula in cell C2 that calculates the GST exclusive amount. It is based on the GST percentage in cell H1 and the value in column B. It is rounded to two decimal places.

We need to use this calculation throughout the file. We can create our own function that takes a value and returns the GST exclusive value. We will create a range name called GSTExclusive as our custom function.

Step 1 – Create the LAMBDA function in a cell and test it.

The LAMBDA function is not meant to be used in a cell, but you need to test it in a cell. It uses a special layout to allow you to test it. Figure 2 compares the ROUND function with the LAMBDA function version incorporating the ROUND function.

In the ROUND function cell B2 is the value we need to convert. The LAMBDA will accept that as an argument called Amount.

The (B2) on the end is the special layout that is allowed with LAMBDA functions for testing. This can’t be done with normal functions. This passes the value of cell B2 to the Amount parameter at the start of the LAMBDA function. You can define more than one parameter. The ROUND function uses the Amount value in its calculation. The LAMBDA function returns the value that the ROUND returns. Remember LAMBA is not meant for use in cells. Things will become clearer when you see the final solution.

Step 2 – Copy the LAMBDA function to a range name.

In the Formula Bar use your mouse to select and copy the LAMBDA function including the = sign but excluding the (B2) on the end.

In the Formulas ribbon tab click the Define Name button in the Defined Names section.

In the Name box enter GSTExclusive. In the Refers to box paste in the LAMBDA function. Click OK. See Figure 3.

Step 3 – Use the new range name function. The range name can now be used like a function throughout the file. See Figure 4.

Step 4 – Document the range name.

When you type a standard Excel function name Excel will display the arguments required by the function. You can mimic this functionality by entering the function details in the Comment section of the New Name or Edit Name dialog. You can do this at the creation stage (Step 2), or you can do it after you have created the name.

In the Formula tab click the Name Manager. Find the GSTExclusive and select it and click the Edit button. In the Edit Name dialog add the comments shown in Figure 5.

The comment text appears when you start typing the name.

Notice that in the Refers to box the reference to cell reference $H$1 (the GST percentage) has been updated to include the sheet name, which is GST example. This ensures the function works consistently throughout the file.

Sheet name function

The next example is more complex and expands upon the April 2021 article that demonstrated the LET function. The LET function allows you to use variables within formulas and it works seamlessly with the LAMBDA function. We will create a function called SheetName that returns the sheet name based on a cell reference.

Figure 6 shows all the formulas that extract the sheet name from the current sheet. The formulas are displayed below the cell that contains the formula. The sheet is called Next example.

The formula in cell A1 works in all versions of Excel.

The LET function in cell A4 requires the subscription version of Excel. The LET function shortens formulas by reducing duplication by using variables. Note the CELL function is used three times in the cell A1 formula, but only once in the LET version in cell A4.

The LAMBDA function in cell A7 provides the cell reference (Ref) for the CELL function within the LET function.

This will be a common structure. The parameter from the LAMBA function is passed directly to the variable definition in the LET function, making it easy to convert an existing LET function into a LAMBDA function.

Cell A10 uses the range name function that uses the LAMBDA function and returns the sheet name.

Sharing functions

If you copy a sheet from one workbook to another, the LAMBDA range name functions you have created will also be copied across.

Note the GST example is not a good function to copy between files, as it relies on a link to a cell in the file for the GST amount. The second example will work in any file.

This has been an introduction to the topic of the new LAMBDA function. This new function opens up a lot of opportunities to shorten, centralise and simplify formulas in Excel.

Advanced formula environment add-in

To coincide with the release of the LAMBDA function, there is a new free Excel Add-in called the Advanced Formula Environment, which can make working with LAMBDA functions easier. It provides a separate formula editing task pane, which adds line breaks and syntax colours to LAMBDA range names. This is like working with DAX measure formulas in Power Pivot or Power BI.

The companion video and Excel file will go into more detail to demonstrate these techniques.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected]