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

• 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 98 Macintosh Edition
• 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. You can add a second series to the chart using non-related x-axis values, by using any of the following methods.

MORE INFORMATION

In Microsoft Excel versions 5.0 and 7.0, you can add a second series to an xy (scatter) chart that uses a different set of x values by using any of the first three methods. In Excel 97 for Windows and Excel 98 Macintosh Edition, use method 4.

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:

       a. In step 1 of 4, click Next.
   

       b. In Step 2 of 4, click XY (Scatter), and then click Next.
   

       c. In Step 3 of 4, select type 6, and then click Next.
   

       d. 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 pressing the CTRL key and selecting the different range of cells.) In the ChartWizard, follow these steps:

       a. In Step 1 of 4, click Next.
   

       b. In Step 2 of 4, click XY(Scatter), and then click Next.
   

       c. In Step 3 of 4, select type 6, and then click Next.
   

       d. 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:

       a. In step 1 of 4, click Next.
   

       b. In Step 2 of 4, click XY (Scatter) and click Next.
   

       c. In Step 3 of 4, select type 6 and click Next.
   

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

Method 4

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 in the chart sub-type area of the dialog box. Click Next.

4. Click the Series tab. Then, click Add. In the X Values box, type C1:C5; in the Y Values box, type D1:D5. Then, click Finish.

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

REFERENCES

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

   series, data, chart, adding

   adding a series