On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Taxation and Accounting, Software
- Credit: ATATX 1.5, CPE 2.0
-
In this presentation, author and Excel expert David Ringstrom, CPA, helps you expand your Excel toolbox by comparing the INDEX/MATCH functions to the XLOOKUP worksheet function in Excel 2021 and Excel for Microsoft 365. You'll also see how to transform ugly reports exported from accounting software and other platforms into analysis-ready formats by using Power Query. David will also show you how to use Excel's Solver feature to identify the combination of amounts from a list, such as invoices or deposits, that add up to an amount of your choice.
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.
Who should attend:
Professionals seeking to use Microsoft Excel more effectively.
Topics typically covered:
- Understanding how the VLOOKUP function allows you to look up data instead of having to manually reference individual cells.
- Using XLOOKUP to search lists from the bottom up to find the last match (instead of only the first match with VLOOKUP).
- Using Excel's Solver feature to find the amounts that match a total, such as which checks or invoices make up a particular total.
- Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces.
- Looking up data to the left or right of a given column with XLOOKUP.
- Using the MATCH function to find the position of an item in a list.
- Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
- Using Power Query to clean up accounting reports to remove pitfalls like blank rows, merged cells, missing data, and more.
- Enabling Excel’s Solver add-in for more complex what-if analyses.
- Exploring the XLOOKUP worksheet function in Excel 2021 and Microsoft 365.
- Using Excel's Solver feature to find the amounts that match a total, such as which checks or invoices make up a particular total.
Learning objectives:
- 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.
- Identify the menu in Power Query that contains the Fill Down command.
- Identify the type of data that the $ icon at the top of a Power Query column represents.
Level:
Intermediate
Format:
Live webcast
Instructional Method:
Group: Internet-based
NASBA Field of Study:
Specialized Knowledge and Applications (2 hours)
Program Prerequisites:
Prior experience with Microsoft Excel is recommended.
Advance Preparation:
None
- Introduction
- Please Ask Questions Today
- Excel Versions00:06:39
- VLOOKUP Function 00:07:16
- MATCH Function 00:26:36
- INDEX/MATCH Functions 00:28:40
- XLOOKUP Function (Excel 2021+) 00:37:00
- XLOOKUP Looking to the Left 00:41:24
- XLOOKUP Finding the Last Match 00:44:31
- XLOOKUP Matching Multiple Criteria 00:48:20
- XLOOKUP Text vs. Numbers Causes #N/A 00:54:01
- Clean A/R Aging with Power Query 01:01:11
- Clean A/R Aging with Power Query - Steps 1 - 8 01:04:06
- Clean A/R Aging with Power Query - Steps 9 - 18 01:06:30
- Clean A/R Aging with Power Query - Steps 19 - 27 01:09:55
- Clean A/R Aging with Power Query - Steps 28 - 36 01:13:38
- Clean A/R Aging with Power Query - Steps 37 - 42 01:17:57
- Enabling Excel's Solver Add-In 01:29:41
- Solver: Find Amounts That Add Up to an Input - Steps 1 - 3 01:31:10
- Solver: Find Amounts That Add Up to an Input - Steps 4 - 7 01:32:55
- Solver: Find Amounts That Add Up to an Input - Steps 8 - 14 01:36:34
- Solver: Find Amounts That Add Up to an Input - Steps 15 - 24 01:37:23
- Solver: Find Amounts That Add Up to an Input - Steps 25 - 28 01:39:47
- Solver: Find Amounts That Add Up to an Input - Steps 29 - 31 01:40:01
- Solver: Find Amounts That Add Up to an Input - Steps 32 - 35 01:40:29
- Solver: Find Amounts That Add Up to an Input - Steps 36 - 37 01:40:39
- Thank You for Attending 01:42:18
- Presentation Closing 01:43:40
- #N/A Error 00:23:27
- Absolute Reference 00:10:17, 00:15:23
- Add-In 01:29:51
- AVERAGE 00:07:31
- Cell 00:07:43, 01:33:41
- Cell Reference 00:07:42
- CHOOSE Function 00:41:40
- Column 00:09:01, 00:26:53, 00:29:09, 00:38:20, 00:41:47, 00:58:37, 01:01:57, 01:18:03
- Dialog Box 01:30:00, 01:32:27
- Filter 01:33:21, 01:39:53
- Formula 00:07:51, 00:10:36, 00:13:28, 00:25:31, 00:41:37, 01:35:59
- IFNA Function 00:23:36
- INDEX Function 00:06:57, 00:28:44
- LOOKUP 00:08:59, 00:44:41, 00:54:04
- MATCH Function 00:06:57, 00:26:39, 00:28:52, 00:44:46
- Microsoft 365
- Power Query 00:02:13, 00:07:02, 01:01:12
- Power Query Editor 01:08:54
- Row 00:10:14, 00:15:12, 00:35:16, 01:09:45
- Solver 00:02:53, 00:07:03, 01:30:18, 01:36:05
- SUM 00:07:30, 00:23:31
- Table 00:10:41, 00:35:07
- Table Array 00:09:49, 00:43:08
- Text to Columns Wizard 00:58:41
- VLOOKUP 00:06:56, 00:07:22, 00:10:08, 00:23:46, 00:26:47, 00:29:01, 00:41:29
- Workbook 00:02:32, 01:08:52, 01:22:40
- Worksheet 00:07:27
- XLOOKUP 00:06:46, 00:37:12, 00:44:36, 00:48:25
#N/A Error: Excel displays this error when a lookup function, such as VLOOKUP or MATCH, cannot return the requested information.
AVERAGE : Returns the average (arithmetic mean) of the arguments.
Absolute Reference : Absolute references in Excel are a direct link to a specific cell or range of cells that remain fixed if you copy or drag the formula. Absolute references are represented by $ symbols. A $ before a column letter freezes the column, while a $ before the row number freezes the row number. You can freeze the column letter and/or row number when needed.
Add-In: An Excel Add-In is a file (usually with an .xla or .xll extension) that Excel can load when it starts up. The file contains code (VBA in the case of an .xla Add-In) that adds additional functionality to Excel, usually in the form of new functions.
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.
Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.
Cell Reference: A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. There are three types: Relative, Absolute, and Mixed
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Dialog Box: A dialog box in Excel is a screen where you input information and make choices about different aspects of the current worksheet or its content, such as data, charts, and graphic images.
Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.
Formula: A formula is an expression which calculates the value of a cell.
IFNA Function : Introduced in Excel 2013, the IFNA function allows users to display alternative results for a calculation that results in a #N/A error. The IFNA function will, however, reveal other errors, such as #REF!, #NULL!, etc. IFNA isn’t backward compatible with Excel 2010 and earlier.
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.
LOOKUP: The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel.
MATCH Function: The MATCH function searches a prescribed range for specified criteria and returns a column or row number if a match is found. MATCH can be used with other functions that require a column or row number.
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.
Power Query: Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.
Power Query Editor: Power BI Desktop also comes with Power Query Editor. Use Power Query Editor to connect to one or many data sources, shape and transform the data to meet your needs, then load that model into Power BI Desktop.
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.
SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet.
Solver: Solver is an add-in for Microsoft Excel that allows you to perform what-if analysis operations. Excel's Goal Seek feature allows you to solve for a single input, while Solver allows you to solve for a single input while optionally placing constraints additional cells during the solving process.
Table: A table is an arrangement of data in rows and columns, or possibly in a more complex structure. Tables are widely used in communication, research, and data analysis. Tables appear in print media, handwritten notes, computer software, architectural ornamentation, traffic signs, and many other places.
Table Array: A table array is one of the arguments used in Excel's lookup functions, such as VLOOKUP and HLOOKUP. For VLOOKUP (vertical lookup), the table_array must contain at least two columns of data. For HLOOKUP (horizontal lookup), the table_array must contain at least two rows of data.
Text to Columns Wizard: An Excel feature which allows users to separate data from a single column within an Excel spreadsheet into two or more columns, or to remove unnecessary data from within a column.
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.
