Online Lab Submission
Students: Instructors: Pre-Lab Assignments
Interactive Animations:

Graphing Linear Relationships

Creating and interpreting graphs is a skill that must be included and cultivated in the general chemistry curriculum. As there isn't an expectation that freshmen students possess a working knowledge of Excel, the general chemistry laboratory is the perfect place for students to follow a "guided experience" (described under the More Information link) inside Excel to display their lab data. The Chem21Labs program will display a graph of the student's data as an embedded part of the lab report on the webpage . . . . students can "compare" their Excel graph to this Chem21Labs graph as an initial check that they followed the directions provided by their "guide." Next, the student enters the information they collected during the "tour" . . . . slope, y-intercept, R2, standard deviation . . . . into the Chem21Labs lab report webpage. The student receives immediate feedback on the correctness of their values and either continues with the lab submission process or takes a second "guided tour" through Excel.

In addition to plotting  y vs. x , buttons at the top of the graph can be clicked to plot  1/y vs. x , and  ln(y) vs. x  . . . . these relationships are helpful in understanding PV = nRT relationships as well as whether a reaction follows 1st or 2nd order kinetics.

Additionally, "More Information" (click link below) about graphing lab data with Excel can be delivered to students inside an expandable section - this section can be collapsed using the "Hide Information" link below it.


Graphing with Excel

  1. Open Excel by clicking on Start, All Programs, Microsoft Office, Microsoft Office Excel
  2. [show=11] [show=12]
    [show=21] [show=22]
    [show=31] [show=32]
    [show=41] [show=42]
    [show=51] [show=52]
    Place x values (the following [show=] syntax is used in Chem21Labs to "redisplay" a value that resides in the database . . . . the student will not see the [show=] text, they will see their actual lab data . . . . [show=11], [show=21], [show=31], [show=41], [show=51]) in cells A1 – A5 and y values ([show=12], [show=22], [show=32], [show=42], [show=52]) in cells B1 – B5. To enter values into cells, click the cell and type the number then press ENTER – enter only numbers in the spreadsheet. You can also copy / paste the values (see table to the right) by clicking on [show=11] and dragging / highlighting until the cursor is on [show=10452] and all values are highlighted.  Copy (Ctrl + C) the values to your clipboard, click in Cell A1 in Excel, and Paste (Ctrl + V) the values into Excel.
  3. Click on the INSERT tab and then click on the SCATTER chart in the CHARTS area. Click on the SCATTER WITH ONLY MARKERS icon.
  4. This will place a chart on the page. To make sure the chart is graphing your data correctly, right-click in the white area on the right side of the chart and click the Select Data link.
  5. Click Series 1 to highlight it and then click the Edit button. Make sure the X values are '=Sheet1!$A$1:$A$5' and the Y values are '=Sheet1!$B$1:$B$5'.
  6. Click OK and then click OK again.
  7. Click the graph to select it and then click the '+' icon that appears to the right.  Check the Trendline option.
  8. To place the Equation and R2 value on the graph, mouse over the space to the right of the word 'Trendline' and click the arrow that appears.  Click MORE TRENDLINE OPTIONS and then click Display Equation on Chart and Display R2 Value.
  9. Your graph will be displayed along with the equation for the 'best-fit' line through the 5 data points. Report the slope of the line below. If the slope in the Excel equation doesn't have at least 3 Sig Figs, click in an empty Excel cell and enter the following starting with the equal sign:  =slope(B1:B5,A1:A5).  Press the Enter Key and the slope will appear.
  10. Add a Chart Title and Axes Titles (click the chart, highlight the Design tab, click the Add Chart Element button).

To see a video on how to use Excel to create a graph, determine the slope and y-intercept of the best-fit line, format the title and axes, save it as an image file, and upload it to Chem21Labs, click the How To Graph Using Excel link (you may have to right-click and select Show All to see the top of the video).