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

Enter and format data in an Excel spreadsheet in a form appropriate for
graphing

Create a scatter plot from spreadsheet data

Insert a linear regression line (trendline) into the scatter plot

Use the slope/intercept formula for the regression line to calculate a
x value for a known y value

Explore curve fitting to scatterplot data

Create a connected point (line) graph

Place a reference line in a graph
Topics
Scientific Visualization: 2D data visualizations
Science: Chemistry Beer's Law Absorbtion Spectra
NC Scientific Visualization
Objectives
4.04 Describe visual methods for representing datadriven 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

Bertoline, G. R., Wiebe, E. N., Miller, C., &
Mohler, J. L. (1997). Technical graphics communications (2nd ed.).
New York, NY: McGrawHill.

Cleveland, W. S. (1985). The elements of graphing
data. Monterey, CA: Wadsworth.

Kosslyn, S. M. (1994). Elements of graph design.
San Francisco, CA: W. H. Freeman.
Tutorial  Table of Contents
Introduction
Part 1  Beer's Law Scatter Plot and Linear Regression

Entering and Formatting the Data in Excel

Creating the Initial Scatter Plot

Creating a Linear Regression Line

Using the Regression Equation to Calculate Concentration

Adjusting the Chart Display
Part 2  Titration Data Plotting

Creating a Scatter Plot of Titration Data

Curve Fitting to Titration Data

Changing the Scatter Plot to a Line Graph

Adding a Reference Line

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).

Title the spreadsheet page in cell A1

Label Column A as the concentration of the known solutions in cell
A3.

Label Column Bas the absorption readings for each of the solutions
in cell B3.
Begin by formatting the spreadsheet cells so the appropriate number of
decimal places are displayed (see Figure 1a).

Click and drag over the range of cells that will hold the concentration
data (A5 through A10 for the sample data)

Choose Format > Cells... (this is shorthand for choosing Cells...
>from the Format menu at the top of the Excel window)

Click on the Number tab

Under Category choose Number and set Decimal places to 5

Click OK

Repeat for the absorbance data column (B5 through B10 for the sample data),
setting the decimal places to 4
Figure 1a
If you do not have your own data, you can copy the data seen in Figure
1b.

Enter your data below the column titles

You can also place the absorption readings for the unknown solutions below
the other data.
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

Choose the Chart Wizard icon from the tool bar (Figure 3). If the
Chart Wizard is not visible, you can also choose Insert > Chart...
Figure 3.
The first dialogue of the wizard comes up

Choose XY (Scatter) and the unconnected points icon for the
Chart subtype (Figure 4a)
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)

Enter Beer's Law for the Chart Title

Enter Concentration (M) for the Value X Axis

Enter Absorbance for the Value Y Axis
Figure 4c.

Click on the Legend tab

Click off the Show Legend option (Figure 4d)
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:

Your data should look as though it falls along a linear path

Horizontal reference lines were automatically placed in your chart, along
with a gray background

Your chart is highlighted with square 'handles' on the corners. When your
chart is highlighted, a special Chart floating palette should also appear,
as is seen in Figure 5. Note: If the Chart floating palette does
not appear, go to Tools>Customize..., click on the Toolbars
tab, and then click on the Chart checkbox.If it still doesn't show
up as a floating palette, it may be 'docked' on one of your tool bars at
the top of the Excel window.

With your graph highlighted, you can click and drag the chart to a wherever
you would like it located on the spreadsheet page. Grabbing one of the
four corner handles allows you to resize the graph. Note: the graph
will automatically adjust a number of chart properties as you resize the
graph, including the font size of the text in the graph. You may need to
go back and alter these properties. At the end of the first part of this
tutorial, you will learn how to do this.
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.

Choose Chart > Add trendline...
A dialogue box appears (Figure 6a).

Select the Linear Trend/Regression type
Figure 6a.

Choose the Options tab and select Display equation on chart
(Figure 6b)
Figure 6b.

Click OK to close the dialogue
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'.

Highlight a spreadsheet cell to hold 'x', the result of the final
equation (cell C12, labeled B in Figure 8).

