Microsoft KB Archive/26718

Hint for Making a Scatter Chart (an X-Y Plot) in Excel PSS ID Number: Q26718 Article last modified on 02-26-1993 PSS database name: W_eXceL

2.x 3.00 | 2.20 3.00

WINDOWS | OS/2

Question:

What is the best way to make an x-y plot from two columns of numbers? The way I usually do it is as follows:


 * 1) Highlight the two columns of numbers.
 * 2) Choose Edit Copy.
 * 3) Press F11 for a new chart.
 * 4) Choose Edit Paste Special and specify that the first column should be used for the categories.

Response:

If using Excel 3.0
In Excel 3.0 if you press F11 after selecting your values Excel will present you with a dialog box allowing you to tell Excel to use your values as X and Y values for an X-Y Scatter Plot.

If using Excel 2.x
The shortcut method involves leaving the first cell in the selection blank. For example, the following is a typical layout for data and what usually goes wrong:

x y 11 17.3 14 12.45 19.7 22.345

If you select the range containing the two columns, including the x and y labels, you will get a chart with two series in it when you press F11. This is not the desired result.

The correct method is as follows:


 * 1) Leave the cell with the x label in it blank.
 * 2) Highlight the same area and press F11.

This method tells Excel to make a chart and use the values in the first column as the categories (the x values in this case). The following is the sample data without the x label (of course, after this is done, choose Gallery Scatter to make the chart type an actual x-y plot if it is not your preferred chart type):

Y 11 17.3 14 12.45 19.7 22.345

For more information, please refer to Pages 131-143, 625-629, and 406-407 in the “Microsoft Excel Reference” manual.

Copyright Microsoft Corporation 1993.