EXCEL WORKSHOP (A "Computer Lab" period for Chemistry 20L) EXCEL is a very popular spreadsheet software program, which allows the user to perform many kinds of statistical analyses of the data. It can also perform a variety of mathematical calculations. In addition, EXCEL can plot data in a variety of formats. During the workshop, each of you will have a chance to practice preparing graphs from rate data. The goal of the workshop is to familiarize you with the EXCEL software so that you can use it to process your experimental data in chemistry and other classes. You will work in pairs in the computer lab. Before you can use the software, you need to open it. Go to “START” (located at the bottom left corner of the screen) and select “PROGRAMS”. Click on “Microsoft EXCEL”. NOTE: There is NO NEED to save any files based on the data in this handout that you will work on during the workshop period. There are, in general, three different steps when working with EXCEL. Data entry, data analysis, and graphing. Step (I) Data Entry – Enter the raw rate data (found on the next page) for the reaction of Brilliant Blue FCF with hypochlorite. Follow the instructions below to enter the data. On the spreadsheet, notice that the columns in EXCEL are labeled as A, B, C … etc. The rows of the spreadsheet are labeled as 1, 2,3, etc. Each cell (i.e. a rectangular box) is defined by the location of the row and column in EXCEL. For example, the very first cell located on the upper left corner has an address of A1 in EXCEL. Before you start entering the data, you may want to increase the width of the columns. For example, if you want to increase the width of column A, point the cursor to the letter A and click on the left button of the mouse. The entire column A should now be highlighted. Go to the menu bar and click on “FORMAT”. Select “COLUMN” and then select “WIDTH”. A dialog box will appear. Type in a number that is greater than the original number shown to increase the width of the column. To decrease the width of the column, type in a smaller number. You can always go back and increase the column width anytime even after you enter the data. (Alternatively, you can increase the width of a column by moving the cursor to the edge of a cell until you see the cursor change to a symbol with horizontal arrows. Now holding the mouse button down, drag the cursor to the left or right to give you the column width that you want.) Now that the columns are the width you want, it is time to start entering the data. Move the cursor (or cross) to the cell with an address A1 (i.e. the very first cell in the spreadsheet). Type “Time”. Move the cursor to cell B1 and type “Absorbance”. These are the labels or titles for each of the columns. Now go to cell A2 and start entering the time data in the table on the next page. When you are finished entering the data for time, enter the data for the Absorbance (starting with cell B2). Your spreadsheet at this point should look like the table on the next page. 1Sample data for the reaction of Brilliant Blue FCF with hypochlorite (molar absorptivity, ε for Brilliant Blue FCF 1.38 x 105 M cm-1) A B C D E 1 Time Absorbance 2 0 1.72 3 30 1.29 4 60 0.87 5 90 0.58 6 120 0.39 7 150 0.25 8 180 0.17 9 210 0.11 10 240 0.073 11 270 0.048 12 300 0.032 13 330 0.021 14 360 0.014 15 390 0.009 16 420 0.007 17 450 0.004 You are now almost ready to ask EXCEL to do some simple calculations (see step (II) below). But before you can generate the data for column C (concentration of dye) column D (ln dye concentration) and column E (1/concentration of dye) type in the headings “[Dye]” into cell C1 and “ln [Dye]” into cell D1 and “1/[Dye]” into cell E1. Step (II) Data Analysis – This is where you will find how valuable a spreadsheet program can be. You will use the program to calculate the concentration of the dye from the Absorbance data for column C, the natural log of the concentration for column D and the reciprocal of the concentration for column E Place your cursor in cell C2. This is where you want to place the FIRST entry for the concentration at time 0. To calculate the concentration, you will need to use the Beer’s Law equation, A = εbC where A is the absorbance; ε is the molar absorptivity, (1.38 x 105 M cm-1 for Brilliant Blue FCF), b is the path length (1 cm in this example) and C is the molar concentration. To carry out the calculation, type “= B2/138000” where B2 is the cell that contains the corresponding Absorbance for this time and press enter. You must include the equal sign; otherwise, EXCEL will handle it as a label not a direction for a calculation. The number “1.24638E-05” should appear in cell C2 (check with your TA if you do not have this value.) You are now ready to finish the rest of the data in column C. Move the cursor to the number you just calculated (i.e. cell C2). Go to the menu bar and click on “EDIT”; select “COPY”. The cell C2 should now be highlighted with a dotted line. Now use your mouse and highlight the rest of column C by holding down the left mouse button and dragging the cursor down the column C until it reaches the location for the last data point in column C. You can easily tell where the last data point for column C is by looking at the location of the last data point for column B. Release the mouse button and part of the column C should now be highlighted in black. Go to the menu bar again, click on “EDIT” and select “PASTE”. You just finished generating the concentration data for column C. (Alternatively, you can carry out a set of calculations by dragging the mouse. To do this, highlight the cell that you calculated (C2). You should see a tiny square at the bottom right of 2this cell. Put the center of the cursor (if it is a cross, or the tip of the arrow) on this square and holding the mouse button down, drag the cursor to the last cell that you want to calculate.) You are now ready to carry out the calculations in column D placing your cursor in cell D2 and using the equation “=ln(C2)”. Complete the calculations for the remaining cells in the D column and then move to Cell E2. Now type “=1/C2” If you have trouble generating the data, ask your TA for assistance. Now take a
View Full Document
Unlocking...