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: [FeNCS²⁺]
8. Cell C1: Absorbance
3. In Cells B2→B7 enter the [FeNCS²⁺] 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

4. 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

NOW, your Spreadsheet should look like mine. Your absorbances WILL be different. Therefore, your slope WILL be different.

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

2. STEP 2 of 4: Chart Source Data

Click the Series Tab

Click Add

· 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 [FeNCS²⁺] (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

3. 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 FeNCS²⁺

Value(X) Axis: [FeNCS²⁺] (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

4. 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. Add a Trend Line
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 R² value. NOTE: The line will most likely NOT pass through all of your data points.

We have the equation and R², but they might be hard to see. Let’s make it look better.

2. 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 R² should be more visible.

3. Adding Minor Ticks
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

2. 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.