Basic Graphing in Excel-2007
Table of Contents
- Entering and Formatting the Data in Excel
- Creating the Initial Scatter Plot
- Creating a Scatter Plot of Titration Data
- Adding Lines to the Scatter Plot
Introduction
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 into the appropriate cells.
- Title the spreadsheet page in cell A1
- Label Column A as the Concentration (M) of the known solutions in cell A3. This is the independent variable
- Label Column B as the Absorbance readings for each of the solutions in cell B3. This is the dependent variable
- Your data will go in the first two columns in the spreadsheet.
- Enter the independent and dependent variable values into cells A4:B9
- Finally, enter the information shown in rows 12 and 13. These are absorbance values from two samples of unknown concentrations (more on this later).
Figure 1a.
Lets now format the numbers
- Click and drag over the range of cells that will hold the concentration data (A4 through A9 for the sample data)
- Choose Home>Number... (this is shorthand for choosing Cells... from the Format menu at the top of the Excel window),
- Click on the Number tab
- Under Category choose Number and set Decimal places to 5
- Click OK
- Repeat for the absorbance data column (B4 through B9 for the sample data), setting the decimal places to 4
Figure 1b.
The concentration data is probably better expressed in scientific notation.
- Highlight the concentration data and choose Home>Number.... and click on the Number tab
- Choose the Scientific Category and set the Decimal places to 2.
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, in this case A3:B9, insert a chart from the Insert>Chart menu
- The type of chart you want is a XY-Scatter Chart using only markers (see Figure 3).
Figure 3.
The basic XY-Chart should appear on your worksheet.
Return to Top
The X-Axis should have concentration data, and the Y-Axis should have the absorbance data.
The next step adds titles to the X- and Y-Axes.
Select the Chart, then Chart Tools>Layouts Menu:
- Select Chart Tools>Chart Title option to change attributes of the title
- Click on the Title to edit &/or format the entry
- Select Chart Tools>Axis Titles>Primary Horizontal Axis option to add a X-Axis Title
- Click on Axis Title & enter Concentration (M) for the X Axis (Horizontal Axis)
- Select Chart Tools>Axis Titles>Primary Vertical Axis>Rotated option to add a Y-Axis Title
- Click on Axis Title & enter Absorbance for the Y Axis (Vertical Axis - Rotated Title)
- Do NOT forget units for your axes!
Chart Title
X-Axis Title (Horizontal Axis)
Y-Axis Title (Vertical Axis)
Then remove the Legend
- Click on the Legend
- Hit delete
Your should end up with a graph like the one below in Figure 5
Your XY-Scatter plot is now finished and should appear on the same spreadsheet page (called a worksheet) as your original data. 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 the titration of a strong acid with a strong base (see Figure 10 for the final plot). With this titration, a strong base (NaOH) of known concentration is added to a strong acid, HCl (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 free 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
- Using a new sheet in the spreadsheet workbook, enter your titration data as shown in Figure 6.
- Go to the Data Input Tutorial if you need hints on formatting the cells to the proper number of decimal places
Figure 6.
Now, create a scatter plot of titration data, just as you did with the Beer's Law plot (Figure 7).
- Highlight the titration data and the Column headers (A4:B16)
- Click on Insert>Chart>Scatter with only Markers
- Add appropriate Chart and Axes Titles (Layout>Chart Title, Layout>Chart Axis Titles)
- Select and Delete the Legend
- Your chart should look like the one below:
Figure 7.
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):
- Choose Insert>Scatter Chart - Scatter with smooth lines and markers
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