Maximize Excel: LET and LAMBDA Functions
Presented by David H. Ringstrom, CPA
 

Duration: 100 minutes
 

In this presentation, author and Excel expert David H. Ringstrom, CPA, will cover a range of topics to enhance your Excel skills. You will learn how to assign names to cells, which lays groundwork for the LET and LAMBDA functions. The LET function is available in Excel 2021 and Excel for Microsoft 365 and enables you to create pairs of names and associated values that can store intermediate calculations. David will address common issues such as the #NAME? error with the LET function and then introduce the LAMBDA function available in Excel for Microsoft 365, which enables you to create custom, reusable worksheet functions without writing programming code. He will also demonstrate the ISOMITTED function and showcase the Excel Labs Add-In. Additionally, you will discover how to save LAMBDA functions to GitHub and download them from there. David will guide you through the advanced formula environment and provide insights on moving LAMBDAs between workbooks. Don't miss this opportunity to expand your Excel expertise.

David is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

 
Who should attend: Professionals seeking to use Microsoft Excel more effectively..
 
Topics typically covered:
 

• Navigating directly to inputs by using Excel's Name Box, and then returning to the previous location in the workbook via the Go To commmand.

• Utilizing the ISOMITTED function with LAMBDA to create optional arguments.

• Moving LAMBDA functions between workbooks.

• Understanding how using names in the wrong order can result in a #NAME? error with the LET function.

• Downloading LAMBDA functions from GitHub into Excel's Advanced Formula Environment.

• Transforming cell references into range names by way of the Apply Range Names to Formulas command.

• Create your own custom worksheet functions without writing any code in Microsoft 365 with the LAMBDA function.

• Saving LAMBDA functions to GitHub for easier distribution and maintenance.

• Creating bookmarks and nicknames for key inputs by way of the Create Names from Selection feature.

• Installing the free Excel Labs Add-In which enables API access to ChatGPT.

• Documenting IF functions by using LET to assign nicknames to the inputs.

• Assigning names to cells to streamline formulas and bookmark key inputs within a workbook.

 
Learning objectives:

• State what the CHOOSE function returns when the Index_Num argument exceeds the number of Index arguments.

• State the location of the Apply Names command within the Excel menu interface.

• Recall which worksheet function ISOMITTED tests for optional parameters.

 
 
Level:
Advanced
 
NASBA Field of Study:
Specialized Knowledge (2 Hours)
 
Program Prerequisites:
Extensive prior experience with Microsoft Excel
 
Advance Preparation:
None