Data Import & Export

Combine Weekly Excel Worksheets into a Master Table

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 868 views

Question details

Combine records from weekly worksheets into one master table for ongoing data entry and reporting.

How to Combine Weekly Excel Worksheets into a Master Table
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.
Before you start

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.

Solution 1Recommended

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.

1
Connect to the source workbook

In a separate Excel workbook, select Data > Get Data > From File > From Excel Workbook, then choose the saved workbook containing the weekly sheets.

2
Select the weekly sheets

In the Navigator, enable Select multiple items, select the weekly worksheets, and choose Transform Data.

3
Prepare the columns

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.

4
Append the queries

Select Home > Append Queries > Append Queries as New. Choose Three or more tables, add the weekly queries, and select OK.

5
Load and report

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.

Combine existing weekly worksheets with Power Query
Choose how to maintain the master: To keep the query refreshable, update its source sheets and use Data > Refresh All; do not type new records into the query output. To maintain one master table instead, copy the loaded results and use Paste Special > Values in a separate range, format it as a table, and add future records there.
Work with a master table in WPS

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. 1. Open the workbook: Open WPS Spreadsheets and choose Open to select the Excel workbook containing your weekly sheets.
  2. 2. Add a master sheet: Add a worksheet and enter the shared column headings in its first row.
  3. 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. 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.
Keep weekly records together in one master tableFilter dates and create PivotTables for reportsOpen and save Microsoft Excel XLSX workbooksUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.