Graphing Data from the Chemistry Laboratory

  • Topics
  • Scientific Visualization Objectives
  • Tools
  • Teacher Information
  • References
  • Tutorial Part I
  • Tutorial Part II
  • Evaluation Criteria
  • Extensions

    • Purpose

    To graph and analyse data using a spreadsheet.   Guidelines (heuristics) for creating a good graph are reviewed.

    Overview

    Topics

    Scientific Visualization:  2D data visualizations
    Science:  Chemistry  Beer's Law Absorbtion Spectra
     
     

    NC Scientific Visualization Objectives

    4.04 Describe visual methods for representing data-driven visualizations
    5.03 Produce computer based data visualization projects

    Tools

    Excel:  This tutorial was written specifically for Excel.  The tutorial could be adapted for other spreadsheet packages.

    Teacher Information

    This exercise is primarily designed to give students basic skills in creating scatter plots in Excel, and then adding either a regression line or fitted curve to the data points. These techniques are very good for labs in fields like chemistry and physics where the data collected by the students needs to be interpreted in relation to some theoretical model. For example, does the slope of the regression line, fitted to the collected data, match the theoretical slope calculated from the equation? In addition to these basic skills, some principles of good graph design are demonstrated with the somewhat modest graph formatting options allowed in Excel.

    References

    Tutorial - Table of Contents

    Introduction

    Part 1 - Beer's Law Scatter Plot and Linear Regression

    1. Entering and Formatting the Data in Excel
    2. Creating the Initial Scatter Plot
    3. Creating a Linear Regression Line
    4. Using the Regression Equation to Calculate Concentration
    5. Adjusting the Chart Display

    Part 2 - Titration Data Plotting

    1. Creating a Scatter Plot of Titration Data
    2. Curve Fitting to Titration Data
    3. Changing the Scatter Plot to a Line Graph
    4. Adding a Reference Line
    5. Modifying the Chart Axis Scale

    Introduction

    Beer's Law states that there is a linear relationship between concentration of a colored compound in solution and the light absorption of the solution. This fact can be used to calculate the concentration of unknown solutions, given their absorption readings. First, a series of solutions of known concentration are tested for their absorption level. Next, a scatter plot is made of this empirical data and a linear regression line is fitted to the data. This regression line can be expressed as a formula and used to calculate the concentration of unknown solutions.

    Finally, some techniques are demonstrated as to how to make the plot more readable using the formating options available in Excel.
     

    Part 1 - Beer's Law Scatter Plot and Linear Regression

    Entering and Formatting the Data in Excel

    Open Excel.

    Your data will go in the first two columns in the spreadsheet (see Figure 1a).

    Begin by formatting the spreadsheet cells so the appropriate number of decimal places are displayed (see Figure 1a).

    Figure 1a

    If you do not have your own data, you can copy the data seen in Figure 1b.

    Figure 1b.

    The concentration data is probably better expressed in scientific notation.

    Figure 2.

    Creating the Initial Scatter Plot

    With the data you want graphed, start the chart wizard

    Figure 3.

    The first dialogue of the wizard comes up

    Figure 4a.

    The Data Range box should reflect the data you highlighted in the spreadsheet. The Series option should be set to Columns, which is how your data is organized (see Figure 4b).

    Figure 4b.

    The next dialogue in the wizard is where you label your chart (Figure 4c)

    Figure 4c.

    Figure 4d.

    Keep the chart as an object in the current sheet (Figure 4e). Note: Your current sheet is probably named with the default name of "Sheet 1".

    Figure 4e.

    The initial scatter plot is now finished and should appear on the same spreadsheet page as your original data. Your chart should look like Figure 5. A few items of note:

    Figure 5.

    Creating a Linear Regression Line (Trendline)

    When the chart window is highlighted, besides having the chart floating palette appear, a Chart menu also appears. From the Chart menu, you can add a regression line to the chart.

    Figure 6a.

    Figure 6b.

    The chart now displays the regression line (Figure 7)

    Figure 7.

    Using the Regression Equation to Calculate Concentrations

    The linear equation shown on the chart represents the relationship between Concentration (x) and Absorbance (y) for the compound in solution. The regression line can be considered an acceptable estimation of the true relationship between concentration and absorbance. We have been given the absorbance readings for two solutions of unknown concentration.

    Using the linear equation (labeled A in Figure 8), a spreadsheet cell can have an equation associated with it to do the calculation for us. We have a value for y (Absorbance) and need to solve for x (Concentration). Below are the algebraic equations working out this calculation:

    y = 2071.9x + 0.111

    y - 0.0111 = 2071.9x

    (y - 0.0111) / 2071.9 = x

    Now we have to convert this final equation into an equation in a spreadsheet cell. The equation associated with the spreadsheet cell will look like what is labeled C in Figure 8. 'B12' in the equation represents y (the absorbance of the unknown). The solution for x (Concentration) is then displayed in cell 'C12'. Note: If your equation differs for the one in this example, use your equation

    Duplicate your equation for the other unknown.

    Note that if you highlight your new equation in C13, the reference to cell B12 has also incremented to cell B13.

    Figure 8.
     

    Adjusting the Chart Display

    The readability and display of the scatterplot can be further enhanced by modifying a number of the parameters and options for the chart. Many of these modifications can be accessed through the Chart menu, the Chart floating palette, and by double-clicking the element on the chart itself. Let's start by creating a better contrast between the data points and regression line and the background.

    Figure 9a.

    In the Chart Area Format dialogue, set the border and background colors (see Figure 9b)

    Figure 9b.

    Now, delete the horizontal grid lines

    Now, adjust the color and line weight of the regression line and the color of the data points Finally, you can move the regression equation to a more central location on the chart If necessary, resize the font size for text elements in the graph. The results can be seen in Figure 9c.
     
     

    Figure 9c.

     

    Part 2 - Titration Data Plotting

    Creating a Scatter Plot of Titration Data

    In this next part of the tutorial, we will work with another set of data. In this case, it is of a strong acid-strong base titration (see Figure 10). With this titration, a strong base (NaOH) of known concentration is added to a strong acid (also of known concentration, in this case). As the strong base is added to solution, its OH- ions bind with the free H+ions of the acid. An equivalence point is reached when there are no free OH- nor H+ ions in the solution. This equivalence point can be found with a color indicator in the solution or through a pH titration curve. This part of the tutorial will show you how to do the latter.

    In the last part of the tutorial, the axis scale is manipulated on the plot in order to get a closer look at the most critical part of the plot: the equivalence point.

    Note that there should be two columns of data in your spreadsheet:

    Column A: mL of 0.1 M NaOH added

    Column B: pH of the 0.1 M HCl / 0.1M NaOH mixture

    Figure 10.

    Now, create a scatter plot of titration data, just as you did with the Beer's Law plot.(Figure 11).

    Figure 11.

    Continue through steps 2 through 4 of the Chart wizard:

    The resulting plot should look like Figure 12:

    Figure 12.

    Curve Fitting to Titration Data

    The next logical question that you might ask is whether a linear regression line or a curved regression line might help us interpret the titration data. You may remember that our goal with this plot is to calculate the equivalence point, that is, what amount of NaOH is needed to change the pH of the mixture to 7 (neutral)?

    Create a linear regression line:

    Looking at the data (Figure 13a), it is clear that the first 45 ml of NaOH do little to alter the pH of the mixture. Then between 45 ml and 55 ml, there is a sharp rise in pH before leveling off again. The data trend does not seem linear at all and, in fact, a linear regression line does not fit the data well at all.

    Figure 13a.

    The next approach might be to choose a different type of trendline (Figure 13b):

    Figure 13b.

    You can see that a second order polynomial curve does not capture the steep rise of the data well. A higher order curve might be tried (Figure 13c):

    Figure 13c.

    Still, the third order polynomial does not capture the steep part of the curve where it passes through a pH of 7. Even higher order curves could be created to see if they fit the data better. Instead, a different approach will be taken for this data. Go ahead and delete the regression curve:

    Changing the Scatter Plot to a Line Graph

    Instead of adding a curved regression line, all of the points of the titration data are connected with a smooth curve. With this approach, the curve is guaranteed to go through all of the data points. This is both good and bad. This option can be used if you have only one pH reading per amount of NaOH added. If you have multiple pH readings for each amount added on the scatter plot, you will not end up with a smooth curve. To change the scatter plot is a (smoothed) line graph (Figure 14a):

    Figure 14a.

    The result should look like Figure 14b:

    Figure 14b.

    This smooth, connected curve helps locate where the steep part of the curve passes through pH 7.

    Adding a Reference Line

    The chart can be enhanced by adding a reference line at pH 7. This clearly marks the point where the curve passes through this pH. Further refinements in the chart can be made by (as you did with the Beer's law chart): The result should look like Figure 15.

    Figure 15.

    Modifying the Chart Axis Scale

    The above chart gives a good overview of the entire titration. If you would like to focus exclusively on the steep part of the curve between 45 and 55 ml of added NaOH, a new chart can be created which limits the X Axis range. Start by making a copy of the current chart: With the new chart highlighted (Figure 16):

    Figure 16.

    Next, both vertical and horizontal gridlines can be added to more accurately locate the equivalency point (Figure 17):

    Figure 17.

    With enhancements similar to what you did to the other chart, the result will look like Figure 18.

    Figure 18.

    Even with this smooth curve passing through all of the data points, it is still an estimation of what intermediate mL added/pH data points would be. A clear inaccuracy is where the curve moves in a negative X direction between the 50 and 51 mL data points. More data points collected between 49 and 51 mL would both better smooth the curve and give a more accurate estimation of the equivalency point.
     

    Evaluation Criteria

    Success will be measured by the following criteria:
     
    2D Data Driven Visualization
    Transfer the data to a spreadsheet
    5
    Properly format the data in the spreadsheet
    5
    Create chart from data
    10
    Fonts chosen are clear and easy to read
    5
    Graphic has appropriate titling and labeling
    5
    Regression line and formula are displayed properly
    10
    Axes scaled to data
    5
    Fitted line for titration graph created properly
    5
    Legends labeled clearly
    5
    Colors chosen to aid understanding 
    5
    Data marks clear
    5
    Overall graph portrays pattern of data
    10
    An explanation is given as to the chemical principles behind the regression line and the fitted titration curve
    20
    Cooperation allows multiple graphs to be compared
    5
    Total 
    100

    Extensions:

    Possible extensions include making charts and graphs of other chemical reactions carried out in the lab. This type of graphing also lends itself to physics and technology education labs where data is collected, graphed, and compared to some theoretical equation. Examples might be a lab on Ohm's law or velocity of a toy car on a downhill track. Make sure if experiments are carried out,  that the lab and students are properly outfitted with safety equipment.
     
     

    Top of Page           Lesson Plan Menu

    SciVisHome





    5/30/00