CopyrightCatalyst Education 2019
Using Excel for Graphing
Don't use plagiarized sources. Get Your Custom Essay on
need help with chemistry excel assignment
Just from $13/Page
• Students will work individually for this lab. Students will need a laptop in the lab for this
experiment.
OBJECTIVES
– To learn to use Excel (or comparable software) to graph and analyze data.
INTRODUCTION
Graphical representation of laboratory data is often used to interpret the results of laboratory
experiments. Excel is a software program that can provide many ways to graphically represent data for
a laboratory experiment; it allows you to organize the data in a spreadsheet from which graphs can be
produced.
PROCEDURE
EXCEL 2016 GRAPHING INSTRUCTIONS
Graphs can be prepared by hand or by computer. While you may want to do a quick sketch of your data
in graphical form to get a first impression of any relationships or trends, you will want to produce your
final, working graph using graphing software. Instructions are provided here for Microsoft Excel using
data provided below. Note that Excel refers to a graph as a “chart”.
Creating a Chart:
1. Open Microsoft Excel.
2. Enter the paired data in columns. Be sure to label the columns with informative titles. Include
units in the title where applicable.
Copyright Catalyst Education 2019
3. Highlight the cells that contain the data to be graphed. If you want the column and row labels
to appear in the chart, include those cells in the selection.
4. Click on the Insert tab and then “Scatter” for the plot type. While there are many charting
options, the scatter plot will be used exclusively in this course. A new window will open; select
the option without any lines……
…and the x-y scatter plot will be automatically inserted onto the worksheet:
Copyright Catalyst Education 2019
5. Notice that there is a default title based on one of the column titles but no labels for the axes.
You will definitely want to add axis labels, and perhaps change the title to make it more
informative. You can also change the scale or the tick marks.
To change the title: Left click on the title; the
text is now accessible for editing. Font type,
style and size can also be changed while the title
is highlighted by returning to the “Home” tab.
To edit the axes: Left click on the add chart element
box; this will add text boxes with “Axis Title” to both
axes. Now double click on either text box to change
the axis labels.
Copyright Catalyst Education 2019
To add a trendline:
If you want to fit your data to a trendline (i.e., the best
fit line of all the data points), left click on a data point
to select it, then right click and select “Add Trendline”:
Select the type of trendline; for most purposes this will be linear.
Then check the boxes for “Display Equation on chart” and “Display R-squared value on chart”.
Close the window and the best-fit straight line will be drawn
and the equation and RP2P value displayed on the graph. These
last two items are in a text box, which can be moved to an
easy-to-read location on the graph. Double click on the
equation to change the “y” and “x” to “IQ” and “(Lead
Levels)” respectively.
If needed this page of the workbook can be printed; both the data and the graph will be on one page.
Alternatively, by clicking on the graph and then printing, you will obtain a full page chart.
To solidify your graphing skills using Excel (or other graphing software), complete one of the data sets
below. Based on your birthday, select the data set, complete the worksheet and prepare two graphs
according to the instructions in the worksheet. Submit the worksheet and graphs to your TA at the start
of the next lab period.
If your birthday is in
Jan – Apr use Data Set A
May – Aug use Data Set B
Sep – Dec use Data Set C
Copyright Catalyst Education 2019
Graphing Data Set A
Name:___________________________ TA Name/Lab Section:_____________________________
Pi (p) is the ratio of the circumference of a circle to its diameter. The value of this ratio is a
constant regardless of the size of the circle; thus pi is a universal physical constant. The
diameter and circumference of several circles were measured by CHEM 1114 students,
each using a different ruler. (Include units below if applicable. If necessary, use a
separate sheet of paper for 6c and 7c.)
1. Inspect the data below and calculate the value of pi using two pairs of the data: _______ _______
2. Prepare a hand-drawn plot of the two variables on the reverse side of this worksheet. Include a title, axis
labels (with units), and a trendline.
Estimate the circumference of a circle with a diameter of 4.50 cm:____________
Estimate the diameter of a circle with a circumference of 3.94 inches:________________
3. a. Prepare a plot using graphing software. Include a title, axis labels (with units), the equation of the best-fit
line and the RP2P value on the graph.
b. Re-write the equation of the best-fit line substituting “Diameter” for x and “Circumference” for y directly
on the graph.
c. Attach the fully labeled graph to this worksheet.
4. What is the value of pi based on the equation for the best-fit line? __________
5. Determine the percent error using the definition of percent error:
Use a value of 3.142 for the actual value of pi.
% Error = _____________________
6. Using your computer-generated graph,
a. visually estimate the circumference of a circle when the diameter is 4.50 cm: _________________.
b. calculate the circumference for d = 4.50 cm using the equation of the best fit line: _____________. Use
the graph to ensure that
this value is reasonable.
c. compare the calculated circumference to the two visually interpolated values (Steps 2 and 6a). Briefly
discuss any discrepancies.
7. Using your computer-generated graph,
a. visually estimate the diameter of a circle with a circumference of 3.94 inches: ________________.
b. calculate the diameter using the equation of the line: ___________. Use the graph to ensure that this
value is reasonable.
c. compare the calculated diameter to the two visually interpolated values (Steps 2 and 7a). Briefly discuss
any discrepancies.
Data:
Diameter (x) Circumference (y)
1.38 cm 4.08 cm
3.80 cm 4.70 in
1.56 in 12.5 cm
2.06 in 6.43 in
7.28 cm 8.57 in
% error = Actual-Experimental
Actual
x 100
Copyright Catalyst Education 2019
Copyright Catalyst Education 2019
Graphing Data Set B
Name:____________________________ TA Name/Lab Section:_________________________
Hookworms are parasitic nematodes that live in the intestines of a host. Hookworms do their damage
(often fatally) by draining blood from the intestinal wall. The data below correlate the number of
hookworms and the amount of blood lost by that number of worms. (Include units below if applicable.
If necessary, use a separate sheet of paper for 6c and 7c.)
1. Inspect the data below and estimate the amount of blood lost due to one hookworm: ________
2. Prepare a hand-drawn plot of the two variables on the reverse side of this worksheet. Include a title, axis
labels (with units), and a trendline.
Estimate the total blood volume lost per day when 74 hookworms are present in the intestine:________
Estimate the number of hookworms that would drain 38.2 cmP3P of blood per day:______________
3. a. Prepare a plot using graphing software. Include a title, axis labels (with units), the equation of the best-fit
line and the RP2P value on the graph.
b. Re-write the equation of the best-fit line substituting “Number of Hookworms” for x and “Blood Volume”
for y directly on the graph.
c. Attach the fully labeled graph to this worksheet.
4. Determine the volume of blood consumed by a single hookworm based on the equation for the best-fit line.
________
5. Determine the percent error using the definition of percent error:
Use 0.48 mL/hookworm for the actual volume of blood.
% Error = _____________________
6. Using your computer-generated graph,
a. visually estimate the total blood volume lost per day when 74 hookworms are present: ___________.
b. calculate the volume of blood lost using the equation of the best fit line: __________. Use the graph to
ensure that this value is reasonable.
c. compare the calculated blood volume to the two visually interpolated values (Steps 2 and 6a). Comment
on any discrepancies.
7. Using your computer-generated graph,
a. visually estimate the number of hookworms draining 38.2 cmP3P of blood per day: _______________.
b. calculate the number of hookworms using the equation of the line: _________. Use the graph to ensure
that this value is reasonable.
c. compare the calculated number of hookworms to the two visually interpolated values (Steps 2 and 7a).
Comment on any discrepancies.
Data:
Number of hookworms (x) Blood lost per day (y)
24 12.2 mL
5 2.48 cmP3
45 1.37 inchP3
88 1.49 oz
80 39.8 cmP3
63 31.5 mL
% error = Actual-Experimental
Actual
x 100
Copyright Catalyst Education 2019
Copyright Catalyst Education 2019
Graphing Data Set C
Name: __________________________ TA Name/Lab Section: ___________________________
For the questions below, include units if applicable. If necessary, use a separate sheet of paper for 1,
6c and 7c.
Tire pressure is in part a function of the temperature of the tire.
1. Based on everyday experience, state (in words) the relationship between tire pressure and temperature. Look
at the data below and see if the numbers support your statement.
2. Prepare a hand-drawn plot of the two variables on the reverse side of this worksheet. Include a title, axis
labels (with units), and a trendline.
Estimate the tire pressure when the temperature is 18.6°C:__________.
Estimate the temperature of the air in the tire when the pressure is 37.0 psi: __________
3. a. Prepare a plot using graphing software. Include a title, axis labels (with units), the equation of the best-fit
line and the RP2P value on the graph.
b. Re-write the equation of the best-fit line substituting “Temperature” for x and “Pressure” for y directly on
the graph.
c. Attach the fully labeled graph to this worksheet.
4. What is the value of the slope for the relationship between temperature and pressure? ___________
5. Determine the percent error using the definition of percent error:
Use 0.145 psi/°C for the “Actual” value of the slope.
% Error = _____________________
6. Based on your computer-generated graph,
a. visually estimate the tire pressure when the temperature is 18.6°C: _______________.
b. calculate the tire pressure at this temperature using the equation of the best fit line: __________. Use
the graph to ensure that this value is reasonable.
c. compare the calculated pressure to the two visually interpolated values (Steps 2 and 6a). Comment on
any discrepancies.
7. Based on your computer-generated graph,
a. visually estimate the temperature of the air in the tire when the pressure is 37.0 psi: ___________
b. calculate the temperature of the air in the tire at this pressure: __________. Use the graph to ensure that
this value is reasonable.
c. compare the calculated temperature to the two visually interpolated values (Steps 2 and 7a). Comment
on any discrepancies.
Data:
Temperature (x) Tire Pressure, psi (y)
12.9 °C 3.39 x 10P1
15.4 °C 34.25
-2.10°F 2.68 x 10P1
19.5 °C 3.50 x 10P1
29.6 °F 36.53
% error = Actual-Experimental
Actual
x 100
Copyright Catalyst Education 2019
