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 analysis and graphing Data entry data 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 1 Sample data for the reaction of Brilliant Blue FCF with hypochlorite molar absorptivity for Brilliant Blue FCF 1 38 x 105 M cm 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 A Time 0 30 60 90 120 150 180 210 240 270 300 330 360 390 420 450 B Absorbance 1 72 1 29 0 87 0 58 0 39 0 25 0 17 0 11 0 073 0 048 0 032 0 021 0 014 0 009 0 007 0 004 C D E 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 2 this 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 look …
View Full Document
Unlocking...