The use of the spreadsheet has added a whole new dimension to the tasks involved in recording and reporting student performance. Instructors are taking advantage of the ease of use and power of the Microsoft Excel spreadsheet to make tracking student achievement less burdensome.

You will learn how to:

• Create a grade book. 
• Enter text into a worksheet. 
• Enter data into a worksheet. 
• Format text and data. 
• Create simple formulas. 
• Save worksheet files.

Objective

It's the beginning of the year and you want to create an electronic grade book to keep track of student achievement and do more sophisticated reporting of student progress. You will use the Microsoft Excel 2000 program to accomplish these and other tasks by entering the student information onto an Excel 2000 worksheet.
 

1. Open Microsoft Excel 2000.

1. Click cell A1 to select it. 
2. Type your name and press enter. 
3. In cell A2, type the name of your class. 
4. Press the down arrow key to go to cell A3. 
5. Type in Day/Time the class meets.
6. Press the down arrow key to go to cell A4. 
7. Type in Fall 2001.
8. Press enter twice to go to cell A6.

1. Type Last Name
2. Press the tab key. 
3. Type the following items in the following cells, pressing tab after each entry:

Notice that not all entries fit neatly into their cells. You will return to adjust the size of the columns in a few moments.

1. Right-click cell A1. 
2. Click Format cells. The Format cells dialog box appears, where you can enhance cells in a variety of ways. 
3. Click the Font tab, if it's not already selected. 
4. In the Font Style box, click Bold. 
5. In the Size box, scroll down and click 14. 
6. Click OK. 
7. Highlight cells A2 through J4 by clicking A2 and dragging to row J4. 
8. Right-click the selection. 
9. In the Shortcut menu, click Format Cells.
10. Click the Alignment tab. 
11. Under Horizontal Text Alignment, select Center Across Selection. 
12. Click OK. 
13. With these cells still highlighted, click the arrow next to the Font Color button on the Formatting toolbar, and click the Light Blue color. 
14. On the Formatting toolbar, click the Bold button. 
15. Click the Font Size box and then click 16. 
16. Select row 6 by clicking on the Row Header (the numbered gray area to the left of row 6). 
17. On the Formatting toolbar, click the Bold button and the Italic button. 
18. Click the Center button to center the headings in each cell.

1. Select columns A through J by clicking the Column Header for column A and dragging across to column J. Selected columns are highlighted. 
2. Move the mouse pointer over the border between any two column headers until it changes to a double arrow. 
3. Double-click to automatically get the best fit for all the columns. 
4. At this point your spreadsheet should look like this: 

It's time to fill in the names of your students and their scores. When finished, you'll do a little more formatting and add some formulas.

1. Type the following names into cells A7 through A11, pressing enter after each entry: 
   Atwater
   Bosco
   Brown
   Morton
   Wong
    
2. Type the following names into cells B7 through B11: 
   Allyson
   Bill
   Latavia
   Myron
   Hu
   
   
3. Next, complete the grid by entering the following data for each column: 
   
    
4. Type Class Average in cell A13.

1. Select cells A7 through H11. 
2. Click the Sort Ascending button.

1. Bold and italicize the entry in A13. 
2. Select cells A6 through I13. 
3. Right-click the selection, then click Format Cells in the shortcut menu. 
4. Click the Border tab.
5. In the Style box, click the Medium-Weight Solid Line. 
6. In the presets area, click the Outline button. 
7. Click OK. 
8. Select cells C7:I13. 
9. Click the Center button on the Formatting toolbar.

Autofill: By dragging the fill handle of a cell, you can copy that cell to other cells in the same row or column.

First you want to get a class average for each homework assignment and the quiz and test.

1. Click cell C13. 
2. Type =average(HW-1)


3. Press enter. 

Notice that the average of the scores shows up in cell C13. Also notice that the formula itself shows in the formula bar when the cell is selected. To modify or change the formula, simply click in the formula bar to make the changes, and then press enter.

You have the average for the homework assignment, but you need the average for the other columns. You could type the formulas in cells D13 through H13. But that would take some time, and you might make some mistakes. Excel can make it very easy for you.

4. To fill all of the other cells, drag from the lower-right corner of cell C13 to H13. 

 Excel not only copies the formula into each cell but adjusts the formula for each column so that it is correct for that set of numbers. Of course, there is an error in cell H13 because there are no numbers here to sum yet. 
5. Click cell I7. 

You need a formula here that adds up the points for all of the work and divides by the number of possible points. Then you'll convert it to a percentage.
6. Type =sum(
7. Select cells C7:H7. 

Notice that Excel puts the range you have selected into the formula for you.
8. Type )/166
9. Press enter. 

Now, just as you copied the formulas in row 13, you drag the fill handle to copy the formula in I7 to I11.

1. Select cells I7 through I13. 
2. Click the Percentage button on the toolbar.

Saving your grade book

If you have not saved your grade book yet, and you want to keep this sample, do it now.

1. Click the Save button. 
2. Name your file, then choose Save from the Standard toolbar. 


All that's left is to decide what grade each student should get.

Finally, let's add a function that automatically calculates student grades based on their averages in column G.

1. Click cell J7. 
2. Type =IF(AverageScore>0.89, "A")
3. Press enter. 

The average in cell I7 is >89%, therefore the student gets an "A." What about assigning letter grades for students performing below 89%?
4. Click cell J7. Press Delete to clear the cell contents. 
5. On the Insert menu, click Function. 
6. In the Function Category list, click Logical. 
7. In the Function Name list, click IF. 
8. Click the Office Assistant icon in the lower-left corner of the dialog box. 
9. When the Office Assistant asks if you want help, click Help with this Feature. 
10. Click Help on Selected Function. The Help window displays help on the IF function. 
11. Scroll down to the Examples section and review the following example:

The function required to return a letter grade is listed below the table, and is called a nested function:

=IF(AverageScore>.89,"A",IF(AverageScore>.79,"B",
IF(AverageScore>.69,"C",IF(AverageScore>.59,"D","F"))))

You can make as many modifications as you need, and of course, you can easily copy this sheet several times to make sheets for your other classes.