Q. There are formulas that I am repeatedly having to create in my Excel workbook, and there are no built-in functions in Excel that can do these calculations. Is there a quicker way to reuse the same formulas without copying, pasting, and editing or starting from scratch?
A. I love all things Excel, but the LAMBDA function is one I am most excited about. It allows you to build your own custom functions. LAMBDA is a relatively new function for users of Excel 365 and Excel for the web.
So, why would you want to spend your valuable time creating a custom function? One of the principles of working smart is to automate tasks where possible. If you are routinely creating formulas that accomplish the same purpose, spending a little extra time upfront to create a function that can be reused will save you time in the end, possibly a significant amount of time.
There are some steps that you should follow when creating custom functions with LAMBDA:
- Test your formula.
- Create the LAMBDA in the spreadsheet.
- Define the LAMBDA in the Name Manager.
The LAMBDA function requires that you define a parameter or a calculation. The parameter(s) are optional and can be used in the calculation. The calculation is required, and it must be listed as the last argument. I think this becomes much clearer with an example.
Note that the content that follows is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.
Let's go through an example. We will create a LAMBDA to calculate Cost Per Unit. The way this is calculated is to take Total Cost divided by the Quantity. (See the screenshot of the table with this information below.)
The calculation for Cost Per Unit is a very easy formula to create and copy to other cells. However, we are using a very simple formula in this example so as not to distract from learning about LAMBDA. The true power of the LAMBDA function will be realized by using it to recreate more complex formulas.
Let's now follow our steps listed above.
1. Test the formula: In cell D2, I enter =B2/C2. Because this is a table, the Cost Per Unit populates on every row and the formula works.
2. Create the LAMBDA in the spreadsheet: In cell F2 (you can use any cell outside of your table), I create the LAMBDA. I enter =LAMBDA(TotalCost,Quantity,TotalCost/Quantity)(B2,C2). TotalCost is the first parameter, Quantity is the second parameter, and TotalCost/Quantity is the calculation. We then define the first parameter as B2 (for this calculation only to make sure it works) and define the second parameter as C2. The LAMBDA works.
3. Define the LAMBDA in the Name Manager: Copy just the LAMBDA without the cell references for that specific formula to create a custom function that can be used repeatedly, =LAMBDA (TotalCost,Quantity,TotalCost/Quantity). Go to the Formulas tab and choose Define Name from the Defined Names group. Paste the copied LAMBDA into the Refers to: box. Enter the name that you want to call your new custom function in the Name: box. I named mine CostPerUnit. If you would like to add any comments, you can add them in the Comment: box. This is particularly useful if your parameters may need more explanation.
See the screenshot below for the completed New Name window for CostPerUnit.
Now we have a new built-in function in our Excel workbook called CostPerUnit that we can call anytime we like. We can use it the same as any other built-in Excel function. The custom function will be available only in the workbook where it was created, not in all of your Excel workbooks. Let's put our cursor back in cell D2, the first row where we want to calculate our Cost Per Unit (see the screenshot below). Type =CostPerUnit. You see it's listed among the other built-in functions now. Select it and define the parameters. The first parameter is cell B2, and the second parameter is cell C2. The final formula should look like this: =CostPerUnit(B2,C2) or =CostPerUnit([@[Total Cost]],[@Quantity]), if your data is contained in a table. See the screenshot below for the resulting values of the new function we created.
I have created a workbook with an example of using LAMBDA and an accompanying video.
I recommend you create custom functions that can replicate the more complicated formulas that you routinely use. Work smart!
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org.We regret being unable to individually answer all submitted questions.
