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

brief description of your system.

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

unknown temperature. Record your answer and show your work for this calculation on your

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

on your graph.

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

and show your work for this calculation on your report.

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

b) Record on your report:

•

•

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.

•

•

Record your calculations and identify any outlier measurements on your report.

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

your graph in Part 2.

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

A more accurate experimental value will yield a lower percent error (< 5% is desirable) than a
less accurate value.
The Densities of Solids and Solutions
Page 4 of 6
Santa Monica College
Chemistry 11
Procedure
Part A: The Precision of Volumetric Glassware
Materials and Equipment: Distilled water (in wash bottle), 100-mL graduated cylinder,
50-mL beaker, 50-mL buret, 10-mL volumetric pipet, pipet bulb, buret stand, electronic balance
1. Weigh a dry 50-mL beaker on an electronic balance, and record this mass on your report
form. You will use this beaker and the same electronic balance for Steps 2, 3 and 4.
2. Graduated Cylinder: Fill the 100-mL graduated cylinder with slightly more than 10 mL of
distilled water from the wash bottle, then record the actual volume used to the correct
number of significant figures. Carefully transfer the distilled water into the small pre-weighed
50-mL beaker, and measure and record the combined mass. When finished, empty the
distilled water out of the small beaker, then carefully dry it.
3. Volumetric Pipet: Your instructor will demonstrate the correct use of the volumetric pipet and
pipet bulb at the beginning of the lab period. Use the volumetric pipet to transfer precisely
10.00 mL of distilled water from the supply in the wash bottle (with the top removed) into the
small pre-weighed 50-mL beaker. Record the volume used and the combined mass of the
beaker and water. Note that the pipet measures volume to 2 decimal places. Again, empty
the water out of the small beaker when finished, then carefully dry it.
4. Buret: Fill the buret about half-way with distilled water from your wash bottle, then let 1-2 mL
of this water drain out of the tip into the sink. Measure and record the buret “initial” reading
to the correct number of significant figures. Now drain slightly more than 10 mL of the water
from the buret into the small pre-weighed 50-mL beaker. Measure and record the buret
“final” reading to the correct number of significant figures. The actual volume used is the
difference between the final and initial buret readings – record this value as well as the
combined mass of the beaker and water on your report form.
5. When finished, clean up as directed by the instructor.
6. For each of the three sets of data you collected (using the graduated cylinder, volumetric
pipet and buret), calculate the density of distilled water to the correct number of significant
figures. Then share your three density values with all the students in your lab section, and
record the results of the entire class on your report form.
Part B: The Density of a De-gassed Soda
Materials and Equipment: Degassed soda, distilled water (in wash bottle), 100-mL beaker,
small funnel, 50-mL buret, 50-mL Erlenmeyer flask, buret stand, electronic balance
1. Prepare the buret by first rinsing it with distilled water, then rinsing it with a small quantity of
the soda, as demonstrated by the instructor.
2. Obtain about 70 mL of degassed soda in a medium 100-mL beaker. Using the funnel, fill
the buret with soda (from the beaker) to just above the zero mark, then drain out some of
the soda in the sink until the meniscus is between 1 and 5 mL. Secure the buret firmly in
place with the buret clamp/stand, and record the initial buret reading to the correct number
of significant figures.
The Densities of Solids and Solutions
Page 5 of 6
Santa Monica College
Chemistry 11
3. Weigh a dry 50-mL Erlenmeyer flask on an electronic balance, and record this mass. Use
this same balance for all subsequent mass measurements.
4. Carefully drain 5-6 mL of the soda from the buret into the 50-mL flask, and record the new
buret reading. Now measure and record the combined mass of the flask and soda.
5. Do not pour the soda out of the Erlenmeyer flask! Next, add an additional 5-6 mL of soda to
the flask from the buret. Again, measure and record the new buret reading and the new
combined mass of the flask and soda.
6. Repeat Step 5 four more times. You will have obtained a total of six measurements
involving increasing larger amounts of soda when you are finished. Then clean up as
directed by the instructor.
Part C: The Density of an Unknown Metal
Materials and Equipment: Unknown metal sample, empty capped glass vial, distilled water (in
wash bottle), electronic balance, thermometer
1. Obtain an unknown metal sample from your instructor. Record the ID Code of the metal on
your report form.
2. Carefully examine the empty capped glass vial. The cap on this vial should have a small
hole pierced through it. This hole will allow air and excess water to be expelled from the
vial. Weigh this empty, dry capped vial using an electronic balance. Use this same balance
for all subsequent mass measurements.
3. Add the entire sample of your unknown metal to the empty vial and weigh it again (with cap).
4. Now fill the vial (with the metal still in it) to the brim with distilled water. Gently tap the vial to
remove any air that might be trapped between the metal pieces. Place the cap on firmly,
pressing out excess water. No air bubbles should be visible under the cap. Wipe off any
drops of water on the outside of the vial, and then weigh it.
5. Next, remove the metal from the vial and then fill it to the brim with distilled water only.
Place the cap on firmly, wipe off excess water, and weigh. Again, no air bubbles should be
visible under the cap.
6. Finally, use the thermometer to measure the temperature of the water in the vial. Record
this value on your report form, and use it to obtain the density of water from the table on
Page 4.
7. When finished, dry the metal sample and return it to your instructor, and clean up as
directed.
The Densities of Solids and Solutions
Page 6 of 6
Santa Monica College
Chemistry 11
Gravimetric Analysis of an Unknown Sulfate
Objectives
The objectives of this laboratory are as follows:
To experimentally analyze an unknown sulfate salt via a precipitation reaction, using the
techniques associated with Gravimetric Analysis to collect and weigh the precipitate, and
To calculate the percentage by mass of SO4-2 in the unknown sulfate salt via a
stoichiometric analysis of the collected precipitate, and then use this percentage to identify
the metal “M” present in the sulfate salt.
Background
Gravimetric analysis is a quantitative method for accurately determining the amount of a
substance by selective precipitation of the substance from an aqueous solution. The precipitate
is separated from the remaining aqueous solution by filtration and is then weighed. Assuming
that the chemical formula for the precipitate is known and that the precipitation reaction goes all
the way to completion, then the mass of the substance in the original sample can be
determined.
In this experiment, the percentage by mass of sulfate in an unknown sulfate salt will be
determined by gravimetric analysis. First, a pre-weighed sample of the unknown sulfate salt will
be dissolved in water. Next, an excess of aqueous barium chloride is added to the aqueous
solution of the unknown salt. This will result in the precipitation of all the sulfate ions as barium
sulfate:
Metal sulfate (aq) + Barium chloride (aq) → Barium sulfate (s) + Metal chloride (aq)
The barium sulfate precipitate is collected by filtration, dried and weighed. Since barium
chloride is added in excess, and since the precipitation reaction goes to completion, we can
assume that all of the sulfate is transferred from the original unknown sample to the precipitate.
The mass of sulfate in the collected BaSO4 precipitate can be calculated via its percent
composition. This also yields the mass of sulfate in the original unknown since:
mass of sulfate in the precipitate = mass of sulfate in the unknown sample
Finally, using the mass of sulfate along with the initial mass of unknown used, the percentage
by mass of sulfate in the original sample may now be calculated.
In order to obtain the best results, the collected BaSO4 crystals should be as large as possible.
This considerably aids the filtration process (larger crystals are less likely to be pass through the
filter paper), and it also minimizes the amount of impurities adsorbed onto the crystals (smaller
surface area). In general, larger crystals are obtained when the rate of precipitation is as low as
possible. The rate of precipitation is minimized by slowly adding the BaCl2 solution to the
aqueous mixture containing the unknown salt while continuously stirring the mixture. The rate
of precipitation can be decreased even further by slightly increasing the solubility of the BaSO4.
This may be achieved by lowering the pH with 6M HCl and by increasing the temperature. The
resulting decrease in the yield of the BaSO4 is insignificant.
Gravimetric Analysis of an Unknown Sulfate
Page 1 of 3
Santa Monica College
Chemistry 11
Procedure
Chemicals
Unknown solid sulfate salt, 6M HCl solution and 0.1M BaCl2 solution
Equipment
250-mL beaker, analytical balance, stirring rod, 100-mL graduated cylinder, stand with ring
clamp, wire screen, Bunsen burner, wash bottle with distilled water, crucible and lid, crucible
tongs, ash-less filter paper, large funnel, 500-mL Erlenmeyer flask, clay triangle
Safety
Be very careful when handling 6M HCl (aq). If this acid comes in contact with your skin or eyes
you should immediately rinse the affected area with water for several minutes. Also remember
that items heated in the Bunsen burner are very hot (especially the crucible), and to allow ample
time for them to cool before touching.
Instructions
1. Weigh a clean, dry 250-mL beaker to the nearest 0.001 g using the analytical balance, and
record this mass on your lab report. Next, add 0.30 – 0.35 grams of your unknown sample
to the beaker. Record the combined mass of the beaker plus sample on your lab report.
2. Add 50 mL of distilled water, followed by 20 drops of 6M HCl (aq), to the sample in the
beaker. Stir the contents of the beaker until the sample has entirely dissolved. Leave the
stirring rod in the beaker.
3. Obtain a stand with a ring clamp from the back of the lab. Place your wire screen on the
ring and the beaker containing your dissolved sample on the wire screen. Use the Bunsen
burner to heat the solution until it is nearly (but not quite) boiling. Turn the Bunsen burner
off before the solution boils.
4. While heating the solution, measure out 25 mL of 0.1M BaCl2 (aq) using a 100-mL
graduated cylinder. The graduated cylinder used should be clean (rinse with distilled water)
but does not need to be dry.
5. Slowly add small portions of BaCl2 (aq) to the beaker containing the hot solution. You
should observe the formation of a white precipitate of BaSO4 (s). Stir the contents of the
beaker as you add the BaCl2 solution. The addition of the BaCl2 must be performed very
slowly – this step should take you at least 3 minutes to complete! When finished, rinse
any precipitate that remains on the stirring rod into the solution with a small amount of
distilled water, and then allow the precipitate to settle in the beaker for about 20 minutes.
6. While the precipitate settles, prepare your crucible by heating it in the hottest part of the
Bunsen burner flame for about 2 minutes (use the crucible tongs). Repeat with the lid.
Place the hot crucible and lid on the metal base of the stand to cool. Once they have cooled
to room temperature weigh the crucible without the lid using the analytical balance, and
record this mass on your lab report. You do not need to weigh the lid.
Gravimetric Analysis of an Unknown Sulfate
Page 2 of 3
Santa Monica College
Chemistry 11
7. Obtain a piece of ash-less filter paper from your instructor and fold it into quarters. Open the
folded paper into a cone and place it into your large funnel. Wet the filter paper with a small
amount of distilled water so that it adheres to the funnel. Sit the funnel in the mouth of a
500-mL Erlenmeyer flask, which will be used to collect the filtrate.
8. After 20 minutes has passed, slowly pour the mixture containing the BaSO4 precipitate down
your stirring rod into the funnel. Be careful that the level of liquid in the funnel is never more
than three-fourths of the way to the top of the filter paper. When the transfer is complete
use your wash bottle (filled with distilled water) to rinse the residual precipitate from the
beaker and the stirring rod into the funnel.
9. Wear gloves for this step. After all the liquid has drained from the funnel, very carefully
press the top edges of the filter paper together, and gently fold the filter paper into a
compact package that will fit into the crucible. It is important that you do not use too much
force in order to avoid tearing the filter paper. Place the folded filter paper into the crucible.
10. Take your stand, ring clamp and Bunsen burner over to a fume hood. Place your clay
triangle on the ring and the crucible in the clay triangle for support. Gently heat the
crucible without the lid to remove the water. Once the paper appears to be dry (after
several minutes), heat the crucible more vigorously so that the filter paper begins to
char (turning from white, to brown, to black) – but not so vigorously that the filter paper
bursts into flame. If the filter paper bursts into flame you should cover it with the crucible lid
to put out the flame, then reduce the amount of heat and remove the lid. Continue to heat
moderately without the lid until all of the filter paper has turned black.
11. Once all the filter paper has turned black, vigorously heat the crucible without the lid in
the hottest part of the Bunsen burner flame so that the bottom of the crucible is red hot.
The charred filter paper (carbon) will gradually combust and be converted into CO2 gas.
When the filter paper is entirely combusted only the white BaSO4 should remain in the
crucible. Continue to heat the crucible vigorously until no charred filter paper remains. This
should take about 10 minutes.
12. Allow the crucible to cool to room temperature. Weigh the crucible (without the lid) and its
contents on the analytical balance. Record this mass on your lab report.
13. Place the crucible and its contents back in the clay triangle and heat vigorously (without the
lid) for an additional 5 minutes. Then allow it to cool again and reweigh the crucible (without
the lid) and its contents. If the mass is within 0.005 grams of your mass obtained in step 12,
then record this mass on your lab report. If the mass has decreased by more than 0.005
grams, then either the BaSO4 is still wet or not all of the filter paper has combusted and you
should repeat this step until you achieve a consistent mass.
14. Discard the BaSO4 in the proper waste container, then clean up as directed by your
instructor.
Gravimetric Analysis of an Unknown Sulfate
Page 3 of 3
Santa Monica College
Chemistry 11
Name:
Date:
Lab Section:
Exercise: Graphical Analysis of Data using Microsoft Excel©
Part 1: Simple Linear XY Plot
•
Using Excel, prepare a linear XY plot of “Pressure vs Temperature” with the supplied data
as instructed. Print out a full-sized copy of this graph and staple it to this report.
•
Which data is plotted on the y-axis?
the x-axis?
•
Record the following information from your graph:
The equation of the fitted trendline
The value of the slope of this line
The value of the y-intercept of this line
•
Is the fit of the trendline to your data good (circle one)? Yes / No
Explain your response.
•
Determine the temperature (in K) of the cooled neon gas when it has a pressure of 181 torr:
a) via extrapolation and estimating
b) via the equation of the trendline
Show your calculation for (b) in the space below.
Part 2: Two Data Sets and Overlay
•
Using Excel, prepare an XY plot of “Light Absorbance vs Amount of Red Dye” with the
supplied data as instructed, showing two sets of linear data simultaneously. Print out a fullsized copy of this graph and staple it to this report.
•
Record the equations of the trendlines fitted to
Dataset A:
Dataset B:
Graphical Analysis of Data using Microsoft Excel
Page 1 of 2
Santa Monica College
Chemistry 11
Part 2 Continued
•
Perform a “simultaneous equations calculation” to determine the x and y values for the point
of intersection between these lines. Show your work in the space below.
Part 3: Statistical Analysis and Scatter Plot
•
For the SMC dataset (only), determine the following values using Excel:
Mean SO4-2 concentration
•
Median SO4-2 concentration
Are there any outliers in the SMC dataset (circle one)?
Standard deviation in dataset
Yes / No
If yes, which measurements are the outliers?
Show the calculations you used to identify the outliers in the space below.
Exclude the outliers and re-calculate the following values for the SMC dataset using Excel:
Mean SO4-2 concentration
Median SO4-2 concentration
Standard deviation in dataset
•
Using Excel, prepare a Scatter Plot with the supplied data as instructed, showing both the
SMC and ECC datasets simultaneously. Print out a full-sized copy of this graph and staple
it to this report.
•
Which dataset (SMC or ECC):
Shows more scatter (less reproducibility)?
Has the larger standard deviation?
Contains the more precise measurements?
Graphical Analysis of Data using Microsoft Excel
Page 2 of 2

Don't use plagiarized sources. Get Your Custom Essay on

Graphical Analysis of Data Using Microsoft Excel Lab Reports

Just from $13/Page

The price is based on these factors:

Academic level

Number of pages

Urgency

Basic features

- Free title page and bibliography
- Unlimited revisions
- Plagiarism-free guarantee
- Money-back guarantee
- 24/7 support

On-demand options

- Writer’s samples
- Part-by-part delivery
- Overnight delivery
- Copies of used sources
- Expert Proofreading

Paper format

- 275 words per page
- 12 pt Arial/Times New Roman
- Double line spacing
- Any citation style (APA, MLA, Chicago/Turabian, Harvard)

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.

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.

Read moreEach 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.

Read moreThanks 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.

Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more