Excel LAMBDAs vs UDFs

Overview

Have you ever written a custom function in Excel with a LAMBDA or a User-Defined Function (“UDF”)?  They are both great options for building functions that reduce complexity, combine separate functions into one, and allow you to reuse and update useful formulas from all over your workbook (check out this basic example 🖼️).

While they are both great options, there are some key differences you should be aware of as you choose which one to use in your Excel files.  In this article, we’ll discuss the pros and cons of LAMBDAs and DAFs and examples of both of them.

LAMBDA functions

Pros

There are several advantages of using LAMDBA to define custom functions, including:

• Like other dynamic array functions, they can spill natively when the formula results in an array.
• There is no need to use macros or understand VBA or other programming languages – leverage the existing Excel functions you know to build them.
• Formulas can be simple and brief or long and complex.
• They work well with other Excel features, like conditional formatting.

Cons

There are a few limitations to be aware of using LAMBDA functions as compared to UDFs, including:

• MS Office 365 is required, so they are not backward compatible with older versions of Excel.
• There are no advanced debugging tools for LAMBDA functions like there are with UDFs.
• LAMBDA functions can’t access operating system-level settings like UDFs can.
• LAMBDA functions are not directly useable in macros like UDFs are.

When to use

I’d recommend using LAMBDA functions when:

• All your users are on newer versions of Excel (ideally MS Office 365).
• You may want to build a formula that can easily spill across multiple cells.
• Your custom function will be relatively simple and contain just a couple lines of formula text.
• Your custom function will be specific to one workbook and you don’t expect to reuse the custom formula all over many workbooks.

User-defined functions

Pros

There are several advantages of using UDFs to define custom functions, including:

• They can be stored in Excel files, add-ins, or your personal macro workbook.
• There are extensive debugging tools available, just like when building macros.
• They are generally backward-compatible to Excel versions back to 2007, and in many cases, even earlier.
• They work really well macros, helping you make macros less complex and by making the UDF-referenced blocks much more reusable.

Cons

There are a few limitations to be aware of using UDFs as compared to LAMBDA functions, including:

• They must be saved in .xlsm files and enable macros, which may be seen as a security risk.
• They require using programming skills in Visual Basic for Applications (VBA).  Although it’s a relatively easy programming language to learn, it’s still more demanding to learn than standard Excel functions.
• They don’t natively spill across multiple cells like LAMBDA and other dynamic array functions.
• UDFs can sometimes be slower than LAMBDA functions, especially when using a spilling LAMBDA function.

When to use

I’d recommend using UDFs when:

• You have a solid understanding of VBA and using/sharing macro-enabled is an option for your users.
• You might want to use your function with macros or with other UDFs.
• Your custom function will be somewhat complex and debugging will be helpful to troubleshoot.
• You might need to access operating system-level settings.

Summary

For 90% or more scenarios, either a LAMBDA function or a UDF can get the job done, and it’s more up to your preferences and those of the people that will ultimately be using your files (as you can see in this basic example 🖼️).  There’s also nothing stopping you from using both of them and even using them together – I’ve done this a few times!  Ideally, it’s easier to maintain and for others to understand if you just use one option.  Either approach will make your files much more powerful and allow you to save quite a bit of time by reusing those custom functions.

Have you used LAMBDAs or UDFs extensively in Excel?  What’s your preference and why?  Let us know in the comments below!