Chart & Visualization Issues

How to Copy Excel Charts While Updating Only Selected Series

Camila Milosovich Sep 30, 2026 869 views

Question details

The user needs to copy an existing chart to new data blocks so that specific data series (X and Y ranges) update to the new location, while reference data series (Columns AA and AB) remain completely fixed.

How to Copy Excel Charts While Updating Only Selected Series

Product: Spreadsheet
Device & OS: not provided
Scenario: Copying and pasting charts across a worksheet containing repeated data blocks where dynamic updating is required for main data, but absolute referencing is needed for constant reference lines.
Observed behavior: When the chart is copied, all data references either remain fixed to the original location, or removing absolute references fails to automatically update the chart as expected.

Before you start

Ensure your repeated data blocks are spaced uniformly and identically structured, as consistent spacing makes it much easier to implement dynamic named ranges.

Solution 1Recommended

Use Dynamic Named Ranges for Chart Series

This method uses Excel's Name Manager to create dynamic formulas that act as the chart's data source, bypassing the default behavior of charts reverting to absolute references.

By default, Excel charts hardcode data references as absolute (e.g., $A$1). To force a chart to update relatively when copied, you must link its series to a Dynamic Named Range using the OFFSET or INDEX function.

Create Named Ranges for Relative Data

Navigate to Formulas > Name Manager and click New. Create a named range (e.g., 'DynamicX') using the OFFSET function based on the active cell to ensure the range moves when the chart is copied.

Create Named Ranges for Fixed Data

In the Name Manager, create another named range (e.g., 'FixedRef') that points strictly to your absolute data using dollar signs, such as =$AA$1:$AA$20.

Apply Named Ranges to the Chart

Right-click your original chart and choose Select Data. Edit each series and replace the cell range with your newly created named ranges in the format 'WorkbookName.xlsx!DynamicX'.

Copy and Paste the Chart

Copy the chart to the new data block. The series linked to the relative named range will update based on its new position, while the series linked to the absolute range will remain pointing to columns AA and AB.

Use Dynamic Named Ranges for Chart Series

Workbook Reference Required: When entering named ranges into the Chart Data series formula, you must prefix the named range with the workbook or worksheet name, otherwise Excel will reject the formula.

Solution 2

Update Chart Data Ranges Manually via Drag and Drop

If you are unfamiliar with complex named ranges or VBA, the fastest alternative is to manually drag the data series boxes after pasting the chart.

Easily Manage Chart Data Series in WPS Spreadsheet

WPS Spreadsheet provides a highly intuitive Name Manager and Chart Data selection interface, making it simple to configure mixed references and dynamic named ranges. You can seamlessly apply advanced charting techniques to visualize repeated data blocks without manual updates.

1. Define Data Ranges: Open your workbook in WPS Spreadsheet, go to the Formulas tab, and select Name Manager to create your relative and absolute data ranges.
2. Bind Series to Chart: Insert your chart, right-click to choose Select Data, and replace standard cell references with your custom named ranges.
3. Duplicate Instantly: Copy and paste the chart across your spreadsheet. The ranges will automatically follow your predefined dynamic logic.

Fully compatible with Microsoft Excel (.xlsx) files and chart objectsIntuitive Name Manager for easily setting up OFFSET and INDEX dynamic rangesFree and lightweight alternative for complex data visualization and data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does a copied chart always link back to the original data?

By design, Excel charts use absolute cell references (e.g., $A$1:$B$10) for their data sources to prevent the chart from breaking when moved. Even if you manually remove the dollar signs in the formula bar, the software often automatically reverts them back to absolute references to lock the data scope.

Can I use VBA to automatically update copied charts?

Yes. A VBA macro can be written to loop through the SeriesCollection of the currently selected chart. The script can parse the series formula strings, increment the row numbers for the relative series based on a user prompt or offset variable, and leave the fixed series formulas untouched.

Do Helper Columns work for this scenario?

Helper columns can simplify the charting process. You can set up a fixed 'Chart Data' area that acts as the source for your single chart. You can then use Data Validation drop-downs or INDEX/MATCH formulas to pull different blocks of data into this dedicated area, eliminating the need to copy the chart itself.

Excel & Data Problems