Chart & Visualization Issues

How to Copy Excel Data and Charts Without Breaking References

Adam Davis Oct 21, 2026 868 views

Question details

The user wants to copy an Excel worksheet range containing data and charts without the new charts remaining absolutely linked to the original data source.

How to Copy Excel Data and Charts Without Breaking References

Product: Excel
Device & OS: not provided
Scenario: Duplicating a dataset and its associated visual charts within a workbook for reuse, comparison, or modification.
Observed behavior: When copying and pasting a range normally, chart data references remain absolute, causing the copied charts to still update from or refer to the original dataset instead of the new one.

Before you start

Identify the exact data ranges and corresponding charts you need to duplicate, and ensure your worksheet does not contain hidden rows or columns that might complicate a full-sheet copy.

Solution 1Recommended

Duplicate the Entire Worksheet and Move the Content

The most reliable method to preserve relative references between data and charts is to copy the entire worksheet first, then cut the resulting isolated items back to your target sheet.

When you copy a chart by itself or alongside a small data range, Excel maintains the absolute cell references to the original sheet. By duplicating the entire worksheet, Excel automatically shifts the chart's data source to reference the newly copied sheet, keeping the data and chart linked together locally.

Copy the entire worksheet

Right-click the worksheet tab at the bottom of the screen, select 'Move or Copy', check the 'Create a copy' box, and click 'OK' to generate a duplicate sheet.

Update data on the duplicate

Modify the data on the newly copied sheet as needed. Because the charts on this copied sheet reference local data, they will automatically update to reflect your new values.

Cut and paste to the original sheet

Select the updated data range and the newly updated charts on the copied sheet. Use Ctrl+X to cut them, navigate back to your original worksheet (or your desired target sheet), and press Ctrl+V to paste them.

Delete the temporary sheet

Right-click the tab of the duplicated temporary worksheet and select 'Delete' to keep your workbook clean and organized.

Duplicate the Entire Worksheet and Move the Content

References Safely Preserved: Cutting and pasting the elements from the duplicated sheet moves them without reverting their references back to the old data, permanently solving the absolute link issue.

Efficient Spreadsheet Management

Copy and Manage Charts Easily with WPS Spreadsheet

WPS Office provides a highly compatible spreadsheet tool that makes copying, duplicating, and managing complex chart references completely straightforward. You can easily duplicate sheets to preserve chart links without any hassle.

1. Open the workbook: Launch WPS Spreadsheet and open your existing file containing the data and charts.
2. Duplicate the worksheet: Hold down the Ctrl key, then click and drag the sheet tab to the right to instantly create a full duplicate.
3. Edit the new data: Type your new values into the duplicated sheet. The charts will adjust automatically based on the local sheet references.
4. Cut and paste to target: Highlight the modified data and charts, right-click to select 'Cut', and 'Paste' them into your final preferred worksheet.

Seamlessly duplicate entire worksheets with dragging shortcutsFully compatible with Microsoft Excel (.xlsx) formats and chart typesIntuitive right-click menus for fast data source modificationFree to use with comprehensive data visualization capabilities

microsoft office alternative - wps office

Frequently Asked Questions

Why do my copied charts still change when I edit the old data?

When you copy just a chart and its data range, the spreadsheet software often keeps the chart's data source locked to the original absolute range (e.g., Sheet1!$A$1:$B$5). This means any changes to the original data will still affect the copied chart.

Can I manually change the chart reference without copying the whole sheet?

Yes. You can paste the chart, right-click it, and select 'Select Data'. In the data source dialog, manually edit the 'Chart data range' field to point to your newly pasted data instead of the original source.

Is there a fast shortcut to duplicate an entire worksheet?

Yes. Instead of using the right-click menu, you can simply hold down the Ctrl key on your keyboard, click the worksheet tab at the bottom, and drag it to the left or right. Release the mouse button to instantly create a duplicate.

Excel & Data Problems