Santa Monica College
4/27/2020
1.1
1: Using Excel for Graphical Analysis of Data (Experiment)
Objectives
To
learn to use Excel to explore a number of linear graphical relationships.
In several upcoming labs, a primary goal will be to determine the mathematical relationship between two variable physical
parameters. Graphs are useful tools that can elucidate such relationships. First, plotting a graph provides a visual image of data
and any trends therein. Second, via appropriate analysis, they provide us with the ability to predict the results of any changes
to the system.
An important technique in graphical analysis is the transformation of experimental data to produce a straight line. If there is a
direct, linear relationship between two variable parameters, the data may be fitted to the equation of line with the familiar form
through a technique known as
linear regression
. Here
represents the slope of the line, and
represents the
y

intercept, as shown in the figure below. This equation expresses the mathematical relationship between the two variables
plotted, and allows for the prediction of unknown values within the parameters.
The equation for the bestfit line is
where
Computer spreadsheets are powerful tools for manipulating and graphing quantitative data. In this exercise, the spreadsheet
program Microsoft Excel
will be used for this purpose. In particular, students will learn to use Excel in order to explore a
number of linear graphical relationships. Please note that although Excel can fit curves to nonlinear data sets, this form of
analysis is usually not as accurate as linear regression.
Procedure
Part 1: Simple Linear Plot
Scenario:
A certain experiment is designed to measure the volume of 1 mole of helium gas at a variety of different
temperatures, while keeping the gas pressure constant at 758 torr:
©
Santa Monica College
4/27/2020
1.2
Temperature (K)
Volume of Helium (L)
203
14.3
243
17.2
283
23.1
323
25.9
363
31.5
1. Launch the program Microsoft Excel
(2016 version, found on all computers in all the computer centers on campus). Go to
the Start button (at the bottom left on the screen), then click Programs, followed by Microsoft Excel
.
2. Enter the above data into the first two columns in the spreadsheet.
Reserve the first row for column labels.
The x values must be entered to the left of the y values in the spreadsheet. Remember that the independent variable (the
one that you, as the experimenter, have control of) goes on the xaxis while the dependent variable (the measured data)
goes on the yaxis.
3. Highlight the set of data (not the column labels) that you wish to plot (Figure 1).
Click on
Insert > Recommended Charts
followed by
Scatter
(Figure 2).
Choose the scatter graph that shows data points only, with no connecting lines – the option labeled
Scatter with Only
Markers
(Figure 3).