XL: Adding Series to XY (Scatter) Chart with Second X Axis

ID: Q115073

The information in this article applies to:

Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows

SUMMARY

When you add a second series to an xy (scatter) chart, the new values will be added with respect to the same x-axis values as the first series. This article illustrates how to add a second series to an xy scatter chart that utilizes a second set of x values.

MORE INFORMATION

To add a second series to an xy (scatter) chart using a different set of x values, use the appropriate method for your version of Excel.

Microsoft Excel 97

To use this method, follow these steps:

Enter the following data into a worksheet:


      A1: 1     B1: 5     C1: -1     D1: -5
      A2: 2     B2: 6     C2: -2     D2: -6
      A3: 3     B3: 4     C3: -3     D3: -8
      A4: 4     B4: 2     C4: -4     D4: -5
      A5: 5     B5: 5     C5: -5     D5: -1

Select the range A1:B5. On the Standard toolbar, click the Chart Wizard tool.

In the list of chart types, click XY (Scatter). Click one of the line charts under Chart Sub-type. Click Next.

Click the Series tab. Then, click Add. In the X Values box, type =Sheet1!$C$1:$C$5; in the Y Values box, type =Sheet1!$D$1:$D$5. Then, click Finish.

In the chart, double-click the second series to display the Format Data Series dialog box. Click the Axis tab. Click Secondary Axis and click OK.

Microsoft Excel Versions 5.0 and 7.0

Use any of the following three methods.

Method 1

To use this method, follow these steps:

Enter the following data into a worksheet:


      A1: 1     B1: 5     C1: -1     D1: -5
      A2: 2     B2: 6     C2: -2     D2: -6
      A3: 3     B3: 4     C3: -3     D3: -8
      A4: 4     B4: 2     C4: -4     D4: -5
      A5: 5     B5: 5     C5: -5     D5: -1

Select the range A1:B5, and in the ChartWizard, select the following options:
1. In step 1 of 4, click Next.
2. In Step 2 of 4, click XY (Scatter), and then click Next.
3. In Step 3 of 4, select type 6, and then click Next.
4. In step 4 of 4, click Data Series In Columns: use first 1 column(s) for X Data and first 0 row(s) for Legend Text. Click Finish.

Select the range C1:D5 and click Copy on the Edit menu.

Double-click the chart created in step 2.

On the Edit menu, click Paste-Special, and select the following options:
Add Cells As: New Series
Values Y in: Columns
Categories (X values) in First Column

Method 2

To use this method, follow these steps:

Using the data from Method 1, select cells A1:B5,D1:D5 and create a chart using the ChartWizard. (Note the range is a discontiguous selection which may be selected by holding down CTRL and selecting the different range of cells.) In the ChartWizard, follow these steps:
1. In Step 1 of 4, click Next.
2. In Step 2 of 4, click XY(Scatter), and then click Next.
3. In Step 3 of 4, select type 6, and then click Next.
4. In Step 4 of 4, click Data Series in Columns. Use first 1 column(s) for X Data and first 0 row(s) for Legend Text, and then click Finish.

Select the line in the chart for the second series (the name box indicator should indicate S2). On the Format menu, click Selected Data Series, and then click the X Values tab.

Replace the X Values reference with the new x-axis reference for the second series.

In our example this is C1:C5.

Method 3

To use this method, follow these steps:

Enter the following data into a worksheet:


      A1:   1     B1: 5     C1:
      A2:   2     B2: 6     C2:
      A3:   3     B3: 4     C3:
      A4:   4     B4: 2     C4:
      A5:   5     B5: 5     C5:
      A6:  -1     B6:       C6:  -5
      A7:  -2     B7:       C7:  -6
      A8:  -3     B8:       C8:  -8
      A9:  -4     B9:       C9:  -5
      A10: -5     B10:      C10: -1

Select the range A1:C10, and in the ChartWizard, select the following options:
1. In step 1 of 4, click Next.
2. In Step 2 of 4, click XY (Scatter) and click Next.
3. In Step 3 of 4, select type 6 and click Next.
4. In step 4 of 4, click Data Series In Columns: use first 1 column(s) for X Data and first 0 row(s) for Legend Text.

Click Finish.

REFERENCES

Microsoft Excel 97

For more information about adding a new series, click the Office Assistant, type "adding a series," click Search, and then click to view "Add data to a chart."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Excel Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

Q120802 Office: How to Add/Remove a Single Office Program or Component

Microsoft Excel 7.0

For more information about adding a new series, press F1. In the Search box, type the following:

adding a series

and select "Add data to a chart."

Earlier Versions of Microsoft Excel

For more information about adding a new series, click the Search button in Help, and type:

series, data, chart, adding

Additional query words: XL97 non-contiguous contiguous axes overlay

Keywords : kbualink97 kbdta kbchart xlchart
Version : MACINTOSH:4.0,5.0,5.0a; WINDOWS:4.0,4.0a,5.0,5.0c,7.0,7.0a
Platform : MACINTOSH WINDOWS
Issue type : kbhowto