Chapter 7 - Introduction to Excel
7.1 Introduction to Spreadsheets
A spreadsheet is a grid of cells that are arranged horizontally in rows and vertically in columns. If you put numbers in these cells, you can tell the program to perform mathematical operations on the numbers.
The advantage of a spreadsheet is that when one number is changed, every number that depends on it is recalculated automatically and changed as well.
The following exercises will give you a taste of Excel's ability to manage numbers. If you feel uncomfortable with Excel at the end of this chapter, there are two computer aided instruction programs you can view. The first is called Introducing Microsoft Excel, the second is called Learning Microsoft Excel. Both of these programs can be found in the Excel Help in the upper right hand corner of the menu bar (Look for the question mark).
Start the Excel program by double-clicking on the Excel icon. You will see a screen that tells you that you are using Excel 5.0 After a few moments a worksheet will appear.
It is possible that the startup parameters for Excel have been set so that Excel 5.0 does not appear. If this happens, check with the instructor
Move the cursor (it looks like a large +) to cell A1, click on cell A1 to select it and type Gradebook. Now press the return or enter key. Gradebook should appear in cell A1.
Next, move the cursor to cell A3. Click and drag the cursor over cells A3 to F3 and type the words Student's Name and press enter or return. You will notice that the words Student's Name appears in cell A3 and the cursor has moved to cell B3. If you did not click and drag over cells A3 to F3 the cursor would have moved to cell A4 when you pressed return. Enter Test 1 in cell B3, Test 2 in cell C3. Continue until you have 5 tests entered into columns B3 to F3.
Notice that the title Student's Name is cut off. To show the entire title, change the width of the column by clicking on the line between the letters A and B near the top of the worksheet. The cursor should change so that it looks like the cursor shown on the below:
Move this new cursor between A and B to adjust the width of column A.
Now drag the cursor over cells A1 to F1 and A3 to F3 and click the bold button. Everything that you have typed should now appear in a bold font. Your spreadsheet should look something like this:
Next type in the names of some students in cells A4 to A8. It will be make later manipulations easier if you type in the names as last name, first name. For example, type Cohn, Kim rather than Kim Cohn. If you enter long names you may have to adjust the width of the column to see the entire name. Make up names. Next, enter grades for tests under cells B4 to B8, C4 to C8, D4 to D8, E4 to E8 and F4 to F8. Adjust the width of the cells so that the sheet fits into the window of the computer without difficulty. The spreadsheet should look something like this:
1. Calculating student averages by means of a formula.
a) To calculate the average of the first student (in my example, Kim Cohn) we need to add up the first student's grades and divide by the number of exams, five. We will use Excel to carry out these two operations.
b) Click on cell G4 and type the symbol =. Next sequentially click in boxes B4, C4, D4, E4 and F4. The formula, =B4+C4+D4+E4+F4, should appear in the formula bar. If you made a mistake, use the cursor in the formula bar to delete the mistake(s). Press enter and the sum of the cells B4 to F4 should appear as shown below. Check the sum by hand to make sure you entered the formula correctly.
c) If this were all Excel could do, no one would use it. However, Excel saves time by eliminating repetition. Click on the sum in cell G4 and drag the cursor over cells G5 to G8. All of the cells from G4 to G8 should be selected. Under Edit on the menu select Fill Down. Excel will figure out what you are trying to calculate and enter the correct formulas in those cells. So, for example, cell G7 now contains the formula =B7+C7+D7+E7+F7 and the correct sum for those cells has been calculated.
d) The grade book is getting too big to fit on the screen. Click and drag over all of the cells that have something in them. Go to the menu and choose Format, scroll down to column and choose autofit. Looks better.
e) To calculate the average for each student, select cell H 3 and enter Average. Next select cell H4 and type =, then click on cell G4 (G4 will appear in the formula window) type the /" symbol (on the lower right hand side of the keyboard) followed by 5. The formula bar should read G4/5. Press enter to see the average. Next select cells G4 to G8 and choose Fill Down to complete the calculations. Your grade book should be complete and look something like the one shown below:
2. Using Excel's Built in Formulas.
a) Another way to calculate data is to use formulas or functions that are built into Excel. Click on cell I4 to make it active. From Insert in the menu bar at the top of Excel select Function. Choose Statistical as the Function Category and Average as the Paste Function. The dialog box with the correct selections will appear as:
Press return or click on OK to see the following.
Now click in the box labeled number 1 and then click in the Excel worksheet. Click and drag from B4 to F4 and then hit return or click on finish. If you carried out the operation correctly, the formula bar will contain the formula =AVERAGE(B4:F4), and the cell I4 will contain the same average you calculated before.
e) Fill down by selecting the cells I4 to I8, choosing Fill Down from the Edit Menu on the menu bar and pressing Enter.
f) Although the average determined by the formula and the average determined by the function are the same in the example shown above, there are times when the two numbers are different. For example, suppose Kim Cohn was absent for the first exam and his grade was entered as an a. Try it and see what the sheet calculates. It should look like this:
7.5 Pasting Links, Developing a Progress Report
Finally, we will set up a progress report for the student. There are two ways to do this. First, copy the data from Excel and paste it into a word processing document. This can be done in several ways. If you think the data will never change you can cut the numbers from Excel and paste them into Excel or Word. However if the numbers will change you can stay in Excel and paste. The following describes the second process. Microsoft products use a third method called object linking and embedding. You may use this method to paste between any two Microsoft products.
a) Open the spreadsheet that you used for your gradebook. Open another spreadsheet by clicking on the tab that shows sheet 2 at the bottom of the spreadsheet where it indicates sheet 1, sheet 2, etc.
b) In box A1 Enter the words Progress Report in bold letters. Click in box B1 to select it. Under the Insert menu choose Paste Function, and in the dialog box choose, under the function category, date and time and choose now:
This action will paste the current date and time in B2. The date can be formatted by choosing Format from the menu. In the example below I have formatted it so that it reads 15-Oct-96
c) Click on A3 to select it, and enter the words Student's name.
d) Click on A4 and enter one of the student's name.
e) Click on cell A5 and enter the words Your current average is and then open the grade book you prepared previously and click on a cell that contains the average of the same student. Choose Copy from the menu bar under Edit. Open your new spreadsheet and click on cell B5 and chose Paste Link from the menu bar under Edit and paste special.
If a student is deleted or added in the grade book, the links will be maintained and the progress report updates as the additions and deletions are made.
Excel is very useful in manipulating and managing data and numbers. The previous example illustrated some of the functions of Excel.
It is possible to set up grade books, progress reports and other charts that contain more information. For example, the gradebook you developed could be modified so numbers in a range automatically produce a grade of A, B, C or the grade book could be sorted by total averages to rank the students. Comments could be added so that an improvement in performance could automatically call forth a specific response in the progress report. All of this takes time and effort. In many cases, it is more convenient to use an application that has been developed to carry out a specific function, like keeping a grade book, or balancing a check book. Excel is like a Swiss army knife. It does a lot, but at times is difficult to use. I do not use a Swiss army knife to open my can of beans unless I have no other option. However, when no other program can be used to keep track of data, Excel and other spreadsheets like Lotus 1,2,3 are extremely useful. During the course of the term you will explore many of the functions of Excel that are useful in science.
