Instructions for using Microsoft Excel to Graph Chemical Data

To learn how to get the computer to plot the graph, then test the linearity of the line and obtain the slope and intercept values, we will use data that we know the answers for.

For example if we use the equation   y = 2 x  then we know it's a straight line with slope equal to 2 and intercept equal to 0.
Also when we use the correlation coefficient R to test the linearity of the data we also know we should get a value of R = 1. The correlation coefficient R is a number between 0 and 1 . The closer to 1 then the better the data fits a straight line. To compare two graphs we just compare the value of R for each plot.

In this case our data can be as follows:

                     X       Y
......................1        2
                      2        4
                      3        6

    Open MicroSoft Excel and enter your x and y values in two columns.
    Click this link to see what your spread sheet should look like. Excelteach1

Note, clicking on the link should open Microsoft Excel on your computer !!!

    Click on the Chart Wizard icon on the tool bar at the top of the table.

    a box will open entitled Chartwizard Step 1 of 4  Chart type

    in chart type select XY scatter

    in chart sub type select the middle left graph, that is the one with curves and data points

    click Next

    a box will open entitled Chartwizard Step 2 of 4 Chart source data

    hit backspace on your keyboard to remove the entry in the data range  entry bar, it will go blank

   drag over the numbers in the x and y columns, BUT NOT the x and y characters.
    this will enter your data in to the data range bar, it will show   =sheet1!$A2:$B$4

    click next

    a box will open entitled Chartwizard Step 3 of 4 Chart options

   enter y=2x for the Title    x for x and y for y

    click Gridline tab at top of box

    remove the entry on major grid lines leaving all blank, this removes the lines

    click Next

    a box will open entitled Chartwizard Step 4 of 4 Chart location

    leave entry in " As object in", this will plot the graph on your data sheet

    Click Finish

The graph will appear in the spread sheet, click  Excelteach2  to see what you should see on your own spreadsheet

Excel calculation of correlation coefficient R , slope and intercept.

    Open " tools" pulldown menu

     You should see Data Analysis....  Open this

   If  Data Analysis does not appear it can be loaded in to menu in the following manner

    Open the Help tab at top of excel sheet
    A box will appear with the question What would  you like  to do?
    In the space that says " Type your Question here" Hit backspace and type in Analysis toolpak
    A new box appears  with  What would  you like  to do?
    and below that it will say Install and use toolpak,  Click on the button and the instructions will appear
    Follow these instructions,
    you may need to close and reopen Excel for data Analysis to appear in your tools menu.

   When you open data Analysis  a new box will open and provide a list of Analysis tools
    Scroll down to   Regression and  click on it , it will then be highlighted, then click OK

    A box labeled Regression will appear
    put he cursor in the Input Y range
    then drag over your  Y  values in your spreadsheet to enter them as you did to enter data for the graph
    put he cursor in the Input X range
    then drag over your  x  values in your spreadsheet to enter them

    Then click OK
    A new sheet will appear labeled Summary Output
   click Excelteach3 to see what your sheet should look like

    The first heading will be Regression Statistics
    with
                    Multiple  R      1            This the correlation coefficient R
                    R square        1            This is the square of R

    Note for our straight line  Y = 2X   R should equal 1, a perfect straight line and this is what we get!!!!

   There are many other data in the table but he ones we want are the slope and Intercept
 

    with
            Intercept         O
            X Variable      2    Excel uses the term X Variable for the slope

    Note  for our straight line  Y = 2X
    Intercept should equal 0, and slope should equal 2   and this is what we get!!!!

   You may copy the relevant data and paste it on your spreadsheet
    ( labeled sheet 1 at bottom of page , just click sheet 1 to return to it)

    Click Excelteach4 to see what it should look like
    when the data is pasted onto the sheet with your x and y data , graph and regression data.