Copy a Variable-Size Data Range Between Excel Worksheets
Question details
Copy source data to another worksheet while allowing the destination range to update automatically when the number of source rows changes.
- Product
- Microsoft Excel worksheets
- Device & OS
- not provided
- Scenario
- The source data range can grow or shrink, and the user needs a linked destination worksheet rather than a fixed copied range.
- Observed behavior
- A fixed cell reference does not automatically include newly added rows or remove rows that no longer exist.
Identify the first row and columns that contain the source data, including whether the first row is a header row. If you use a formula-based method, ensure that the destination spill range is empty so the results can expand.
Convert the source data to an Excel Table and reference the whole table
Use this preferred method when the source is a single structured data list that may gain or lose rows.
Excel Tables expand automatically when rows are added directly below the table. A structured table reference on the destination worksheet can then return the current table contents without maintaining a fixed cell range.
On the source worksheet, select the complete data range and choose Insert > Table. Confirm the range and select My table has headers if the first row contains column names.
Select any cell in the new table, open the Table Design tab, and enter a name such as MyTable in the Table Name box.
Select the top-left destination cell and enter =MyTable[#All] to return the headers and all current table rows. Use =MyTable[#Data] if you want data rows without headers.
Remove values or formulas from cells where the table reference needs to expand. The formula result updates as rows are added to or removed from MyTable.
Use a dynamic array formula for a non-table source range
Use this option when the source cannot be converted to a table but has a known maximum range and a reliable first-column marker.
Create an Auto-Expanding Worksheet Reference in WPS Spreadsheet
WPS Spreadsheet can help you organize changing source data as a table and display its current contents on another worksheet. Use a structured table reference for maintained lists, or enter a dynamic array formula when the source must remain a regular range.
- 1. Open the source worksheet: Open the workbook in WPS Spreadsheet and select the data range that needs to update dynamically.
- 2. Format the range as a table: Choose Insert > Table, confirm the selected range, and enable headers when applicable.
- 3. Name the table: Click inside the table and use the Table Tools options to assign a recognizable name, such as MyTable.
- 4. Add the destination reference: Open the destination worksheet, select the first output cell, and enter =MyTable[#All] or =MyTable[#Data].
- 5. Allow results to expand: Keep the cells below and beside the formula empty so the returned table data can grow as the source changes.
Frequently Asked Questions
What is the best way to copy a range that changes size in Excel?
Convert the source list to an Excel Table through Insert > Table, then enter a structured reference such as =MyTable[#All] on the destination worksheet. The reference updates as the table changes.
What is the difference between MyTable[#All] and MyTable[#Data]?
=MyTable[#All] returns the table headers and data rows. =MyTable[#Data] returns only the data rows and excludes the header row.
Why does my dynamic array formula show a spill error?
A spill error usually means cells in the intended output area already contain data, formulas, or merged cells. Clear or unmerge the blocked destination cells and enter the formula again.
Can I combine variable-size data from several worksheets?
Yes. If each source is a table with matching columns, use a VSTACK formula to combine the table ranges, or use Power Query to append multiple worksheet tables into one refreshed output.
