Excel’s recalculation engine has never been revamped since its inception — until now.
In this practical webinar, Excel expert, David Ringstrom, CPA, will introduce you to a completely new class of worksheet functions—dynamic arrays. Available only to Office 365 subscribers, dynamic arrays recalculate traditional formulas faster. These functions eliminate the need to use menu commands to sort, filter, and/or remove duplicates from a list of data. With leading-edge dynamic array functions, you can also create formulas that resize themselves automatically, including an amortization table that expands into additional rows when a loan term increases, or contracts when a loan term is shortened.
You will see every technique demonstrated at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2019. David draws your attention to any differences in the older versions of Excel (2016, 2013, and earlier) during the presentation, as well as in his detailed handouts. You’ll also discover some features only available in Office 365, but not in Excel 2019. You will also receive an Excel workbook that includes most of the examples used during the webcast.
Office 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.
You will learn how to:
- Use dynamic array formulas and resize an amortization table based on changing the loan term.
- Prevent dynamic arrays from resizing by using wrapper functions.
- Integrate the SEQUENCE function within SUMIF to create a dynamic running balance column for an amortization table.
- Contrast traditional static amortization tables with a dynamic amortization now possible in Office 365.
- Pair the EOMONTH and SEQUENCE functions together to create a dynamic column of period end dates.
- Create a dynamic list of sequential numbers with the SEQUENCE function in Office 365.
- Create self-updating lists data validation lists with dynamic array formulas.
- Utilize the PPMT and SEQUENCE functions together to return a dynamic column of principal paid amounts.
- Create an in-cell list by way of Excel’s Data Validation feature.
- Display subsets of data dynamically by way of the new FILTER worksheet function.
- Nest dynamic array functions such as SORT and UNIQUE together.
- Describe the nuance of editing dynamic array–based formulas in Office 365.
Upon course completion, you will be able to:
- Identify the arguments that correspond to the UNIQUE function
- Recall which ribbon tab or drop-down menu contains the Data Validation feature
- Select the arguments for the EOMONTH function in Excel
Who Should Attend: Practitioners who wish to keep up with cutting-edge features in Office 365.
Program Level: Intermediate
Format: Live Webcast
Instructional Method: Group Internet Based for the Live Program. [If you attend the program on-demand (after the live date), you will receive Self-Study credit. Please note that to receive Self-Study credit you will need to complete a final exam after viewing the webinar.]
NASBA Field of Study: Computer Software & Applications (2 hours)
Program Prerequisites: None. Experience with Worksheet Functions in Microsoft Excel Recommended.
Advance Preparation: None