How to Keep an Excel Chart Linked to a Copied Table
Question details
The user wants a copied Excel chart to update based on a newly copied table rather than remaining linked to the original source table.
- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- Duplicating a chart and its source data to a new location within Excel.
- Observed behavior
- When selecting and copying a chart alongside its source table, the pasted chart continues to reference and update from the original table instead of the newly pasted one.
Ensure your current workbook is saved. If you plan to copy the data to an entirely different workbook, verify that both the source and destination workbooks are open before proceeding.
Copy the Entire Worksheet to Preserve Chart Links
The most reliable way to keep a copied chart linked to its new data table is to duplicate the entire worksheet rather than copying and pasting just the objects.
By default, Excel charts use absolute references. If you copy a chart and a table simultaneously, the new chart will point back to the original sheet's data range. Copying the entire worksheet maintains the relative relationship between the chart and the table within the new sheet.
Locate the worksheet tab at the bottom of the Excel window containing your chart and table, then right-click it.
Click 'Move or Copy...' from the context menu that appears.
In the 'To book' dropdown, select the current workbook or a different open workbook where you want the copied data to live.
Check the box next to 'Create a copy' at the bottom of the dialog box, then click 'OK'. The newly generated sheet will contain the table and a chart correctly linked to it.
Use Cut and Paste to Move Linked Charts
If your goal is to relocate the chart and table entirely without leaving a copy behind, using the Cut command prevents Excel from duplicating references to the old location.
Easily Duplicate Sheets and Charts with WPS Spreadsheet
WPS Spreadsheet provides a seamless way to copy worksheets while preserving all dynamic links between charts and source tables, ensuring your data visualization remains accurate without manual adjustments.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the document containing the chart and data you want to copy.
- 2. Access Sheet Options: Right-click the specific worksheet tab at the bottom of the screen.
- 3. Select Move or Copy: Click 'Move or Copy' from the popup menu.
- 4. Duplicate the Sheet: Tick the 'Create a copy' checkbox, select where you want to place the copied sheet, and click 'OK'.
Frequently Asked Questions
Why does my copied chart still update from the original Excel table?
When you copy and paste just the chart and table together, Excel retains an absolute cell reference to the original data range to prevent accidental data loss. It does not automatically assume you want to link to the adjacent copied data.
Can I manually change the data source of a copied chart?
Yes. Right-click the copied chart, select 'Select Data' from the menu, and in the 'Chart data range' box, manually highlight or type the cell range of your new, copied table to update the connection.
Does copying the entire sheet work when sending data to a completely new workbook?
Yes. When using the 'Move or Copy' function, you can select ' (new book) ' from the 'To book' dropdown menu. This creates a fresh workbook containing your duplicated sheet with all internal chart-to-table links preserved.
