Formula Errors

How to Copy an Excel Table and Automatically Update Cell References

Elise Williams Oct 20, 2026 869 views

Question details

The user wants to duplicate an existing table in a worksheet and needs the formulas within the copied table to automatically adjust their cell references to the new location.

How to Copy an Excel Table and Automatically Update Cell References

Product: Excel
Device & OS: not provided
Scenario: Analyzing multiple data sets with a consistent table layout on the same worksheet, requiring duplicated tables to calculate independently based on their new positions.
Observed behavior: Formulas need to shift dynamically to match the newly pasted destination instead of remaining locked to the original table's source data.

Before you start

Verify that the formulas in your original table use relative references (like A1) instead of absolute references (like $A$1) before copying, as absolute references will not shift when pasted.

Solution 1Recommended

Use Standard Copy and Paste with Relative References

By ensuring your formulas use relative references, Excel will automatically shift the cell coordinates to match the new location when you paste the table.

Excel handles cell references in two primary ways: relative and absolute. Relative references shift automatically based on where you paste them, while absolute references (indicated by dollar signs, e.g., $B$2) remain fixed to a specific cell. For a duplicated table to calculate correctly in a new location, its internal formulas must rely on relative references.

Inspect the original formulas

Double-click a cell containing a formula in your original table. Check the formula bar to ensure the references do not have dollar signs (use '=B2*C2' instead of '=$B$2*$C$2'). Remove any dollar signs if you want those references to update upon pasting.

Select and copy the source table

Click and drag to highlight the entire table you wish to duplicate. Press Ctrl+C (Windows) or Cmd+C (Mac) to copy the selection to your clipboard.

Paste the table to the new location

Click on the top-left cell of the destination area where the new table should begin. Press Ctrl+V (Windows) or Cmd+V (Mac) to paste the table.

Verify the updated references

Click on any formula cell in the newly pasted table and look at the formula bar. Confirm that Excel has automatically shifted the row and column coordinates to match the new table's position.

Use Standard Copy and Paste with Relative References

Mixed References: If part of your formula needs to refer to a fixed constant outside the table (like a global tax rate), keep that specific cell as an absolute reference (e.g., $E$1) while leaving the internal table references relative.

Efficient Spreadsheet Tool

Easily Duplicate Tables and Manage Formulas in WPS Office

WPS Spreadsheet fully supports relative, absolute, and mixed cell references, making it incredibly simple to duplicate complex tables, audit formulas, and analyze multiple datasets without reference errors.

1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheet and open the file containing the data table you want to duplicate.
2. Check your reference types: Select your formula cells and press F4 to easily toggle references to relative (e.g., A1) so they can adjust automatically.
3. Copy and paste seamlessly: Highlight the table, press Ctrl+C, click your new destination cell, and press Ctrl+V. Your formulas will update instantly based on the new location.

Automatically updates relative cell references when copying and pasting tables100% compatible with Microsoft Excel (.xlsx) formats and standard formulasIntuitive formula auditing tools to visually track cell dependenciesLightweight, fast, and free alternative for comprehensive data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why are my copied formulas displaying the exact same results as the original table?

This happens if your original formulas use absolute references, which contain dollar signs (like $A$1). Absolute references lock the formula to the original cells. To fix this, remove the dollar signs to convert them to relative references before copying the table.

How do I quickly change absolute references to relative references?

You can quickly toggle between absolute, mixed, and relative references by selecting the cell reference in the formula bar and pressing the F4 key on your keyboard until the dollar signs disappear.

Will structured references in formatted Excel Tables update when copied?

Standard structured references (like [@Column1]) behave like relative references for the current row within the table. If you copy the entire table structure to a new area, the references will adapt correctly to the newly created table.

How can I copy a table's exact formatting along with the updating formulas?

When pasting the copied table, right-click the destination cell and select 'Keep Source Formatting' from the Paste Options menu. This ensures all borders, background colors, and number formats are preserved alongside your relative formulas.

Excel & Data Problems