Skip to main content
Self-Study Courses

Consolidating Financial Data Across Multiple Workbooks

2 CPE Credits $38.00/credit hour
★ ★ ★ ★ ★ 5.0 (2 ratings)

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

Instructor

David H. Ringstrom

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches over 200 webinars each year. His Excel courses are based on over 30 years of consulting and teaching experience. David’s catchphrase is “Either you work Excel, or it works you,” so he focuses on what he sees users don’t, but should, know about Microsoft Excel. His goal is to empower you to use Excel more effectively. He's currently writing a book titled "Exploring Excel's Hidden Treasures". To learn more about David, you can view his LinkedIn profile and follow him on Facebook or Twitter (@excelwriter).
$76.00 / 2 CPE
NASBA Registered Sponsor
CPAs, EAs, CTECs Approved Credentials
QAS Quality Assured
Since 1996 Trusted Provider
CCH CPELink Chat - Support
By using our chat feature, you agree that your conversation may be recorded by Wolters Kluwer and agree to our Privacy Policy.