Power Query Problems

How to Combine Two Excel Tables Without Losing Formatting

Algirdas Jasaitis Oct 20, 2026 868 views

Question details

The user needs to combine two identically structured Excel tables without losing their table formatting and expansion capabilities.

How to Combine Two Excel Tables Without Losing Formatting

Product: Microsoft Excel
Device & OS: not provided
Scenario: Copying and pasting data between two tables with identical structures, commonly those generated via Power Query.
Observed behavior: The destination table fails to automatically expand to include the newly pasted data, often due to selecting cells outside the source table's main data body.

Before you start

Verify that both Excel tables have perfectly matching column headers and identically structured data types before attempting a merge.

Solution 1Recommended

Combine Tables Using Power Query Append

The most reliable method to combine identically structured tables without losing formatting is by using Power Query's Append feature.

Power Query handles data dynamically, meaning that appending queries generates a clean, combined result table without manually copying and pasting, preserving all table properties.

Load Tables to Power Query

Select any cell inside your first table, go to the 'Data' tab, and click 'From Table/Range'. Repeat this step for your second table to load both queries into the Power Query Editor.

Append the Queries

In the Power Query Editor, navigate to the 'Home' tab and click 'Append Queries'. Choose to append as a new query or onto an existing one.

Select Tables to Combine

In the dialog box, select 'Two tables', specify your first query as the Primary table, and choose the second query to append. Click 'OK'.

Output the Combined Table

Click 'Close & Load' on the Home tab. Excel will create a new worksheet containing the correctly formatted, fully combined table.

Dynamic Updates: If you add new rows to either original table in the future, you can simply click 'Refresh All' on the Data tab to update the combined Power Query table.

Solution 2

Manually Append Data by Copying Only the Data Body

If you prefer not to use Power Query, you can manually copy and paste the data, provided you strictly select only the data rows.

Solution 3

Convert Tables to Ranges and Reformat

If automatic table expansion fails entirely, a fallback method is to convert both tables to normal ranges, stack the data, and format them back into a single table.

Combine tables seamlessly

Merge Excel Tables Easily with WPS Office

WPS Spreadsheet offers powerful, intuitive data management tools that ensure you can combine, merge, and format large datasets seamlessly. Built with high compatibility in mind, it handles identical table structures effortlessly.

1. Open Your Workbooks: Launch WPS Spreadsheet and open the file containing the tables you want to combine.
2. Copy the Target Data: Select the specific data body rows from the second table, omitting any headers or blank spaces, and press Ctrl+C.
3. Paste and Expand Automatically: Select the cell just below your primary table and paste the data. WPS will automatically extend the table formatting.
4. Utilize Data Tools: Alternatively, navigate to the 'Data' tab to use WPS's robust Consolidation tools for more complex merging scenarios.

Free and lightweight alternative to Microsoft OfficeFully compatible with Microsoft Excel formats (.xlsx, .xls)Smart table expansion seamlessly integrates appended data rowsBuilt-in data consolidation tools for quick dataset merging

microsoft office alternative - wps office

Frequently Asked Questions

Why didn't my Excel table expand when I pasted new data below it?

This commonly occurs if you accidentally copied cells outside the original table's data body, such as blank rows or headers. It can also happen if the 'Include new rows and columns in table' option is disabled in your Excel AutoCorrect settings.

Can I use VLOOKUP to combine identically structured tables?

No. VLOOKUP is designed for merging columns of data based on matching key values. To stack or append rows of identically structured tables, you should use Power Query or manually copy and paste the rows.

What happens in Power Query if the two tables have slightly different column headers?

Power Query is case-sensitive. If column headers do not match exactly, Power Query will create separate columns for the mismatched headers and fill the corresponding empty spaces with 'null' values for those rows.

Excel & Data Problems