Office 365 New Dynamic Arrays (Completed)

Date: Friday, October 18, 2019
Instructor: David H. Ringstrom
Begin Time:  12:00pm Pacific Time
1:00pm Mountain Time
2:00pm Central Time
3:00pm Eastern Time
CPE Credit:  2 hours for CPAs

Since its inception, Excel’s recalculation engine has never been revamped. Excel expert David Ringstrom, CPA, introduces 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 Office 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, 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 course.

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.

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

Topics Covered

  • Pairing the EOMONTH and SEQUENCE functions together to create a dynamic column of period end dates
  • Integrating the SEQUENCE function within SUMIF to create a dynamic running balance column for an amortization table
  • Preventing dynamic arrays from resizing by using wrapper functions
  • Understanding the nuance of editing dynamic array—based formulas in Office 365
  • Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets
  • Contrasting traditional static amortization tables with a dynamic amortization now possible in Office 365
  • Crafting self-resizing formulas with the new Spilled Range Operator in Office 365
  • Displaying subsets of data dynamically by way of the new FILTER worksheet function
  • Understanding why dynamic array functions cannot be used within tables in Excel
  • Filtering based upon two or more conditions with the FILTER function in Office 365
  • Exploring the risks and benefits of participating in the free Microsoft Office Insider program
  • Utilizing the PPMT and SEQUENCE functions together to return a dynamic column of principal paid amounts

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


Instructional Method
Group: Internet-based

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

Program Prerequisites

Advance Preparation

 Chat — Books Support