Click in the equation area (labeled C, figure 8)

Type an equal sign and then a parentheses

Click in the cell representing 'y' in your equation (cell B12 in
Figure 8) to put this cell label in your equation

Finish typing your equation
Note: If your equation differs for the one in this example, use your equation
Duplicate your equation for the other unknown.

Highlight the original equation cell (C12 in Figure 8) and the cell
below it (C13)

Choose Edit > Fill > Down
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 doubleclicking the element on the chart itself.
Let's start by creating a better contrast between the data points and regression
line and the background.

Doubleclick in the gray background area of the chart or
by selecting Chart Area in the Chart floating palette and then clicking
on the Format icon (Figure 9a).
Figure 9a.
In the Chart Area Format dialogue, set the border and background colors
(see Figure 9b)

Choose None for a Border

Choose the white square from the color palette for an Area color

Click OK
Figure 9b.
Now, delete the horizontal grid lines

Click on the horizontal grid lines in the chart and press
the Delete key
Now, adjust the color and line weight of the regression line and the color
of the data points

Doubleclick on the regression line (or choose Series 1 Trendline
1 from the Chart floating palette and then click the Format icon)

Choose a thinner line for the Line Weight

Click on the word Automatic next to Line Color and the color
palette appears. Choose dark blue from the color palette

Click OK

Doubleclick on one of the data points (or choose Series
1 and click the Format icon)

Choose dark red from the color palette for the Marker Foreground
and Background

Click OK
Finally, you can move the regression equation to a more central location
on the chart

Click and drag the regression equation
If necessary, resize the font size for text elements in the graph.

Either double click the text element or choose it from the floating
palette

Click on the Fonts tab

Choose a different font size
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 acidstrong 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

Using a new page in the spreadsheet, enter your titration data. If you
do not have your own data, use the data shown in Figure 10.

Return to the beginning of the tutorial if you need
hints on formatting the cells to the proper number of decimal places
Figure 10.
Now, create a scatter plot of titration data, just as you did with the
Beer's
Law plot.(Figure 11).

Highlight the titration data and the Column headers

Click on the Chart wizard icon

Choose XY (Scatter) and the scatter Chart subtype
Figure 11.
Continue through steps 2 through 4 of the Chart wizard:

The defaults for step 2 should be fine if you properly highlighted
the data

In step 3 enter the chart title and x and y axis labels
andturn
off the legend

In step 4, leave as an object in the current page
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:

Choose Chart > Add Trendline...

Pick Linear subtype
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):

Click on the linear regression line in the plot and press
the delete key to delete the line

Choose Chart > Add Trendline...

Pick Polynomial subtype

Set the Order of the curve to 2
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):

Doubleclick on the curved regression line

Set the Order of the curve to 3
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:

Click on the curved regression line in the plot and press
the delete key
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):

Choose Chart > Chart Type...

Select the Scatter connected by smooth lines Chart subtype
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.

A set of drawing tools should be visible at the bottom of the window. If
not, click on the Draw icon two to the right of the Chart wizard
icon.

Make sure your chart is highlighted

Choose the line tool at the bottom of the window

Draw a horizontal line at pH 7 across the width of the chart by
clicking and dragging a line across the chart area.

With the horizontal line still highlighted, choose a 3/4 pt line thickness
and a dashed line type at the bottom of the window
Further refinements in the chart can be made by (as you did with the Beer's
law chart):

removing the other horizontal grid lines

turning off the border

changing chart colors

Thickening the curve and shrinking the data points
emphasizes the fitted curve over the individual data points
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:

Select the current chart by clicking near its border

Choose Edit > Copy

Click a spreadsheet cell about 10 rows below the current
chart

Choose Edit > Paste
With the new chart highlighted (Figure 16):

Choose Value (X) Axis from the Chart floating palette

Click on the Format icon

Set the Minimum to 45, Maximum to 55

Set the Major unit to 1 and Minor unit to 0.25

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

Choose Chart > Chart Options...

Click on the Gridlines tab

Select X axis Major gridlines and Y axisMajor
gridlines

Click OK
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