Microsoft KB Archive/71314

Adding a New Data Series to an XY (Scatter) Chart in Excel PSS ID Number: Q71314 Article last modified on 02-09-1993 PSS database name: W_eXceL

2.x 3.00 4.00 | 2.20 2.21 3.00

WINDOWS | OS/2

The information in this article applies to:

- Microsoft Excel for Windows versions 2.x, 3.0 and 4.0

- Microsoft Excel for OS/2 versions 2.2, 2.21, and 3.0

Summary:

After an XY (scatter) chart has been created in Excel 2.x or in Excel 3.x by choosing Copy from the Edit menu, choosing New from the File menu and selecting Chart, then choosing Paste Special from the Edit menu, an extra Y series cannot be added to the chart using the same copy and paste procedures.

If you use the Paste command from the Edit menu to paste the new data into the chart after copying the data from the spreadsheet, it will appear in the chart as a new series based on default X values (1,2,3,4,etc…) instead of your chosen X values. If you try to add a new Y series using the Paste Special command from the Edit menu, nothing will appear on the chart.

More Information:

There are two workarounds for this problem.

Workaround One

Copy and paste the new Y series into the chart. The chart will appear incorrect because Excel will be using default X values instead of the chart’s current category-axis values.
To change the X values to the correct category-axis values, select the new series on the chart. The series formula will then appear in the formula bar similar to the following:

=SERIES(Sheet1!$D<math display="inline">1,,Sheet1!</math>D<math display="inline">2:</math>D$6,3)

-or-

=SERIES(,,Sheet1!$D<math display="inline">2:</math>D$6,3)

Place the cursor between the commas in either statement to insert the category-axis values. Highlight the X values on your spreadsheet. Their cell addresses will now appear in the SERIES formula. Press ENTER and the new series will reformat to the correct category-axis values.

Workaround Two

Use this workaround only when the XY data in the spreadsheet is contiguous.

With the current XY chart active, choose Set Preferred from the Gallery menu. This will make the current chart format the default chart format.
Highlight the entire range of data to be charted and choose Copy from the Edit menu. From the File menu choose New and select Chart. Choose Paste Special from the Edit menu, and select Categories in First Column.

The chart will then appear with the previous formatting intact and the new data series added.

Additional reference words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 3.00 4.0 4.00