How to Combine Data from Multiple Excel Sheets into a Trial Balance
Question details
The user needs an efficient method to consolidate planning data from 11 separate worksheets into a single trial balance without relying on thousands of resource-heavy VLOOKUP formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating financial or planning data spread across multiple worksheets into a unified trial balance report.
- Observed behavior
- The goal is to efficiently stack and summarize data using modern formulas like VSTACK or data tools like Power Query, while ensuring subsequent PivotTables refresh correctly when source numbers change.
Ensure your data across all 11 worksheets has identical column structures and headers. If you plan to use Power Query, converting your data ranges into dynamic Tables (Ctrl+T) is highly recommended.
Use Power Query and PivotTables to Consolidate Sheets
Best for large or recurring workbooks. Power Query dynamically combines tables and feeds the result into a PivotTable for easy summarization.
Power Query provides a robust way to append multiple tables into one. When using this method alongside PivotTables, you must refresh the data sequentially to ensure all updates flow through to the final report.
Select the data ranges in all 11 source sheets and press Ctrl+T to convert them into dynamic Excel Tables. Name them appropriately.
Go to the Data tab, select 'Get Data', and use Power Query to append the 11 tables into a single combined query.
Load the combined query result to a new worksheet as a Table (often referred to as the combined or 'green' table).
Select the combined result table, go to the Insert tab, and insert a PivotTable to generate and summarize your Trial Balance.
When source data changes, right-click any cell in the combined Power Query table and select 'Refresh'. Then, right-click inside the PivotTable and select 'Refresh' to update the final numbers.
Combine Sheets Using the VSTACK Formula
Ideal for Microsoft 365 users who want a direct formula-based approach without using Power Query.
Consolidate Multiple Worksheets Easily in WPS Spreadsheet
WPS Office Spreadsheet provides powerful data consolidation tools and full compatibility with advanced Excel formulas. You can seamlessly combine multiple sheets and summarize financial reports without heavy processing lags.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your 11 planning data sheets.
- 2. Access Data Consolidate: Create a new blank sheet for your Trial Balance. Go to the 'Data' tab and click on the 'Consolidate' button.
- 3. Add data ranges: Select the function you want to use (e.g., Sum) and individually add the data ranges from your 11 source sheets into the 'All references' box by clicking 'Add'.
- 4. Generate the Trial Balance: Check 'Create links to source data' if you want the trial balance to update automatically when source sheets change, then click 'OK'.
Frequently Asked Questions
Why doesn't my PivotTable update after I refresh Power Query?
Power Query and PivotTables refresh independently. You must first refresh the Power Query table to pull in the new source data, and then right-click your PivotTable and select 'Refresh' again to reflect those updated numbers.
Can I use VSTACK in older versions of Excel?
No, the VSTACK function is only available in Microsoft 365 and newer cloud-based versions. For older versions, Power Query or the built-in Data Consolidate feature are the best alternatives to avoid thousands of VLOOKUP formulas.
Is there a limit to how many sheets I can combine?
While standard 3D formulas support up to 255 concurrent sheet references, Power Query can theoretically combine an unlimited number of tables, restricted only by your computer's memory and performance.
