Create Your Own Reusable Functions with Excel LAMBDA

Excel Lambda

Full disclosure: We are not expert-level Excel wizards. You might not be an Excel expert, either. While we know how to do quite a bit of things in there, there a folks we’ve dealt with who are absolute Excel wizards. We have clients that are Economic Research or Financial Management firms who have folks that can do some pretty cool things, and we have some guys in house that really enjoy creating spreadsheets with all sorts of data references, linking, calculations, etc… . But we know that we don’t know nearly as much as some folks. However, we do know a cool new feature when we see it.

While you could program some really cool features and functions into Excel with Visual Basic for Applications (VBA) and other macro-like languages, you’d have to do that in a macro-enabled Excel document, and those are generally bad news. Microsoft recently introduced what they consider the ultimate Excel worksheet function: LAMBDA. LAMBDA allows you to easily create reusable functions and calculations using Excel’s formula language instead of complicated VBA, Macro, or JavaScript code. Those functions can then be reused throughout your workbook, without the headache and security risks. Office Watch calls it a strange name for a really good Excel extra and has some examples of how it works (as well as a simple Metric to Imperial/US conversion example). You can also nest LAMBDA functions, allowing recursion in calculations which wasn’t available before.

So basically, you’ve created a complicate formula and want to re-use it somewhere else? Store is as a LAMBDA function (like “MyAwesomeFunction()”) and you can re-use it wherever. There is more technical details here on how it all works and how to set up your first function.

Yes, it’s a bit advanced, but if you work on massive spreadsheets that need to do a bunch of calculations calculations, LAMBDA would be a very valuable tool for you.

Comment : 0

    Leave a Reply

    Your email address will not be published.


    - A Team That Supports Your
    People, Not Just Your Technology