From: "Saved by Windows Internet Explorer 8" Subject: Basic Graphing with Excel Date: Tue, 28 Dec 2010 12:26:56 -0700 MIME-Version: 1.0 Content-Type: multipart/related; type="text/html"; boundary="----=_NextPart_000_0057_01CBA68A.844A7180" X-MimeOLE: Produced By Microsoft MimeOLE V6.1.7600.16543 This is a multi-part message in MIME format. ------=_NextPart_000_0057_01CBA68A.844A7180 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Location: mhtml:file://E:\Graphing with Excel\Basic Graphing with Excel.mht Basic Graphing with Excel      Graphing with Excel

Basic Graphing in Excel

1. Enter= ing=20 and Formatting the Data in Excel=20
2. Creat= ing=20 the Initial Scatter Plot=20
3. Creat= ing=20 a Scatter Plot of Titration Data=20
4. 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 = 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.

• Highlight the concentration data and choose Format > = Cells....=20
• Choose the Scientific Category and set the Decimal = places=20 to 2.=20

The last step before creating the graph is to choose the = data you=20 want to graph.

• Highlight the data in both the concentration and = absorbance=20 columns (but not the unknown data)=20

This is shown in Figure 2. Figure 2.

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.

• Click Next >

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.

• Click Next >

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.

• Click Next >

Keep the chart as object in = Sheet=20 1 (the current sheet). See Figure 4e.

Figure 4e.

• Click Finish

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.

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.

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.