Saving Time With Excel's Little-Known Lambda Function
In 2020, Microsoft unveiled the new Lambda function in Excel. Perhaps due to the release’s timing (Pandemic!), the function remains unknown and unused by most Excel users. Yet, for those who have discovered this function and its’ power, Lambda provides the capacity to create and store user-defined functions (UDFs) in Excel. In this article, you will learn how to take advantage of this feature and why doing so would be a good move for most Excel users.
First, What Is A User-Defined Function
Almost all Excel users are familiar with functions, and presently, Excel contains hundreds of functions, such as SUM, IF, NPV, and XLOOKUP. These functions underlie the calculations in row after row and column after column of today’s generation of workbooks. Seemingly, there is a function for every need.
However, instances arise where Excel does not offer a pre-defined function to meet the specific needs of some users. For example, perhaps a user needs to allocate monthly overhead expenses to multiple operational departments, such as Finance, Manufacturing, and Executive. Without UDFs, someone must create a formula in Excel each month to handle the allocations. However, allocating the monthly overhead becomes quick and easy by creating and saving a UDF to handle the assignment.
Notably, the concept of UDFs in Excel is not new. Before the advent of Lambda, you could (and still can) create UDFs by writing Visual Basic for Applications (VBA) code and saving the code as an Excel add-in. Although this process works, it is too cumbersome for many, and learning to write VBA code is not usually high on most users’ priority lists.
So, to summarize the answer to the question “What is a user-defined function,” the short answer is simply this: a user-defined function is a function – similar to other Excel functions – that you can create and use in your Excel workbooks.
How Do I Create User-Defined Functions Using Lambda?
So, what is a better option if creating UDFs is clumsy and challenging using the VBA approach outlined above? Lambda! In short, the feature provides a simple method for creating and saving UDFs in Excel. Specifically, Lambda allows you to create customized functions in Excel that you can save and reuse anytime. Further, when you save your UDFs in Excel, you can assign easy-to-remember names to them, so accessing them later is painless.
The Mechanics Of Creating A User-Defined Function Using Lambda
Consider the following example. You must allocate corporate overhead expenses to the Finance, Manufacturing, and Executive departments each month. Further, the allocation percentages are 19%, 48%, and 33%, respectively. You create a formula like the one shown in Figure 1 below to perform the allocation.
Of course, you must re-create this formula monthly because you use a different workbook each month to complete your close-out tasks.
Alternatively, you can use Lambda to store your formulas for future use, eliminating the need to re-create them repeatedly. Perform the following general steps to create a LAMBDA.
- Create and test the formula you want to save as a Lambda so you know it performs as intended and provides accurate calculations.
- When you’re ready to save the formula as a Lambda, click Name Manager on the Formula tab of the Ribbon and then click New. This action initiates the process of creating a new defined name that the formula you will save as your Lambda
- Enter the required fields in the New Name dialog box and click OK to save your new Lambda.
- Once you create and save your formula as a Lambda, you can use it just like any other Excel function.
A Specific Example
Returning to the example above, suppose you wanted to automate overhead allocations. You can do this by creating Lambdas and using them to distribute the expenditures. Specifically, assume you want a formula to charge 19% of overhead to the Finance team, 48% to the Manufacturing organization, and 33% to the Executive group.
To create a Lambda to allocate 19% of overhead to the Finance team, click Name Manager on the Ribbon’s Formulas tab. Next, enter the following data in the formulas dialog box, enter the following data.
- Name: Enter the name you are assigning to your Lambda. You will use this name to call the function in your Excel workbook.
- Scope: Identify whether the Lambda can be used throughout the workbook in which you save it or whether you want to restrict its use to a specific worksheet within the workbook. In general, you will likely want to establish workbook-level scopes.
- Comment: If desired, add an optional comment describing your Lambda.
- Refers to: Enter the parameters for your Lambda and the formula used to calculate the result. A parameter is information you need to pass to your Lambda. Often, a parameter is a cell reference, but it could also be a string or a number.
To illustrate, consider the Lambda pictured in Figure 2. That user-defined function allocates 19% of the overhead to the Finance group. Specifically, in the Refers to box, the Lambda passes the Overhead parameter and a formula that multiplies that parameter by 19%.
After creating a Lambda, you can view it by accessing Excel’s Name Manager. To illustrate, consider the three Lambda’s visible in the Name Manager shown in Figure 3. Further, you can edit or delete a Lambda from the Name Manager.
Why Should I Create User-Defined Functions?
Hopefully, the usefulness of Lambda’s is self-evident: build a formula once and use it anywhere in a workbook without having to recreate it or copy-and-paste it. This feature should save time and improve accuracy in your workbooks.
Further, you can enhance the usefulness of a Lambda by making it available in all future workbooks you create. If that’s your desire, save your Lambdas in your book.xltx template file. Any Lambdas you save there will be available in all workbooks you create in the future, saving even more time and expanding the usefulness of this powerful feature.
Summary
Excel’s Lambda feature is a little-known, little-used feature that offers tremendous promise and potential to eliminate creating the same formula repeatedly. With Lambda, you can create and save formulas and use them repeatedly without recreating or copying. Instead, you can use the Lambda functions you create with the same ease as you use other Excel functions, such as SUM. If you haven’t yet discovered the incredible power of this feature, now is a great time to learn more about Excel’s Lambda feature!
For more information on Lambda, visit this Microsoft website. And for learning opportunities on Excel, click here to learn more about K2 Enterprises and our courses.
