Basic Graphing in Excel
Table of Contents
- Enter=
ing=20
and Formatting the Data in Excel=20
- Creat=
ing=20
the Initial Scatter Plot=20
- Creat=
ing=20
a Scatter Plot of Titration Data=20
- Chang=
ing=20
the Scatter Plot to a Line Graph
Introduction
Beer's Law states that there is a linear =
relationship between concentration of a colored compound in =
solution and=20
the light absorption of the solution. This fact can be used to =
calculate=20
the concentration of unknown solutions, given their absorption =
readings.=20
First, a series of solutions of known concentration are tested =
for their=20
absorption level. Next, a scatter plot is made of this empirical =
data.=20
Entering and Formatting the Data in =
Excel
Open Excel and begin by formatting the =
spreadsheet=20
cells so the appropriate number of decimal places are displayed =
(see=20
Figure 1a).
- Click and drag over the range of cells that will =
hold the=20
concentration data (A5 through A10 for the sample data)=20
- Choose Format > Cells... (this is shorthand for =
choosing=20
Cells... from the Format menu at the top of the Excel window)=20
- Click on the Number tab=20
- Under Category choose Number and set Decimal places =
to=20
5=20
- Click OK=20
- Repeat for the absorbance data column (B5 through B10 for =
the=20
sample data), setting the decimal places to 4 =
![](3D"mhtml:file://E:\Graphing)
Figure 1a.
Your data will go in the first two =
columns in the=20
spreadsheet. Type what is seen in Figure 1b in =
the=20
appropriate cells.
- Title the spreadsheet page in cell A1=20
- Label Column A as the Concentration =
(M) of=20
the known solutions in cell A3. This is the independent =
variable=20
- Label Column B as the Absorbance =
readings=20
for each of the solutions in cell B3. This is the =
dependent=20
variable=20
- Enter the independent and dependent =
variable=20
values=20
- Finally, enter the information shown in=20
rows 12 and 13. These are =
absorbance=20
values from two samples of unknown concentrations (more on =
this=20
later).
Figure 1b.
The concentration data is probably =
better=20
expressed in scientific notation.
Figure 2.
Return to Top
Creating the Initial Scatter Plot
With the data you want graphed =
highlighted,=20
start the chart wizard
- Choose the Chart Wizard icon from the tool bar (see =
Figure=20
3 for two examples). If the Chart Wizard is not visible, you =
can also=20
choose Insert > Chart...
Figure 3.
The first dialogue of the wizard comes =
up
- Choose XY (Scatter) and the unconnected points =
icon=20
for the Chart sub-type (Figure 4a)
Figure 4a.
Return to Top
The Data Range box should reflect the =
data you=20
highlighted in the spreadsheet. The Series option should be set =
to=20
Columns, which is how your data is organized (see Figure =
4b).
Figure 4b.
The next dialogue in the wizard is where =
you label=20
your chart (Figure 4c)
- Enter Beer's Law for the Chart Title=20
- Enter Concentration (M) for the Value X Axis=20
- Enter Absorbance for the Value Y Axis
Figure 4c.
- Click on the Legend tab=20
- Click off the Show Legend option (Figure 4d) =
Figure 4d.
Return to Top
Keep the chart as object in =
Sheet=20
1 (the current sheet). See Figure 4e.
Figure 4e.
The initial scatter plot is now finished =
and=20
should appear on the same spreadsheet page (called a sheet) as =
your=20
original data. Your chart should look like Figure 5. A few items =
of=20
note:
- Your data should look as though it falls along a linear =
path=20
- Horizontal reference lines were automatically placed in =
your=20
chart, along with a gray background=20
- Your chart is highlighted with square 'handles' on the =
corners.=20
When your chart is highlighted, a special Chart floating =
palette=20
should also appear, as is seen in Figure 5. Note: If =
the Chart=20
floating palette does not appear, go to =
Tools>Customize...,=20
click on the Toolbars tab, and then click on the =
Chart=20
checkbox. If it still doesn't show up as a floating palette, =
it may be=20
'docked' on one of your tool bars at the top of the Excel =
window.=20
With your graph highlighted, you can click and drag the =
chart to a=20
wherever you would like it located on the spreadsheet page. =
Grabbing=20
one of the four corner handles allows you to resize the =
graph.=20
Note: the graph will automatically adjust a number of =
chart=20
properties as you resize the graph, including the font size of =
the=20
text in the graph. You may need to go back and alter these =
properties.=20
At the end of the first part of this tutorial, you will learn =
how to=20
do this.
Figure 5.
Go to the tutorial on creatin=
g=20
regression lines to find out how to use a regression line =
with this=20
scatter plot to calculate the concentrations of the two =
unknowns.
Return to Top
Creating an Initial Scatter Plot of =
Titration=20
Data
In this next part of the tutorial, we =
will work=20
with another set of data. In this case, it is of a strong =
acid-strong=20
base titration (see Figure 10 for the final plot). With this =
titration,=20
a strong base (NaOH) of known concentration is added to a strong =
acid=20
(also of known concentration, in this case). As the strong base =
is added=20
to solution, its OH- ions bind with the free H+ions of the acid. =
An=20
equivalence point is reached when there are no free OH- nor H+ =
ions in=20
the solution. This equivalence point can be found with a color =
indicator=20
in the solution or through a pH titration curve. This part of =
the=20
tutorial will show you how to do the latter.
Note that there should be two columns of =
data in=20
your spreadsheet:
Column A: mL of 0.1 M NaOH added
Column B: pH of the 0.1 M HCl / 0.1M =
NaOH=20
mixture
- Using a new sheet in the spreadsheet workbook, enter your=20
titration data as shown in Figure 6.=20
- Go to the Data=20
Input Tutorial if you need hints on formatting the cells =
to the=20
proper number of decimal places
Figure 6.
Now, create a scatter plot of titration =
data, just=20
as you did with the Beer'=
s=20
Law plot (Figure 7).
- Highlight the titration data and the Column headers =
- Click on the Chart wizard icon=20
- Choose XY (Scatter) and the Scatter Chart =
sub-type=20
Figure 7.
Continue through steps 2 through 4 of =
the Chart=20
wizard:
- The defaults for step 2 should be fine if you =
properly=20
highlighted the data=20
- In step 3 enter the chart Title and x and y =
axis=20
Labels and turn Off the Legend=20
- In step 4, leave as an object in the current page =
The resulting plot should look like =
Figure 8:
Figure 8.
Return to Top
Changing the Scatter Plot to a Line =
Graph
All of the points of the titration data =
can be=20
connected to form a smooth curve. With this approach, the curve =
is=20
guaranteed to go through all of the data points. This option can =
be used=20
if you have only one pH reading per amount of NaOH =
added. If=20
you have multiple pH readings for each amount added on the =
scatter plot,=20
you will not end up with a smooth curve. To change the scatter =
plot to a=20
(smoothed) line graph (Figure 9):
- Choose Chart > Chart Type...=20
- Select the Scatter connected by smooth lines Chart =
subtype=20
Figure 9.
The result should look like Figure =
10:
Figure 10.
This smooth, connected curve helps =
locate where=20
the steep part of the curve passes through pH 7.
Return to Top
|