Combine Weekly Excel Worksheets into a Master Table
Question details
Combine records from weekly worksheets into one master table for ongoing data entry and reporting.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Weekly records are stored on separate worksheets, but the user wants a single source for weekly and monthly reports.
- Observed behavior
- The desired result is one master table containing all records, with reports generated from that table.
Check that each weekly worksheet uses the same column headings, such as Date, Name, Time, and Notes. Save the source workbook and identify any title rows or blank rows that should not be imported.
Combine existing weekly worksheets with Power Query
Use Power Query to append consistently structured worksheets into one table, especially when there are many weeks to consolidate.
In a separate Excel workbook, select Data > Get Data > From File > From Excel Workbook, then choose the saved workbook containing the weekly sheets.
In the Navigator, enable Select multiple items, select the weekly worksheets, and choose Transform Data.
In Power Query Editor, check each query's headings and remove extra title rows or blank rows using the Home tab. Use Use First Row as Headers where needed, and set appropriate data types for dates and times.
Select Home > Append Queries > Append Queries as New. Choose Three or more tables, add the weekly queries, and select OK.
Select Home > Close & Load to load the combined result into a worksheet table. Select the table and use Insert > PivotTable or the table filters to create reports.

Copy weekly records into a single master table
Use manual copying when there are only a few worksheets or when you want to start entering all future records in one place.
Consolidate weekly records in WPS Spreadsheets
Use WPS Spreadsheets to copy weekly records into one master sheet, format the data as a table, and filter or summarize it for reports.
- 1. Open the workbook: Open WPS Spreadsheets and choose Open to select the Excel workbook containing your weekly sheets.
- 2. Add a master sheet: Add a worksheet and enter the shared column headings in its first row.
- 3. Combine the records: Copy the data rows, excluding headers, from each weekly sheet and paste them below the existing records on the master sheet.
- 4. Organize and report: Select the combined range and use Insert > Table. Use the filter arrows or Insert > PivotTable to create a report, then save the workbook as XLSX.

Frequently Asked Questions
Do all weekly sheets need the same column headings?
Consistent headings make consolidation more reliable. Before appending queries in Power Query Editor, rename equivalent columns to match and check that dates and times use the appropriate data types.
Can I add new records directly to a Power Query result?
A refresh can overwrite changes made in the query output. Update the source sheets and select Data > Refresh All, or paste the results as values into a separate master table for direct data entry.
How can I report by week or month from the master table?
Select the master table and choose Insert > PivotTable. Add the Date field to the PivotTable and group dates as needed, or use the table's date filters to view a specific period.
Will Power Query automatically include a new weekly sheet?
Not necessarily. If you selected specific sheets in Navigator, you may need to add the new sheet as a query and include it in the append operation before selecting Data > Refresh All.




