Instructions for Creating a Beer¡¯s Law Plot:

Using Excel 2003

1. Open a new Excel Spreadsheet
2. Make the following labels: this is not absolutely necessary.
1. Cell A2: SI
2. Cell A3: S2
3. Cell A4: S3
4. Cell A5: S4
5. Cell A6: S5
6. Cell A7: S6
7. Cell B1: [FeNCS2+]
8. Cell C1: Absorbance
3. In Cells B2¡úB7 enter the [FeNCS2+] for each of the standard solutions (S-series)

Note: You need to enter either 0.00004 OR 4.0E-5 for S2

Highlight B2¡úB7, Click Format, Click Cells

Click the Number Tab

Choose Category: Scientific and Set Decimal Places to 2

Click OK

1. In Cells B2¡úB7 enter the Absorbance for each of the standard solutions (S-series)    Note: These numbers are entered like you normally would.

Follow the same formatting above, except choose Number and 3 Decimal places

Making the Graph:

1. Click Insert¡­ Click Chart¡­.

The Chart Wizard should pop-up.

1. STEP 1 of 4: Chart Type

Chart Type: XY (Scatter)

Chart Sub-Type: pick the one with NO lines, this should be the default

Click Next

1. STEP 2 of 4: Chart Source Data

Click the Series Tab

¡¤        Click the Select Range Button for X-values (this is the button that looks like a small spreadsheet on the right end of the X-values box.)

Drag and Highlight all 6 Cells under [FeNCS2+] (Cells B2¡úB7)

Hit Enter on the Keyboard

¡¤        Click in the Y-values box and REMOVE the {1}

Click the Select Range Button for Y-values

Drag and Highlight all 6 Cells under Absorbance (Cells C2¡úC7)

Hit Enter on the Keyboard

The ranges in the boxes SHOULD look something like:

X Values: =Sheet1!\$B\$2:\$B\$7

Y Values: =Sheet1!\$C\$2:\$C\$7

Click Next

1. STEP 3 of 4: Chart Options

Here is where you set most of the options for your graph

¡¤        Titles Tab

Chart Title: Beer¡¯s Law for FeNCS2+

Value(X) Axis: [FeNCS2+] (mol/L)

Value(Y) Axis: Absorbance

¡¤        Axes Tab

Both Boxes should be checked to show numbers on the X&Y axes

¡¤        Gridlines Tab

Both X&Y Major Gridlines should be turned ON

Both X&Y Minor Gridlines should be turned OFF

¡¤        Legends Tab

Make sure the box is unchecked so that NO legend will be displayed

¡¤        Data Labels Tab

Do not show data labels

Click NEXT

1. STEP 4 of 4: Chart Location

Default is to place the graph on the current sheet. This is fine.

Click FINISH

If ALL went well, you should now have a graph on your spread sheet! Congratulations!!!!

Editing the Graph for Scientific Use¡­..

1. Left Click a data point on your graph, then Right Click to open a Menu, and then Left Click on Add Trend line¡­..
2. A Window will pop up.

¡¤        Type Tab

Trend/Regression Type ¡ú Linear

¡¤        Options Tab

Trend line Name choose automatic

Forecast set to 0

Check all of the boxes for

Set Intercept = 0

Display Equation on Chart

Display R-squared value

¡¤        Click OK

You should now have a straight line going through the best statistical ¡®fit¡¯ to your data and you should have an equation and R2 value. NOTE: The line will most likely NOT pass through all of your data points.

We have the equation and R2, but they might be hard to see. Let¡¯s make it look better.

1. Editing the Equation Box
1. Double Left Click on the equation. This will pop up a window called Format Data Labels.

¡¤        Patterns Tab

Border set to Custom

Area set to Automatic, or choose white

¡¤        Number Tab

Choose Category: Scientific

Choose 3 decimal places

Click OK

Now, your equation and R2 should be more visible.

1. Double Left Click on one of the X-axis values

The Format Axis Window pops up

¡¤        Patterns Tab

Set ALL Ticks to ¡°cross¡± the axis.

This is done by clicking to highlight the little circles next to ¡°cross¡± for Major Ticks and Minor Ticks

¡¤        Scale Tab

This should be fine for Beer¡¯s Law. However, if you needed to make a plot of some other kind of data, you might have to change this.

¡¤        Font Tab

This should be OK.

¡¤        Number Tab

Category: Scientific   2 decimal places

¡¤        Alignment Tab

Set to Automatic

Click OK

1. Double Left Click on one of the Y-axis values

The Format Axis Window pops up

¡¤        Patterns Tab

Set ALL Ticks to ¡°cross¡± the axis.

This is done by clicking to highlight the little circles next to ¡°cross¡± for Major Ticks and Minor Ticks

¡¤        Scale Tab

This should be fine for Beer¡¯s Law. However, if you needed to make a plot of some other kind of data, you might have to change this.

¡¤        Font Tab

This should be OK.

¡¤        Number Tab

Category: Number   2 decimal places

¡¤        Alignment Tab

Set to Automatic

Click OK

Congratulations! If you followed these instructions and with a bit of luck you should have a nice looking scientific quality graph of your data.

Click somewhere OFF the graph.

Left Click the Chart you just made.

Click Print. This will create a full page chart in the Landscape orientation.