On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Taxation and Accounting, Software, Business Administration, Business Skills, Finance
- Credit: ATATX 1.5, ATAOP 1.5, ATAAA 1.5, CPE 2.0
-
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 “Exploring Microsoft Excel's Hidden Treasures: Turbocharge your Excel proficiency with expert tips, automation techniques, and overlooked features”. 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.
Topics typically covered:
- Matching on two or more columns of criteria at once with XLOOKUP.
- Exploring the XLOOKUP worksheet function in Excel 2021 and Microsoft 365.
- Assigning names to cells to streamline formulas and bookmark key inputs within a workbook.
- Streamline formula writing with by creating nicknames for repetitive values with the LET function in Microsoft 365.
- Documenting IF functions by using LET to assign nicknames to the inputs.
- Utilizing the ISOMITTED function with LAMBDA to create optional arguments.
- 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.
- Saving LAMBDA functions to GitHub for easier distribution and maintenance.
- Create your own custom worksheet functions without writing any code in Microsoft 365 with the LAMBDA function.
- Moving LAMBDA functions between workbooks.
- Understanding how using names in the wrong order can result in a #NAME? error with the LET function.
- Installing the free Excel Labs Add-In which enables API access to ChatGPT.
Learning objectives:
- State the purpose of the column_index_num argument within VLOOKUP.
- Recall the section of the Excel Options dialog box where the AutoRecover setting resides that controls how often Excel creates a back-up copy of your workbooks.
- State what SUMIF returns if a match cannot be found.
- Introduction
- Please Ask Questions Today! 00:03:11
- Excel Versions 00:05:15
- Microsoft 365 Windows Beta Channel 00:05:57
- Microsoft 365 Mac Beta Channel 00:09:53
- PRODUCT Function 00:10:29
- IF Introduction 00:13:24
- IFS Function (Excel 2019+) 00:21:36
- CHOOSE Introduction 00:21:36
- SWITCH Function (Excel 2019+) 00:30:06
- XLOOKUP Introduction (Excel 2021+) 00:35:11
- XLOOKUP Matching Multiple Criteria 00:41:44
- Create Names from Selection 00:46:18
- Apply Names to Formulas 00:51:03
- Diagnosing Broken Names 00:52:50
- #NAME? Error with Cell Names 00:56:46
- LET Function (Excel 2021+) 00:57:17
- Documenting IF with LET 01:08:12
- Calculating Volume with LET 01:12:24
- #NAME? Error with LET Function 01:16:00
- LAMBDA Function (Microsoft 365) 01:25:28
- LAMBDA Function (Microsoft 365) - Steps 1 - 5 01:34:52
- LAMBDA Function (Microsoft 365) - Using LAMBDA To Rewrite Formulas 01:40:
- ISOMITTED Function (Microsoft 365) 01:40:26
- Advanced Formula Environment - Steps 1 - 5 01:41:19
- Advanced Formula Environment - Steps 6-13 01:41:57
- Moving LAMBDAs Between Workbooks 01:42:17
- Thank you for attending! 01:43:47
- Presentation Closing 01:43:49
- #N/A Error 00:33:07
- #NAME? 00:53:09, 00:57:03, 01:16:28
- #VALUE! 00:25:47
- Cell 00:10:59, 00:46:33, 01:42:25
- CHOOSE Function 00:21:36, 00:27:04, 00:28:14, 00:30:32
- Column 00:29:45, 00:36::39, 01:20:09
- Formula 00:01:26, 00:02:09, 00:17:51, 00:46:26, 00:47:43, 00:51:54, 01:02:57, 01:11:04, 01:16:26, 01:22:01, 01:32:06
- IF Function 00:13:25, 00:24:56, 01:08:25
- IFS Function 00:21:36
- INDEX Function 00:29:40, 00:59:48
- ISOMITTED Function 01:40:26
- LAMBDA Function 00:00:10, 00:02:12, 00:10:40, 00:35:26, 01:25:29, 01:32:16, 01:34:55, 01:41:26
- LET Function 00:00:08, 00:01:56, 00:46:23, 00:57:21, 01:08:40, 01:12:38, 01:24:39
- Microsoft 365 00:06:09, 00:08:45, 01:16:01
- Name Box 00:47:26, 01:26:37
- Name Manager 01:42:38
- PRODUCT Function 00:10:41, 00:13:19, 01:12:33
- Row 00:29:44, 00:52:59, 01:02:37
- SWITCH Function 00:30:17
- VLOOKUP 00:29:37, 00:30:56, 00:32:13, 00:41:51
- Worksheet 00:02:14, 00:10:35, 00:46:32, 01:42:27
- XLOOKUP 00:35:21, 01:01:12, 01:25:53
#N/A Error: Excel displays this error when a lookup function, such as VLOOKUP or MATCH, cannot return the requested information.
#NAME?: When your formula has a reference to a name that is not defined in Excel, you will see the #NAME? error.
#VALUE!: #VALUE is Excel's way of saying, "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing." The error is very general, and it can be hard to find the exact cause of it.
CHOOSE Function : The CHOOSE function allows you to return a specified item from a list, but in certain cases, it also can be used to have VLOOKUP return data from the left of its criteria column.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Formula: A formula is an expression which calculates the value of a cell.
IF Function: Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.
IFS Function: The IFS formula in excel 2019 checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition.
INDEX Function: The INDEX function can be used to return data from within a given range based on a row and/or column number that you specify.
ISOMITTED Function: The Excel ISOMITTED function is a helper function for LAMBDA functions to allow optional arguments. Inside a LAMBDA function, ISOMITTED will return TRUE when an argument has not been provided. ISOMITTED takes just one argument, argument, which should be the name of an argument defined in the parent LAMBDA function.
LAMBDA Function: The LAMBDA function provides a way to create a custom function in Excel. Once defined and named, a LAMBDA function can be used anywhere in a workbook. LAMBDA functions can be very simple, or quite complex, stringing together many Excel functions into one formula. A custom LAMBDA function does not require VBA or macros.
LET Function: The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax.
Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
Name Box: The Name Box is the box to the left of the formula bar that displays the cell that is currently selected in the spreadsheet. If a name is defined for a cell that is selected, the Name Box displays the name of the cell. You can use the Name Box to define a name for a selected cell as well.
Name Manager: The Excel Name Manager is specially designed to manage names: change, filter, or delete existing names as well as create new ones.
PRODUCT Function: The PRODUCT function multiplies all the numbers given as arguments and returns the product. For example, if cells A1 and A2 contain numbers, you can use the formula =PRODUCT(A1, A2) to multiply those two numbers together.
Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
SWITCH Function : The SWITCH function was introduced in the Office 365 version of Excel 2016. It's not available to users of perpetual licensed versions of Excel 2016 or earlier versions of Excel. The function is an alternative to nesting multiple IF functions when displaying results based on a set of choices.
VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
XLOOKUP: The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. Where a valid match is not found, return the [if_not_found] text you supply.
