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

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:

1. 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
   						

2. Select the range A1:B5. On the Standard toolbar, click the Chart Wizard tool.
3. In the list of chart types, click XY (Scatter). Click one of the line charts under Chart Sub-type. Click Next.
4. 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.
5. 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:

1. 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
   						

2. 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.
3. Select the range C1:D5 and click Copy on the Edit menu.
4. Double-click the chart created in step 2.
5. 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:

1. 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.
2. 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.
3. 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:

1. 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
   						

2. 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.
3. Click Finish.