Excel Agility: Advanced Excel Skills for Accountants

1. Introduction 
2. Please Ask Questions Today
3. Excel Versions00:06:39
4. VLOOKUP Function 00:07:16
5. MATCH Function 00:26:36
6. INDEX/MATCH Functions 00:28:40
7. XLOOKUP Function (Excel 2021+) 00:37:00
8. XLOOKUP Looking to the Left 00:41:24
9. XLOOKUP Finding the Last Match 00:44:31
10. XLOOKUP Matching Multiple Criteria 00:48:20
11. XLOOKUP Text vs. Numbers Causes #N/A 00:54:01
12. Clean A/R Aging with Power Query 01:01:11
13. Clean A/R Aging with Power Query - Steps 1 - 8 01:04:06
14. Clean A/R Aging with Power Query - Steps 9 - 18 01:06:30
15. Clean A/R Aging with Power Query - Steps 19 - 27 01:09:55
16. Clean A/R Aging with Power Query - Steps 28 - 36 01:13:38
17. Clean A/R Aging with Power Query - Steps 37 - 42 01:17:57
18. Enabling Excel's Solver Add-In 01:29:41
19. Solver: Find Amounts That Add Up to an Input - Steps 1 - 3 01:31:10
20. Solver: Find Amounts That Add Up to an Input - Steps 4 - 7 01:32:55
21. Solver: Find Amounts That Add Up to an Input - Steps 8 - 14 01:36:34
22. Solver: Find Amounts That Add Up to an Input - Steps 15 - 24 01:37:23
23. Solver: Find Amounts That Add Up to an Input - Steps 25 - 28 01:39:47
24. Solver: Find Amounts That Add Up to an Input - Steps 29 - 31 01:40:01
25. Solver: Find Amounts That Add Up to an Input - Steps 32 - 35 01:40:29
26. Solver: Find Amounts That Add Up to an Input - Steps 36 - 37 01:40:39
27. Thank You for Attending 01:42:18
28. Presentation Closing 01:43:40

• 

  David H. Ringstrom, CPA

  David H. Ringstrom, CPA, is a nationally recognized instructor who leads dozens of Excel webinars each year. He is the author of Microsoft 365 Excel for Dummies and several other books. With over 30 years of consulting and teaching experience he empowers users to work more efficiently in E [...]

  View Full Profile

• #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.