XY Charts in Excel Worksheets

Select Data

Chart Wizard

Chart Source

Change Data Range

Chart Options

Fine Tuning

Add Trendline

Scientific Graphs

This tutorial covers the production of XY (scatter) charts from data in Excel worksheets. XY charts are used to graph paired data. X is usually assigned to independent variable, the variable whose value is controlled or set by the experimenter. The Y variable is then the depended variable, the values that follow from the each value of X set in the experiment. In some cases, neither the X or Y is set by the experimenter.

In Excel and other graphing programs, XY charts are quite different from line charts or graphs. XY charts plot the actual values of both X and Y while line charts plot the Y value against the row number from the spreadsheet. For more information visit the Microsoft Office Update article, "Creating XY (Scatter) Charts in Excel 97 and 2000".

Words in RED refer to menu or dialog box items, or words to enter. top of page

Using the Images

The images below are shown at a reduced scale. Click on the image to jump to a full size picture. Then use the Back and Forward arrows on your browser to jump back and forth between this page and the image as often as needed.

Select Data

Click on the first X value and drag to select the entire X range. If the Y data is located in the next column, also select it. This example uses the weight and height example in which weight is in column B and the calculated height values to plot are in column E. To give Excel two sets of values to start working with, select column C, even though it does not contain the actual Y values. In this case the actual Y range is selected in a later step.

top of page

Start the Chart Wizard

Start the Chart Wizard by clicking on the tool bar. This will guide you through four steps in making a chart.

Chart Wizard Step 1 - Select Chart Type

In step 1, select scatter plot and retain the default chart sub-type that is automatically selected on the right side of the dialog box. Click Next.

top of page

.

Chart Wizard Step 2 - Chart Source...

This step shows the selected Data Range and allows us to change it if desired. Click on the Series Tab to see the details of the X and Y Values. The X Value: =Sheet1!$B$2:$B$12 is correct. The Y Value: needs to be changed to =Sheet1!$E$2:$E$12.

Click on the square at the right end of the Y Value line known as the Collapsing Dialog button. This shrinks the dialog box allowing us to see the worksheet. top of page

.

Collapsing Dialog button

Change Data Range

As shown in the graphic on the right, we simply drag the dotted selection around the desired data series. Note that the Y Value: line has already changed to =Sheet1!$E$2:$E$12.

Click the Collapsing Dialog button again to display the full dialog box in order to complete Step 2. Click Next. top of page

Chart Wizard Step 3 - Chart Options

Step 3 deals with the labeling of the chart. Five tabs allow us to work with Titles, Gridlines, Axes, Legend, and Data Labels. Not all are relevant in this example.

In the graphic at the near right, the Chart Title and titles for the X and Y Axes have just been typed into their respective edit lines. Note that they already appear on the preview chart.

Series 1 on the right side of the chart is called a Legend. Since there is only one Y series there is no need for a Legend. Click the Legend tab and click the Show Legend check mark to remove the legend. Note that the Series 1 legend disappears immediately (see far right). Click Next to go to the final step of the Wizard. top of page

Enter Titles
Remove Legend

Chart Wizard Step 4 - Chart Location

The default to save as Object in Sheet1 is fine for our example. Click Finish. The option to save as a new sheet can be used when more than one chart is produced. top of page

.

Fine Tuning the Chart

The nearly finished chart appears, but several features need fine tuning. To remove the gray background, bring the cursor area into the Plot Area as shown on the cursor label, then double click. (graphic 1) The Format Plot Area dialog box opens. (graphic 2) On the right side, in the Area section, click None and click OK. Click the X in the upper right of the Chart Tool Bar to close it.

Move the whole chart to part of the sheet away from the data by dragging on the outside border of the chart with the . Enlarge the chart by dragging on the handles (small black squares). When the double headed arrow appears, you are ready to start changing size. (graphic 3). top of page

1. Select Plot Area
2. Format Plot Area

.

3. Size Arrow

.

Change Minimum Values on Axes

The chart has a cluster of data in the upper right quarter and is otherwise empty. To get a better view of the data, we can change the minimum value on the Y axis from zero to 50 inches.

Click on the Y axis to open the Format Axis dialog box. Click the Scale tab and change Minimum to 50. Likewise open the dialog box for the X axis and change Minimum to 100.

Add Trendline

The chart shows that weight generally increases as height increases (a direct proportion). We can get a better view of this relationship by fitting a linear curve to the data. Excel calls this a Trendline. Begin by clicking on one of the data points until the points turn yellow. (graphic 1)

On the Menu bar, click Chart and click Add Trendline. This opens the Add Trendline dialog box. The default settings for Trend/Regression type are Linear, Series 1. Keep these unchanged. (graphic 2)

Click the Options tab and click Display Equation on Chart. (graphic 3) The resulting chart is shown in graphic 4. The equation is of the form Y=mX+b. The slope m and intercept b are constants for the relationship that is useful in predicting new values. For example, we could predict the height of a person weighing 180 lb. assuming that the same general height/weight relationship. ( 180 * 0.2775 + 27.62 =77.55 inches or about 6.5 ft) The Options tab on Add Trendline also allows us to forecast forward or backward (known as extrapolation in math and science).

top of page

1. Select Points

2. Line Type

3. Line Options

4. Final Chart

© Treva Pamer 1-14-2001 MS Office Tutorial Home