Chart & Visualization Issues

Fix Excel Scatter Chart X Values Not Updating After Duplicating Sheet

Rana Garcia Sep 30, 2026 869 views

Question details

The user needs to make the X-axis values of a scatter chart update automatically to reference the newly copied worksheet instead of staying statically linked to the original template sheet.

Fix Scatter Chart X Values Not Updating After Duplicating an Excel Sheet

Product: Excel
Device & OS: not provided
Scenario: Duplicating an analysis sheet that contains a scatter chart with multiple data series driven by formulas.
Observed behavior: When the worksheet is copied, the chart's series names and Y values automatically adapt to the new sheet, but the X values stubbornly continue to reference the source columns of the original template.

Before you start

Verify that your newly duplicated worksheet contains the required data in its source columns and ensure that the columns containing your X-axis data are not hidden.

Solution 1Recommended

Manually Update the Chart Data Source References

Re-link the X values of each data series to the current worksheet using the Select Data Source dialog.

When copying worksheets, Excel sometimes retains absolute sheet references for scatter chart X values, especially if the data ranges involve complex formulas or specific text formats.

To resolve this, you must explicitly tell the chart to look at the new worksheet for its X-axis data.

Open the Select Data Dialog

Right-click the scatter chart on the duplicated worksheet and choose 'Select Data' from the context menu.

Edit the Data Series

In the 'Legend Entries (Series)' box on the left, click on the first data series you wish to fix, then click the 'Edit' button.

Update the Series X Values

In the Edit Series dialog, locate the 'Series X values' field. Change the worksheet name in the formula (e.g., from =TemplateSheet!$A$2:$A$10 to =NewSheetName!$A$2:$A$10). Keep the cell ranges the same.

Apply and Repeat

Click OK. Repeat this editing process for every data series listed in the chart, then click OK on the main Select Data Source dialog to apply the changes.

Manually Update the Chart Data Source References

Quick Reference Check: You can also click on a data point in the chart and directly edit the sheet name in the Excel Formula Bar, which displays the SERIES() function.

Solution 2

Unhide Columns and Verify Formula Outputs

Ensure that the X values on the duplicated sheet are visible and calculating correctly so the chart can read them.

Free Spreadsheet Software

Manage Complex Scatter Charts Easily with WPS Spreadsheet

WPS Spreadsheet provides a highly intuitive charting engine that simplifies data reference management. If you frequently duplicate analysis templates with heavy scatter plots, WPS Spreadsheet handles dynamic references smoothly and provides an easy-to-use data selection interface.

1. Open Your Workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the duplicated worksheet.
2. Access Data Selection: Right-click the problematic scatter chart and select 'Select Data' from the popup menu.
3. Modify the X Values: Click 'Edit' on the targeted data series, adjust the Series X values to point to the current sheet, and hit OK.

Intuitive chart data management when duplicating complex template sheets.100% compatible with Microsoft Excel (.xlsx) formats and advanced charting formulas.Lightweight and fast, preventing slowdowns when editing charts with numerous data series.Free to use with a familiar interface that requires zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why do the Y values update automatically but the X values do not when copying an Excel sheet?

When creating scatter charts, Excel frequently treats X-axis assignments differently than Y-axis or Category values. If the X values reference an array or involve complex formulas, Excel may hardcode the original sheet's reference into the SERIES formula to preserve data integrity, requiring manual updates upon duplication.

How can I tell which sheet my scatter chart is currently referencing?

Click on any plotted data series in your scatter chart and look at the Formula Bar at the top of the screen. You will see a formula starting with =SERIES(). The arguments within this function contain the exact sheet names and cell ranges your chart is pulling data from.

Can I use Find and Replace to update chart data references?

The standard Find and Replace tool (Ctrl+H) does not search inside chart objects or their SERIES functions. However, you can write a short VBA macro to loop through chart series and replace the old sheet name with the new sheet name within the formula string.

Excel & Data Problems