Graphing Data

 

Data from Excel can either be plotted on the same sheet as the data table or on a separate sheet.  The sheets are indicated by the tabs at the bottom of the screen.

 

 

Data Plot on the Same Sheet

 

To plot a graph of the data on the same sheet, you must first highlight the data.

 

 

Then click on the Chart Wizard Icon located on the Menu Bar.

 

 

A Chart Wizard window will open.

 

 

Select the XY (Scatter) option then Next>

 

 

Then Next>

 

 

This window lets you label the axis and format the graph.  Click on the Legend tab and turn off the legend by clicking on the Show legend box.

 

 

Click on Next>

 

 

This window is where you can specify to add the chart as a new sheet or on the same sheet.  We will add the chart as a new sheet then select Finish.

 

 

 

Trendline

 

To add a Trendline click on any data point and right click on the mouse.

 

 

Select Add Trendline…

 

 

This will open a Trendline window.  The trendline type that we want to select for this data is Linear.  Do NOT click on OK yet.  First select the Options tab.

 

 

Click on OK.

 

 

Error in the Slope

 

To determine the +/- error in the slope and the y-intercept, an array must be created.  Go back to the original spreadsheet and highlight 4 empty boxes as shown below.

 

Click the mouse in the formula box and enter the LINEST function.

 

=LINEST(B2:B12,A2:A12,true,true)

 

The B2:B12 interval represent the known y values and the A:2:A12 interval represent the x values.  The LINEST function will evaluate the errors in the slope and y-intercept for a data set. The const and stats should be labeled true and true as shown below.

 

 

 

 

 

 

An array should be entered in the four boxes.  The array represents the following: