# Graphical Analysis of Data Using Microsoft Excel Lab Reports

Santa Monica CollegeChemistry 11
Graphical Analysis of Data using Microsoft Excel©
Introduction
Graphs are useful tools that can elucidate key relationships in collected experimental data.
First, plotting a graph provides a visual image of data and any trends therein. Second, upon
analysis, they can provide us with the ability to predict the results of any changes to the system.
In this exercise, you will learn how to use Microsoft Excel© to manipulate and graph quantitative
data in a variety of ways. If needed, you can download a free copy of Excel 365 to your
personal device from the freely available Office 365 (provided by SMC) by signing in at:
http://www.smc.edu/TechnologyResources/StudentEmail/Pages/Office365.aspx
You can also use Microsoft Excel© 2016 or 2019, which are very similar. Furthermore, Excel
365 is installed on all computers available for student use in the various SMC computer centers.
Part 1: Simple Linear XY Plot
The relationship between a set of x-data and y-data can be easily determined by creating a
simple XY plot. The x-data – which is controlled by the experimenter – is gradually changed,
and the effect of these changes on the y-data may then be readily observed in the plot. Often
these plots will produce a straight line. Recall that whenever there is a direct, linear relationship
between the plotted x- and y-data, that data may be fitted to the equation of a line with the form
y = mx + b, through a technique known as linear regression. This is shown in Figure 1 below.
This equation expresses the mathematical relationship between the x- and y-data, and allows
for the prediction of unknown values. In Part 1, you will learn how to prepare a simple XY plot
and perform a linear regression using Excel.
best-fit line
y data
Best-fit line Equation: y = mx + b
b
y
x
b = y-intercept
m = slope = y/x = y2-y1/x2-x1
x data
Figure 1
Scenario – An experiment is designed to measure the pressure of 0.100 moles of neon gas at a
variety of different temperatures, while keeping the gas in a sealed 5 L steel container:
Temperature (K)
283
323
363
403
443
Pressure of Neon (torr)
345
409
446
512
542
a) Launch Excel and open a blank workbook.
Graphical Analysis of Data using Microsoft Excel
Page 1 of 8
Santa Monica College
Chemistry 11
b) Enter the 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 the experimenter has control of) goes on the
x-axis while the dependent variable (the measured data) goes on the y-axis.
c) Highlight the set of data – not the column labels – that you wish to plot (Figure 2). Click on
the Insert tab (top left), then go to Charts, then Scatter (Figure 3). Choose the scatter
graph that shows data points only, with no connecting lines – the option labeled Scatter
with Only Markers (Figure 4).
Figure 2
Figure 3
Figure 4
A plot should appear on your Excel screen, which is a preview of your graph (Figure 5).
Figure 5
d) If all looks well, it is time to add titles and label the axes of your graph (Figure 6, next page).

First, click inside the chart.
Switch to the Design tab, and click Add Chart Element > Chart Title > Above Chart.
The graph should be given an appropriate title that starts out “Y versus X” followed by a
Again, click on Add Chart Element > Axis Titles (first select Primary Horizontal Axis
Title and then Primary Vertical Axis Title) to add labels to the x- and y-axes. Note that it
is important to label axes with both the measurement and the units used.
To change the titles, click the text box for each title, highlight the text and type in your
new title.
Graphical Analysis of Data using Microsoft Excel
Page 2 of 8
Santa Monica College
Chemistry 11
Figure 6
e) Your next step is to add a trendline to these plotted
data points. A trendline represents the best possible
linear fit to your data. To do this you first need to
“activate” the graph by clicking on any one of the data
points. When you do this, all the data points will
appear highlighted.

f)
Click the Chart Elements button
next to the
upper-right corner of the chart.
Check the Trendline box.
Click More Options. This will display the option
shown in Figure 7.
Notice that the “Linear” button is already selected.
Now select the “Display Equation on Chart” box
and the “Display R-squared value on Chart” box.
Then click “Close”.
The equation that now appears on your graph is the
equation of the fitted trendline. The R2 value gives a
measure of how well the data is fit by the equation.
The closer the R2 value is to 1, the better the fit.
Generally, R2 values of 0.95 or higher are considered
good fits. Note that the program will always fit a
trendline to the data no matter how good or awful the
data is. You must judge the quality of the fit and the
suitability of this type of fit to your data set.
Figure 7
g) Click on your graph, then print out a full-size copy and attach it your Excel Report form. The
title, axis labels, trendline, equation and R2 value should all be easy to see on your graph.
Graphical Analysis of Data using Microsoft Excel
Page 3 of 8
Santa Monica College
Chemistry 11
h) Notice that by graphing just five sets of measured values, a relationship is established
between gas pressure and temperature. The graph contains a visual representation of that
relationship (the plot) as well as a mathematical expression of it (the equation). It can now
be used to make certain predictions. For example, suppose the sample of neon gas is
cooled until its pressure drops to 181 torr. You are asked to determine the gas temperature.
Note that the value “181 torr” falls outside the range of the plotted data. How can you find
the temperature if it doesn’t fall between the known points? There are two ways to do this.
Method (1): Extrapolate the trendline and estimate where the point on the line is.

Click again on the Chart Elements button
next to the upper-right corner of the chart,
then on Trendline and then More Options.
In the section labeled “Forecast” enter a number in the box labeled “Backward”, since
we want to extend the trendline the backward x direction. To decide what number to
enter, look at your graph to see how far back along the x-axis you need to go in order to
cover the section where pressure = 181 torr. After entering an appropriate number, click
“Close”, and the line on your graph should now be extended in the backward direction.
Now use your graph to estimate the x value by envisioning a straight line down from y =
181 torr to the x-axis. Record this value on your report.
Method (2): Plug this value for volume into the equation of the trendline and solve for the
report. This method is more precise than extrapolating and “eye-balling” from the graph.
Part 2: Two Data Sets with Overlay
Scenario – A spectrometer is used to measure the light absorbance of several solutions
containing different quantities of a red dye. Two sets of data are collected:
Dataset A
Dataset B
Amount of Dye (mol)
Absorbance (no units)
Amount of Dye (mol)
Absorbance (no units)
0.100
0.200
0.300
0.400
0.500
0.600
0.700
0.750
0.049
0.168
0.261
0.360
0.470
0.590
0.700
0.750
0.800
0.850
0.900
0.950
0.620
0.440
0.285
0.125
You would like to see how these two sets of data relate to each other. To do this you will have
to place both sets of data, as independent relationships, on the same graph. Note that this
process only works when you have the same axis values and magnitudes.
a) Enter this new data on a fresh page (Sheet 2) into 4 columns in Excel. Be sure to label your
data columns and remember to enter the x values to the left of the y values for each dataset.
b) First, plot Dataset A as a simple XY plot, following the same procedure as you did in Part 1.
Fit a trendline to this data, and obtain the equation of this line.
Graphical Analysis of Data using Microsoft Excel
Page 4 of 8
Santa Monica College
Chemistry 11
c) Now add Dataset B to this graph.

Activate the graph by clicking on one of the plotted data points.
Right-click the chart, and then choose Select Data. The Select Data Source box
appears on the worksheet with the source data of the chart.
Click the Add tab and type “Dataset B” for the Series Name.
Click the little icon on the right of the box “Series X values”, then highlight the x-axis
values of Dataset B. Press enter, then repeat this procedure for the “Series Y Values”,
highlighting the y-axis values of Data B. For each of these steps, you should see the
display shown in Figure 8. Note that slight differences may appear due to the version of
Microsoft Excel installed on your computer.
Figure 8
d) Click OK twice to return to the main Excel window. At this point you should see the new
data points similar to what is shown in Figure 9. You can now independently analyze this
Dataset B by inserting a trendline and obtaining the best-fit equation.
Figure 9
e) Complete your graph by adding axis labels (with units) and an appropriate chart title. Then
click on your graph, print out a full-size copy, and attach it to your Excel Report form. The
title, axis labels, trendlines and best-fit equations for both datasets should all be easy to see
f)
Notice that if these trendlines were extrapolated, they would intersect. Determine the values
of x and y for the point of intersection using simultaneous equations. Record your answers
Graphical Analysis of Data using Microsoft Excel
Page 5 of 8
Santa Monica College
Chemistry 11
Part 3: Statistical Analysis and Simple Scatter Plots
When many independent, repeated measurements are made for one variable, there is inevitably
some scatter (noise) in the data. This is usually the result of random errors over which the
experimenter has little control.
Scenario – Ten different students at two different colleges each measure the sulfate ion
concentration in a sample of tap water (measurements are in parts per million, or ppm):
Santa Monica College (SMC)
Sulfate Concentration (ppm)
35.9
41.4
32.8
35.1
33.2
37.6
27.7
36.6
35.0
39.3
El Camino College (ECC)
Sulfate Concentration (ppm)
45.1
34.2
36.8
31.0
40.7
29.6
35.4
32.5
43.5
38.8
Simple statistical analyses of these datasets might include calculations of the mean and
median concentration, and the standard deviation. The mean ( x ) is simply the average
value, defined as the sum () of each of the measurements (xi) in a data set divided by the
number of measurements (N):
x=
x
i
N
The median (M) is the midpoint value of a numerically ordered dataset, where half of the
measurements are above the median and half are below. The median location of N
measurements can be found using:
M = (N + 1) 2
This formula yields an integer that represents the value corresponding to the median location in
an ordered distribution of measurements. For example, in the set of numbers (3 1 5 4 9 9 8) the
median location is (7 + 1) / 2, or the 4th value. When applied to the numerically ordered set (1 3
4 5 8 9 9), the number 5 is the 4th value and is thus the median – three scores are above 5 and
three are below 5.
Standard deviation (s) is a measure of the variation in a dataset, where:
(x − x )
s=
2
i
N −1
Thus, to find s, subtract each measurement from the mean, square that result, add it to the
results of each other difference squared, divide that sum by the number of measurements minus
one, then take the square root of this result. The larger this value is, the greater the variation in
the data, and the lower the precision in the measurements.
Graphical Analysis of Data using Microsoft Excel
Page 6 of 8
Santa Monica College
Chemistry 11
While the mean, median and standard deviation can be calculated by hand, it is often more
convenient to use a calculator or computer to determine these values. Microsoft Excel© is
particularly well suited for such statistical analyses, especially on large datasets.
a) Enter the data acquired by the students from SMC only into a single column of cells on a
fresh page (Sheet 3) in Excel. Then in any empty cell (usually one close to the data cells),
instruct the program to perform the required functions on the data. To compute the mean or
average of the data entered in cells a1 through a10, for example, you must:

click the mouse in an empty cell
type “=average(a1:a10)”
and press return
To obtain the median you would instead type “=median(a1:a10)”. To obtain the standard
deviation you would instead type “=stdev(a1:a10)”.

The Excel-calculated mean, median and standard deviation for the SMC dataset.
As an additional exercise (for fun?), calculate the standard deviation of this dataset by
hand, and compare it to the value obtained from using Excel.
Rejecting Outliers
Do all the measurements in the SMC dataset look equally good to you, or are there any values
that do not seem to fit with the others? If so, are you allowed to reject these measurements?
Outliers are data points which lie far outside the range defined by the rest of the measurements
and may skew your results to a great extent. If you determine that an outlier resulted from an
obvious experimental error (e.g., you incorrectly read an instrument or prepared a solution), you
may reject the point without hesitation. If, however, none of these errors is evident, you must
use caution in making your decision to keep or reject a point. One rough criterion for rejecting a
data point is if it lies beyond two standard deviations from the mean or average.
c) Using the above criteria, determine if there are any outliers in the SMC dataset.

Then, excluding the outliers, re-calculate the mean, median and standard deviation of
this SMC dataset (using Excel).
Rejecting data points may not be done just because you want your data to look better. If
you choose to reject an outlier for any reason, you must always clearly document in your lab
report:

that you did reject a point
which point you rejected
why you rejected it
Failure to disclose this could constitute scientific fraud.
Graphical Analysis of Data using Microsoft Excel
Page 7 of 8
Santa Monica College
Chemistry 11
Graphing a Scatter Plot
Experimental
Measurements
Unlike the linear XY plots created so far, a scatter plot simply shows the variation in
measurements of a single variable in a given dataset, i.e., it supplies a visual representation of
the “noise” in the data. The data is usually plotted in a column, as shown in Figure 10. Note
that datasets with a greater degree of scatter will have a higher standard deviation and consist
of less precise measurements than datasets with a small degree of scatter.
Data set 1
Data set 2
Figure 10
To obtain such a plot using Excel, all the x values for each dataset must be identical. Thus, let
the SMC measurements be assigned x = 1, and let x = 2 for all the ECC measurements:
Measurements by Students from SMC
SMC
Conc (ppm)
1
35.9
1
41.4
1
32.8
1
35.1
1
33.2
1
37.6
1
27.7
1
36.6
1
35.0
1
39.3
Measurements by Students from ECC
ECC
Conc (ppm)
2
45.1
2
34.2
2
36.8
2
31.0
2
40.7
2
29.6
2
35.4
2
32.5
2
43.5
2
38.8
d) Enter the data as shown above into four columns of your spreadsheet.

Plot the SMC dataset as a simple XY plot (as learned in Part 1).
Now add the ECC dataset to this graph by applying the same steps you used to create
Add appropriate axis labels and a title. You may want to add a Legend (under Chart
Elements) to identify which dataset was produced by which college. You may also want
to adjust the x-axis and y-axis scales to improve the final look of your graph.
e) Click on your graph, print out a full-size copy, and attach it to your Excel Report form. The
title, axis labels, and both datasets should all be easy to see on your graph.

Which dataset (SMC or ECC) shows more scatter? Which contains the more precise
measurements? Which will have the larger standard deviation?
Graphical Analysis of Data using Microsoft Excel
Page 8 of 8
Santa Monica College
Chemistry 11
The Densities of Solids and Solutions
Objectives
The objectives of this laboratory are as follows:

To determine the density of distilled water using different types of volumetric glassware in
order to compare the precision of this glassware.

To determine the density of a de-gassed soda via graphical analysis.

To determine the density of an unknown metal using the technique of water displacement,
and use this value to identify the metal.

To use the program Microsoft Excel© to perform graphical analysis of experimental data.
Background
Density is a fundamental physical property of matter. Physical properties are characteristics of
a substance that can be observed or measured without changing its chemical identity. Other
physical properties include melting point and solubility. In general, since different substances
have unique densities, determining the density of an unknown substance can help identify it.
Density is also an intensive property of matter. An intensive property is one that does not
depend of the amount of matter present. In other words, a substance will have the same
density whether its quantity is large or its quantity is small. An extensive property, in contrast, is
one that does depend on the amount of matter present.
Density is specifically defined as the ratio of a substance’s mass to its volume:
Density =
Mass
Volume
The S.I. unit of density is kg/m3, but in chemistry it is more often expressed in units of g/cm3 for
solids, and g/mL for liquids and solutions. Note that while both mass and volume are extensive,
since density is a ratio of these properties, density is intensive.
In this lab, several different techniques will be employed to determine the density of water (in
Part A), a de-gassed soda (in Part B) and an unknown metal (in Part C). In Parts A and B of
this lab, the experimental data collected will be graphically analyzed, both to provide a visual
image of the data (and any trends therein) and to yield important physical values (density). The
program Microsoft Excel© will be used for this purpose. Students should already have explored
the graphing capabilities of Excel in a separate exercise, and those skills learned will be applied
here. In addition, in Part A a study of density datasets will be performed in order to explore the
concept of precision and the statistical nature of experimental data.
The Densities of Solids and Solutions
Page 1 of 6
Santa Monica College
Chemistry 11
Part A: In this section of the lab, datasets of water density values will be experimentally
obtained and then analyzed, in order to explore the concept of precision and the statistical
nature of experimental data.
Density
Specifically, the density of distilled water will be determined using measurements obtained from
three different types of volumetric glassware (VG): a 50-mL buret, a 100-mL graduated cylinder
and a 10-mL volumetric pipet. Measurements performed by each pair of students in the entire
class will be pooled so that a large ensemble of density values is acquired for each type of
glassware used. The three density datasets will be displayed in a scatter plot, as shown below:
VG 1
VG 2
VG 3
Volumetric Glassware (VG) Used
The datasets will be analyzed for outliers, which will be removed if identified. Recall that when
measurements in a dataset are closely examined, occasionally one or more values may appear
not to “fit in” with the others. These points are called outliers – values that occur far outside the
range defined by the rest of the measurements. One rough criterion for identifying an outlier is
that it lies beyond two standard deviations from the average value. Such values may be
legitimately excluded from a dataset, as they can skew results to a great extent.
Finally, simple statistical analyses of the three datasets will be performed, including calculations
of average density and standard deviation. An average value ( x ) is defined as the sum () of
each of the measurements (xi) divided by the number of measurements (N):
x=
x
i
N
Standard deviation () is defined as:
 =
(x − x )
2
i
N −1
Standard deviation indicates the degree to which a set of measurements deviate from the
average value. Datasets with a large amount of scatter will have a higher standard deviation
and are associated with less precise measurements compared to datasets with little scatter
(greater reproducibility). Thus, using these results, the precision of the measurements obtained
using the three types of volumetric glassware can be compared.
The Densities of Solids and Solutions
Page 2 of 6
Santa Monica College
Chemistry 11
Part B: In this section of the lab, the density of a de-gassed soda will be determined via
graphical analysis of a series of mass and volume data. A de-gassed soda is soda with the
gaseous carbon dioxide removed, typically by gently heating and stirring an open container of
soda over several days.
Using a buret to dispense precise solution volumes, the masses of several increasingly larger
volumes of a de-gassed soda will be measured. This collected mass and volume data will then
be plotted on a graph of “Soda Mass versus Soda Volume” using Microsoft Excel©. A best-fit
trendline will be applied to the plotted data (via linear regression), and the equation of the line
obtained.
Mass
Best-fit line
y
x
Volume
Best-fit line equation: y = mx + b
where b = y-intercept and m = slope
The y-intercept (b) is the point where the line crosses the y-axis. In this experiment, the value of
b should be equal to zero. This is because if there is no volume, the mass must also be zero.
However due to random experimental error, the best-fit line might not pass exactly through the
origin, but it should be quite close.
The slope of the line (m) is the change in the y-axis values (y) divided by the change in x-axis
values (x):
m=
y y1 − y 2
=
x x1 − x2
However according to the graph, since y is actually the change in mass (mass), and x is
actually the change in volume (volume), the slope of the best-fit line will yield the density of the
soda:
m=
The Densities of Solids and Solutions
y
mass
=
= density
x volume
Page 3 of 6
Santa Monica College
Chemistry 11
Part C: In this section of the lab, the density of an unknown metal will be determined and the
metal identified using this experimental value. Although a simple approach is used, this method
can yield density results accurate to 0.1%.
Using a capped glass vial, the following series of four mass measurements will be obtained:
(A) empty vial
(B) vial + metal
(C) vial + metal + water
(D) vial + water
The difference in masses A and B yields the mass of the metal sample. The volume of the
metal may be obtained by taking the difference between the water volumes in C and D
(technique of water displacement – Archimedes Principle). However, these water volumes must
first be calculated using the water masses and the known density of water (see table below).
Finally, density can be calculated using the metal mass and metal volume.
Density of Liquid Water Measured for a Range of Temperatures
(obtained from the CRC Handbook of Chemistry and Physics, 53rd edition)
Temperature (C)
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Density (g/mL)
0.9989
0.9988
0.9986
0.9984
0.9982
0.9980
0.9978
0.9975
0.9973
0.9970
0.9968
0.9965
0.9962
0.9959
In order to identify the unknown metal, the experimentally determined density must be
compared to the true (accepted) densities of several known metals. The percent error between
this experimental value (EV) and the true density value (TV) of the metal will also be calculated.
Percent Error =
| EV − TV |
 100
TV

Don't use plagiarized sources. Get Your Custom Essay on
Graphical Analysis of Data Using Microsoft Excel Lab Reports
Just from \$13/Page

## Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
\$26
The price is based on these factors:
Number of pages
Urgency
Basic features
• Free title page and bibliography
• Unlimited revisions
• Plagiarism-free guarantee
• Money-back guarantee
On-demand options
• Writer’s samples
• Part-by-part delivery
• Overnight delivery
• Copies of used sources
Paper format
• 275 words per page
• 12 pt Arial/Times New Roman
• Double line spacing
• Any citation style (APA, MLA, Chicago/Turabian, Harvard)

## Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

### Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

### Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

### Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.