Formula Errors

How to Link Tables and Automatically Copy Data Between Excel Worksheets

Maira Mehtab Sep 21, 2026 873 views

Question details

The user needs to automatically sync daily updated data from a specific range in one worksheet to a designated destination range in another worksheet.

Product: Excel
Device & OS: not provided
Scenario: A source table on Sheet1 (A1:G1000) is updated daily, and this data needs to appear automatically in a destination area on Sheet2 (starting at B7).
Observed behavior: The user is seeking a formula-based method to mirror a large dataset across different worksheet tables without manual copying and pasting.

Before you start

Verify that your version of Excel supports dynamic array formulas, and ensure the destination range on the second sheet is completely empty to prevent #SPILL! errors.

Solution 1Recommended

Use a Dynamic Array Formula to Mirror Data

Use a direct cell reference formula to automatically spill the data from the source sheet to the destination sheet.

If your spreadsheet software supports dynamic arrays, you can use a single cell formula to pull an entire block of data from one worksheet to another. This ensures that any daily updates made in the source table are instantly and automatically reflected in the destination area.

Select the destination cell

Navigate to Sheet2 and click on cell B7, which will serve as the top-left starting point for your destination table.

Enter the array formula

Type =Sheet1!$A$1:$G$1000 into the formula bar and press Enter on your keyboard.

Verify the data spill

The referenced data from Sheet1 will automatically spill downwards and rightwards into the destination cells. Ensure no existing data, spaces, or formatting blocks this spill area.

Structured Tables Limitation: Linking an unused range solely with formulas can be challenging when both destinations are officially formatted as structured Excel tables (via Insert > Table). Structured tables do not natively support dynamic spill formulas; this method works best when the destination is a normal cell range.

Efficient Data Syncing

Easily Link and Sync Data Across Sheets in WPS Spreadsheet

WPS Office Spreadsheet provides full support for cross-sheet references and dynamic arrays, allowing you to seamlessly link data between worksheets without complicated setups.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your source and destination worksheets.
2. Start the reference: In your destination sheet (e.g., Sheet2), click the starting cell where you want the data to appear and type an equals sign (=).
3. Select the source range: Switch to your source sheet (e.g., Sheet1), click and drag to highlight the original data range (like A1:G1000), and press Enter to complete the link.

Seamlessly link and auto-update data across multiple worksheets and workbooks.Fully compatible with Microsoft Excel (.xlsx, .xls) files and standard array formulas.Free to use with a lightweight installation and rapid performance.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #SPILL! error when linking my tables?

A #SPILL! error occurs when the destination range has existing data, text, or even hidden spaces that block the formula from expanding. Clear all cells in the expected destination range to resolve this issue.

Will my linked data automatically update if the source table expands beyond row 1000?

If you use a fixed reference like =Sheet1!$A$1:$G$1000, new rows added past row 1000 will not be included. To capture future rows, either use full column references (like =Sheet1!A:G) or format the source data as a structured Table and reference its name.

Can I edit individual cells in the destination table after linking?

No. When using a dynamic array formula to mirror data, you cannot edit individual cells within the spilled destination range. Any manual edits in the destination area will break the array and trigger a #SPILL! error. All edits must be made in the original source table.

Excel & Data Problems