Basic Graphing in Excel-2003

Table of Contents

  1. Entering and Formatting the Data in Excel
  2. Creating the Initial Scatter Plot
  3. Creating a Scatter Plot of Titration Data
  4. Adding Lines to the Scatter Plot


Beer's Law states that there is a linear relationship between concentration of a colored compound in solution and the light absorption of the solution. This fact can be used to calculate the concentration of unknown solutions, given their absorption readings. First, a series of solutions of known concentration are tested for their absorption level. Next, a scatter plot is made of this empirical data.

Entering and Formatting the Data in Excel

Type what is seen in Figure 1a in the appropriate cells.

Figure 1a.

Lets now format the numbers

Figure 1b.

Your data will go in the first two columns in the spreadsheet.

The concentration data is probably better expressed in scientific notation.

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

Figure 2.

Return to Top

Creating the Initial Scatter Plot

With the data you want graphed highlighted, start the chart wizard

Figure 3.

The first dialogue of the wizard comes up

Figure 4a.

Return to Top

The Data Range box should reflect the data you highlighted in the spreadsheet. The Series option should be set to Columns, which is how your data is organized (see Figure 4b).

Figure 4b.

The next dialogue in the wizard is where you label your chart (Figure 4c). (Instead select Chart>Layouts to add titles and legends to the graph).

Figure 4c.

Figure 4d.

Return to Top

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

Figure 4e.

The initial scatter plot is now finished and should appear on the same spreadsheet page (called a sheet) as your original data. Your chart should look like Figure 5. A few items of note:

Figure 5.

Go to the tutorial on creating regression lines to find out how to use a regression line with this scatter plot to calculate the concentrations of the two unknowns.

Return to Top

Creating an Initial Scatter Plot of Titration Data

In this next part of the tutorial, we will work with another set of data. In this case, it is of a strong acid-strong base titration (see Figure 10 for the final plot). With this titration, a strong base (NaOH) of known concentration is added to a strong acid (also of known concentration, in this case). As the strong base is added to solution, its OH- ions bind with the free H+ ions of the acid. An equivalence point is reached when there are an equal number of OH- & H+ ions in the solution (pH = 7). This equivalence point can be found with a color indicator in the solution or through a pH titration curve. This part of the tutorial will show you how to do the latter.

Note that there should be two columns of data in your spreadsheet:

Column A: mL of 0.1 M NaOH added

Column B: pH of the 0.1 M HCl / 0.1M NaOH mixture

Figure 6.

Now, create a scatter plot of titration data, just as you did with the Beer's Law plot (Figure 7).

Figure 7.

Continue through steps 2 through 4 of the Chart wizard:

The resulting plot should look like Figure 8:

Figure 8.

Return to Top

Adding lines to the Scatter Plot

All of the points of the titration data can be connected to form a smooth curve. With this approach, the curve is guaranteed to go through all of the data points. This option can be used if you have only one pH reading per amount of NaOH added. If you have multiple pH readings for each amount added on the scatter plot, you will not end up with a smooth curve. To add lines to the scatter plot (Figure 9):

Figure 9.

The result should look like Figure 10:

Figure 10.

This smooth, connected curve helps locate where the steep part of the curve passes through pH 7.

Return to Top