Consolidating Financial Data Across Multiple Workbooks
Author: David H. Ringstrom
| CPE Credit: |
2 hours for CPAs |
In this course, 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 “Exploring Microsoft Excel's Hidden Treasures: Turbocharge your Excel proficiency with expert tips, automation techniques, and overlooked features”. 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 course 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.
Publication Date: April 2025
Designed For
Professionals seeking to use Microsoft Excel more effectively.
Topics Covered
- Filtering unwanted data out of Power Query results
- Learning how to repair broken workbook links
- Managing the external data security warning that may appear when you link external data into Excel spreadsheets
- Exploring how the INDIRECT worksheet function can automate pulling values from a group of individual worksheets
- Using INDIRECT to create a single formula that can retrieve data from any worksheet in a workbook by simply copying and pasting
- Consolidating financial statement exports from two or more entities by appending queries within Power Query
- Using features within Excel to locate workbook links
- Creating flexible workbook links that can be copied down columns or across rows
- Using the Break Links command to remove links to other workbooks
- Configuring Power Query queries to update automatically in the most efficient manner possible
- Appending data from two or more worksheets into a self-updating consolidated list with Power Query
- Combining data from multiple worksheets or workbooks with the Consolidate feature
Learning Objectives
- Describe the purpose of the INDIRECT function
- Describe the purpose of the SUMIF function
- State the location of the Edit Links command within Excel's ribbon (menu)
- Identify the keyboard shortcut that toggles absolute references within a formula in Excel for Windows
- Identify the command that allows you to extract data from an Excel worksheet directly into the Power Query editor
- Identify the feature in Power Query that enables you to combine two or more columns together
Level
Intermediate
Instructional Method
Self-Study
NASBA Field of Study
Computer Software & Applications (2 hours)
Program Prerequisites
Prior experience with Microsoft Excel is recommended.
Advance Preparation
None
