Dynamic Arrays

Level: Intermediate

Available Durations:

  • 50 minutes
  • 60 minutes
  • 75 minutes
  • 90 minutes
  • 100 minutes
  • 120 minutes

Description:

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

Topics/Areas Typically 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 Microsoft 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 Microsoft 365.
  • Crafting self-resizing formulas with the new Spilled Range Operator in Microsoft 365.
  • Displaying subsets of data dynamically by way of the new FILTER worksheet function.
  • Understanding why dynamic array functions can’t be used within tables in Excel.
  • Filtering based on two or more conditions with the FILTER function in Microsoft 365.
  • Exploring the risks and benefits of participating in the free Microsoft Office Insider program.
  • Removing duplicates from a list with the new UNIQUE function.
  • Utilizing the PPMT and SEQUENCE functions together to return a dynamic column of principal paid amounts.

Learning Objectives/Why You Should Attend:

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

Target Industries:

  • Accounting and Finance
  • Business
  • Excel Users
  • Consulting
  • IT
  • Auditing
  • Human Resources
  • Marketing
  • Sales
  • Government
  • Tax

Target Job Title:

  • Accountants
  • CPAs
  • CFOs
  • Controllers
  • Income Tax Preparers
  • Enrolled Agents
  • Financial Consultants
  • IT Professionals
  • Auditors
  • Human Resource Personnel
  • Bookkeepers
  • Excel Users
  • Marketers
  • Government Personnel

About the Instructor:

David H. Ringstrom, CPA is the owner of Accounting Advisors, Inc., an Atlanta-based spreadsheet consulting firm that he started in 1991. Throughout his career David has spoken at conferences on Microsoft Excel, and written dozens of freelance articles about spreadsheets. He offers Excel and Access training and consulting services nationwide.
Pricing and Format Options:

Pricing and Format Options:

Click here to learn about the presentation format and view pricing information.

Click here to view other topics.