Data Import & Export

How to Copy Data to the Bottom of an Excel Table Without Resizing

Aamir Naveed Akram Oct 22, 2026 868 views

Question details

The user needs to append data from one worksheet to the bottom of an existing table on another worksheet without changing the destination table's dimensions, especially when source and destination sizes differ.

How to Copy Data to the Bottom of an Excel Table Without Resizing

Product: Excel
Device & OS: not provided
Scenario: Appending records from a source worksheet to an existing destination table with different layouts.
Observed behavior: Standard copy-and-paste operations often overwrite destination formats, inadvertently resizing column widths or row heights to match the source data.

Before you start

Verify that your destination area is formatted as an official Table (using Ctrl+T) and ensure you have a clear understanding of the matching columns before appending.

Solution 1Recommended

Use Power Query to Append Records (Recommended)

Power Query is the most robust method for appending data from different sources into a single table without altering the original structure or formatting.

When source and destination tables have different sizes or column layouts, manual pasting can disrupt your spreadsheet. Power Query creates a secure connection to merge these tables dynamically based on column headers, ensuring your destination table dimensions stay completely intact.

Load tables into Power Query

Select your source table, navigate to the 'Data' tab, and click 'From Table/Range'. Repeat this step for your destination table.

Append the queries

In the Power Query Editor, go to 'Home' > 'Append Queries' > 'Append Queries as New'. Select your primary table and the table containing the new data.

Close and Load

Once the data is combined correctly, click 'Close & Load To...' and select 'Table' to output the cleanly appended data into a new worksheet without affecting manual cell dimensions.

Use Power Query to Append Records (Recommended)

Automated Updates: Next time you add data to the source table, simply right-click the Power Query result and select 'Refresh' to update it instantly.

Solution 2

Use Paste Special to Preserve Dimensions

Bypass automatic resizing by pasting only the raw values instead of the entire cell object.

Solution 3

Use Lookup Formulas for Selective Appending

Use an IFERROR and HLOOKUP combination to pull specific data into the table bottom automatically.

Efficient Data Management

Easily Append Data with WPS Spreadsheet

WPS Office provides a highly compatible and lightweight spreadsheet application to handle complex data manipulation effortlessly. Whether you prefer to use formulas, Paste Special, or manage extensive datasets, WPS Spreadsheet processes it efficiently.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your source and destination tables.
2. Copy the source data: Select the rows you want to append and press Ctrl+C.
3. Use Paste Special: Right-click the blank row at the bottom of your destination table, choose 'Paste Special', and select 'Values' to append perfectly without resizing.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsAdvanced lookup function support (VLOOKUP, HLOOKUP, XLOOKUP)Smart Paste Special options to easily retain table formattingFree, lightweight, and user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my table expand automatically when I paste data below it?

Excel tables have an Auto-Expand feature enabled by default. When you paste data adjacent to the bottom or right edge of a formatted table, it assumes the data is part of the table and automatically incorporates it. If you wish to disable this, go to File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type, and uncheck 'Include new rows and columns in table'.

How do I stop column widths from changing when pasting from another sheet?

Standard pasting (Ctrl+V) copies everything, including source column widths and cell formatting. To prevent this, always right-click your destination cell and choose Paste Special > Values. This injects only the text/numbers, adopting the destination's pre-existing dimensions and design.

What is the benefit of using Power Query over simple copy-pasting?

Power Query automates the append process. While copy-pasting is manual and prone to human error (like accidentally overwriting formats or misaligning columns), Power Query links the tables dynamically. If your source data updates regularly, you only need to click 'Refresh' to update the appended destination table.

Excel & Data Problems