Dynamic Arrays Formulas in Excel 2021 and Microsoft 365 (Completed)

Since its inception, Excel’s recalculation engine has never been revamped. Excel expert David Ringstrom, CPA, introduces webcast participants to an entirely new class of worksheet functions known as dynamic arrays. Available only to Office 365 subscribers, dynamic arrays recalculate traditional formulas faster. They also eliminate the need to use menu commands to sort, filter, and/or remove duplicates from a list of data. You can 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.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2021, 2019, 2016 and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.

Microsoft 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 2021, Excel 2019, and so on.

Who Should Attend
Practitioners who wish to keep up with cutting-edge features in Office 365.

Topics Covered

• Assigning random numbers to a block of cells by way of the new RANDARRAY function
• Contrasting traditional static amortization tables with a dynamic amortization now possible in Excel 2021 and Microsoft 365
• Crafting self-resizing formulas with the new Spilled Range Operator in in Excel 2021 and Microsoft 365
• Create self-updating Data Validation lists by way of the OFFSET and COUNTA functions
• Creating a dynamic list of period numbers in an amortization table with the SEQUENCE function
• Creating a dynamic list of sequential numbers with the SEQUENCE function in Excel 2021 and Microsoft 365
• Creating an in-cell list by way of Excel's Data Validation feature
• Diagnosing the new #CALC error that can arise within improperly crafted dynamic array formulas
• Displaying subsets of data dynamically by way of the new FILTER worksheet function
• Filtering based upon two or more conditions with the FILTER function in Excel 2021 and Microsoft 365
• Integrating the SEQUENCE function within SUMIF to create a dynamic running balance column for an amortization table
• Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets

Learning Objectives

• Recognize a dynamic array function from a list of worksheet functions
• Identify the function that returns the principal portion of a single loan payment based on a constant interest rate and constant payments
• Recognize the character that represents the Spilled Range Operator

Level
Intermediate

Instructional Method
Group: Internet-based

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Experience with Worksheet Functions in Microsoft Excel.

Advance Preparation
None