Excel Power Query: Combine 180 Tables into One Spreadsheet
Question details
The user needs to consolidate 180 Excel tables with the same column structure into one worksheet or table.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Multiple tables or source files must be appended into a single dataset for analysis and possible future refreshes.
- Observed behavior
- The tables remain separate, and the user needs one combined spreadsheet or table.
Confirm that every source table uses matching column headers and compatible data types. If the sources are separate files, place them in one folder and remove unrelated files before starting the query.
Combine the tables with Excel Power Query
Use this maintainable method when the 180 tables may change or need regular refreshes and cleanup.
Power Query can import tables or files, append their rows, apply cleanup steps, and load the result into one worksheet. The saved query can be refreshed when the source data changes.
Place the source workbooks in one folder, or confirm that the tables have consistent headers and matching column types.
In Excel, select the Data tab, choose Get Data, and select the appropriate source such as From File > From Folder or From Table/Range.
In the Power Query window, choose Combine and Transform Data, review the detected columns, remove unnecessary fields, and apply any required filters or type changes.
Select Home > Close & Load or Close & Load To, choose Table, and load the appended data to a new worksheet.
When source data changes, select Data > Refresh All to rerun the query and update the consolidated worksheet.
Append ranges with VSTACK in Excel 365
Use this approach for a quick one-time combination when the ranges are known and the source layouts will not change frequently.
Use WPS Office to Review and Work with the Combined Spreadsheet
WPS Office is a free, lightweight Microsoft Office alternative for opening, reviewing, editing, and sharing the consolidated workbook after the data has been combined. Its familiar interface and strong Excel file compatibility make migration straightforward, although Microsoft Excel Power Query itself is required to create and refresh this specific query workflow.
- 1. Open the consolidated workbook: Launch WPS Spreadsheet and select Open to load the combined .xlsx workbook.
- 2. Review the imported table: Use the Data tab to sort or filter the consolidated table and check that the rows and headers imported correctly.
- 3. Save in Excel format: Select Menu > Save As, choose the .xlsx format, and save the reviewed workbook for continued sharing or editing.
Frequently Asked Questions
Is Power Query the best way to combine 180 Excel tables?
Yes. Power Query is generally the most maintainable option when tables share the same structure and the data may need regular refreshes, filtering, or cleanup.
Can Power Query combine tables stored in separate Excel files?
Yes. Put the workbooks in one folder, then select Data > Get Data > From File > From Folder. Choose Combine and Transform Data to append the matching files.
Can VSTACK replace Power Query for this task?
VSTACK can append ranges in supported Excel 365 versions, but a formula becomes difficult to maintain across 180 tables. Power Query is better for recurring imports and changing source files.
What happens if the source tables have different column headers?
Power Query may create separate columns for headers that do not match. Standardize the header names and data types before combining, or rename and transform the columns in Power Query.
Can WPS Office create and refresh this Excel Power Query?
WPS Office can open, edit, and review the resulting .xlsx workbook, but Microsoft Excel is required for the Power Query creation and refresh workflow described here.
