In this presentation author and Excel expert David H. Ringstrom will explore dynamic array worksheet functions, including the powerful SORT, FILTER, and UNIQUE functions, available in Excel 2021 and Excel for Microsoft 365. You'll see how to resolve common challenges such as the #SPILL! and #CALC! errors and dive into the versatility of CHOOSEROWS, CHOOSECOLS, VSTACK, and HSTACK functions available to Excel for Microsoft 365 users. You'll see how to create dynamic spreadsheets such as an amortization tables, that automatically adapts to the inputs that you provide.

David is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

Who should attend: Professionals seeking to use Microsoft Excel more effectively.

Topics typically covered:

• Seeing dynamic array formulas in action by resizing an amortization table based on changing the loan term.

• Diagnosing the new #CALC error that can arise within improperly crafted dynamic array formulas.

• Understanding the Sort feature in Excel for rearranging lists of data.

• Sorting lists of data dynamically from elsewhere in a spreadsheet with the new SORT function.

• Removing duplicates from a list with the new UNIQUE function.

• Filtering based upon two or more conditions with the FILTER function in Excel 2021 and Microsoft 365.

• Utilizing the PPMT and SEQUENCE functions together to return a dynamic column of principal paid amounts.

• (blank)

• Integrating the SEQUENCE function within SUMIF to create a dynamic running balance column for an amortization table.

• Crafting self-resizing formulas with the new Spilled Range Operator in in Excel 2021 and Microsoft 365.

• Pairing the EOMONTH and SEQUENCE functions together to create a dynamic column of period end dates.

• Troubleshooting the #SPILL! error that can arise in certain circumstances regarding dynamic array formulas in Excel 2021 and Microsoft 365.

Learning objectives:

• Recognize the color that dynamic array functions are displayed in within Excel's formula bar in all cells except the cell where the formulas was entered.

• State what the FILTER function returns if there are no records to be displayed.

• Identify the character that represents the spilled range operator in Excel.