Learning Generation - Microsoft Excel Tutorial

Create XY Scatter Graph Tutorial
Microsoft Excel
Objective: To use Microsoft Excel to create an XY(scatter) graph from data in a spreadsheet in order to further analyze the data.

Introduction: Often times scientists collect data from lab experiments that need to be extensively analyzed using scatter plots and graphs. Microsoft Excel offers the necessary options to graph data that have been entered into a spreadsheet. In addition, educators can conduct common scientific calculations by using the Excel (line equations and

values).

Skill Practice: Creating a XY (scatter) Graph

Step 1: Open

and enter a data set into the cells of the spreadsheet. Then click on

. Select

from the Chart Wizard - Step 1 window that appears and click on NEXT.

Step 2: The next window that appears is the Chart Wizard - Step 2. Select the SERIES tab from the two available tabs. You will need to remove ALL the data series showing by clicking on

several times.

Step 3: Next, the data to be graphed will need to be selected. This is done by clicking once on

. SERIES 1 will appear in the box above the ADD & REMOVE buttons.

Step 4: Select the X values from the

by clicking once in the X values box, which places the cursor there. Then select the appropriate data from the Excel spreadsheet by left-mousing clicking and holding on one cell and moving the cursor down until all the data points to be plotted are included within the rotating dashed lines. Then release the left-mouse button. A formula will appear in the X value box

, which corresponds to the selected cells' column and row location. The same steps will be taken to select the data for the Y values of the graph. Disregard the ={1} in the Y values box, as this will be replaced by

when the data from the Excel spreadsheet is selected. When selection of the X & Y values is complete, click on NEXT.

Step 5: The next window to appear is the Chart Wizard - Step 3. By selecting the TITLES tab, the title of the graph and the labels for the X & Y axis can be completed. The LEGEND tab can also be selected to determine where the legend box is located on the graph. The axes, gridlines, and data labels can also be changed in this window. When finished with this window, click on NEXT.

Step 6: Chart Wizard - Step 4 is the final window that appears and is where to select the location of the graph, within the current Excel spreadsheet or in a separate sheet. Chose one option and click on FINISH. The graph then appears in the selected location.

Step 7: Further analysis of the data can now be done. First place the pointer/cursor on one of the data points and left-mouse click on it once. The point will then appear as

. Next, with the pointer still on the data point, right-mouse click once. A box with several options will appear; choose ADD TRENDLINE. Another box will then appear; select the OPTIONS tab. Then left-mouse click once on both

. Click OK.

Step 8: A trendline will appear on the graph, as well as a line equation and an

value. To move the line equation and the

value off of the graph and next to the graph legend where it is easier to see, left-mouse click and hold on the information and "drag and drop" it near the legend box.