In this presentation, author and Excel expert David H. Ringstrom, CPA, will explore advanced techniques for managing and consolidating data using Power Query and modern Excel functions. Attendees will learn how to create flexible workbook links, efficiently combine multiple worksheets, and utilize the VSTACK and HSTACK functions available in Microsoft 365. David will also cover essential Power Query properties, address external data security warnings, and demonstrate how to consolidate multiple entities seamlessly. Additionally, participants will discover the power of using the SUMIF and INDIRECT functions alongside monthly worksheets to streamline their data analysis processes. Join us to enhance your Excel skills and unlock new efficiencies in your data management tasks.

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:

• Stack different ranges of cells vertically or horizontally with the VSTACK and HSTACK functions.

• Filtering unwanted data out of Power Query results.

• Creating flexible workbook links that can be copied down columns or across rows.

• Configuring Power Query queries to update automatically in the most efficient manner possible.

• Using features within Excel to locate workbook links.

• Creating self-updating financial spreadsheets by using Power Query to pull data via automated queries that also overcome common issues in exported reports.

• Using INDIRECT to create a single formula that can retrieve data from any worksheet in a workbook by simply copying and pasting.

• Learning the risks of linked workbooks, determining if a workbook contains links, and seeing how links can hide within Excel features.

• Exploring how the INDIRECT worksheet function can automate pulling values from a group of individual worksheets.

• Learning how to repair broken workbook links.

• Combining data from multiple worksheets or workbooks with the Consolidate feature.

• Using the Break Links command to remove links to other workbooks.

Learning objectives:

• State the functionality that the Flash Fill feature offers.

• Define the purpose of the Forecast feature in Excel 2016 and later.

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

Level: Intermediate

Instructional Method: Group Internet Based

NASBA Field of Study: Specialized Knowledge (2 hours)

Program Prerequisites: Prior experience with Microsoft Excel is recommended.

Advance Preparation: None