Excel 365: Consolidate Data for Analysis

Course description

This course demonstrates how to combine and summarize information across multiple Excel worksheets using both 3D formulas and the Consolidate feature. You’ll begin by learning how to create 3D references—formulas that pull values from the same cell location across a range of worksheets. By working with a workbook containing identical monthly sheets for budget versus actual data, you’ll see how to sum values from all months at once without having to write individual formulas for each sheet. The lesson explains the structure required for 3D formulas, how to construct the reference syntax, and how to copy and extend the results across summary tables. You’ll also discover best practices for maintaining workbook integrity, such as protecting the workbook to prevent sheet reordering, which can alter 3D reference results. After exploring this efficient approach for identically structured data, the course moves on to situations where worksheet layouts vary slightly. You’ll learn how to consolidate data from multiple ranges using Excel’s Consolidate feature. The process covers selecting source ranges, choosing functions like SUM or AVERAGE, and determining whether to use labels from the top row or left column. You’ll also see how to include links to source data, giving you drill-down access to the original details while keeping totals up to date. Practical tips are shared for avoiding common errors, ensuring sufficient space for all labels, and understanding how linked data behaves when updates are made. The lesson concludes by demonstrating how to expand or collapse grouped source data, ignore unnecessary formula protection warnings, and manage consolidated results efficiently. By the end, you’ll know how to aggregate data from both structured and unstructured worksheets, whether the ranges come from within the same workbook or from external workbooks, giving you flexible tools to summarize and analyze information across multiple data sources.

Course outline