Power Query Problems

How to Combine Multiple Excel Tables and Keep Data Updated

Nimra Malik Sep 30, 2026 868 views

Question details

The user wants to combine multiple Excel tables with matching column headers into a single master summary table and ensure the consolidated data remains updated when the source tables are modified.

How to Combine Multiple Excel Tables and Keep Data Updated

Product: Excel
Device & OS: not provided
Scenario: Consolidating data from over 50 source tables into one unified table for easier reporting or PivotTable creation.
Observed behavior: The user needs a dynamic setup where any additions, modifications, or deletions in the source data tables are automatically reflected in the summary table upon refreshing.

Before you start

Ensure all your source data ranges are formatted as Excel Tables and that their column headers match exactly in both spelling and capitalization to prevent data misalignment.

Solution 1Recommended

Use Power Query to Append Tables

The most efficient way to merge multiple tables dynamically in Excel is by appending them through the Power Query Editor.

Power Query allows you to connect multiple tables and append them into a single query. Once set up, this query acts as a dynamic link to your source data, meaning any updates made to the original tables can be instantly pulled into your master summary table with a simple refresh.

Format Source Data as Tables

Select each of your source data ranges and press Ctrl + T to format them as Excel Tables. Give each table a recognizable name in the Table Design tab.

Load Tables to Power Query

Go to the Data tab on the ribbon. Click 'From Table/Range' for each of your tables to load them into the Power Query Editor. Instead of loading them directly into the sheet, click 'Close & Load To...' and select 'Only Create Connection'.

Append the Queries

In Excel, navigate to Data > Get Data > Combine Queries > Append. This will open the Append window.

Select Tables to Combine

Choose the 'Three or more tables' option. Select all your source tables from the Available tables list, click 'Add' to move them to the 'Tables to append' box, and click OK.

Load the Summary Table

The appended data will appear in the Power Query Editor. Click the 'Close & Load To...' button on the Home tab, select 'Table' or 'PivotTable Report', and choose where you want to place the combined data in your workbook.

Refresh Data to Keep It Updated

Whenever you add, modify, or delete data in any of the original source tables, simply right-click anywhere inside the combined summary table and select 'Refresh'. The master table will instantly update to reflect all changes.

Case Sensitivity in Column Headers: Power Query is strictly case-sensitive. If one table has a header named 'Date' and another uses 'date', Power Query will create two separate columns. Verify all headers match perfectly before appending.

Free Microsoft Office alternative

Manage Your Spreadsheets Efficiently with WPS Office

While Power Query is a specific feature native to Microsoft Excel, WPS Office provides a free, lightweight alternative with highly compatible spreadsheet tools. It offers familiar interfaces, robust data consolidation features, and seamless handling of complex spreadsheets without the heavy system requirements or subscription fees.

1. Download and Install: Visit the official WPS Office website to download the free suite and install it on your computer.
2. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx files directly with complete format preservation.
3. Consolidate Data: Use the built-in 'Consolidate' feature under the Data tab to summarize, calculate, and merge data from multiple sheets efficiently.

Completely free and lightweight Office suiteSeamless compatibility with Microsoft Excel (.xlsx) formatsBuilt-in data consolidation and sheet merging toolsFamiliar user interface for quick migration with zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Can I combine tables from different Excel workbooks using Power Query?

Yes. You can combine tables from external files by going to the Data tab, selecting 'Get Data', then 'From File', and choosing 'From Workbook'. Once connected, you can append these external queries just like local tables within the same workbook.

Do my column headers need to match exactly to append tables?

Yes, Power Query is case-sensitive. Your column headers must match exactly in both spelling and capitalization. If they differ, Power Query will create separate columns for the mismatched headers and fill the empty spaces with null values.

How do I schedule my combined table to refresh automatically?

You can set up automatic refreshes by right-clicking your combined table, selecting 'Table', then clicking 'External Data Properties'. In the Connection Properties window, you can check the box for 'Refresh every X minutes' or 'Refresh data when opening the file'.

Excel & Data Problems