Creating the workbook

Activity 1: Creating the workbook

1.      If necessary, open Microsoft Excel XP.

2.      On the File menu, click New.

3.      On the General tab, double-click Workbook to open a new one.

4.      Click cell B2 and then type Student List.

5.      Save your work as "Workbook for Attendance and Grades."  

You can use Excel XP worksheets to collect and analyze information, including student records, lesson notes, school activity budgets, and professional organization information, and data. Students may use Excel XP for scientific data, weather journals, financial reports, nutritional diaries, or legislative voting records. Each worksheet can be easily customized and enhanced with graphics and artistic additions. This lesson teaches you to build a workbook containing a worksheet for student attendance and a worksheet for grades.

Password protecting the workbook

Activity 2: Password protecting the workbook.  Because this workbook contains sensitive information, you can add a password to the document. When a workbook is password-protected, no one can read or change information in it without using the password.

1.      On the File menu, click Save As.

2.      In the Save As dialog box, on the Tools menu, click General Options.

3.      In the Password to open box, type a password and then click OK.

4.      In the Reenter password to proceed box, type your password again and then click OK.

5.      Click Save.

If prompted, click Yes to replace the existing file.

When you create a password, write it down and keep it in a secure place. If you lose the password, you cannot open or gain access to the data in a password-protected workbook.

Passwords are case sensitive. Type the password exactly as you want users to enter it, including uppercase and lowercase letters.

Adding an attendance worksheet (Copying a worksheet into a workbook)

Activity 3: Adding an attendance worksheet (Copying a worksheet into a workbook) In this section, you add the attendance worksheet that you already created. The attendance worksheet is the first worksheet in the book you created previously.

1.      Open the Attendance worksheet you already created.

2.      On the Edit menu, click Move or Copy Sheet.

3.      In the To book dialog box, click (Workbook for Attendance and Grades).

4.      Click OK.

Close the file (Attendance). You do not need to save.

Creating the grade book worksheet

Activity 4: Creating the grade book worksheet

1.      Open the Workbook for Attendance and Grades workbook.

2.      Click Sheet 2 at the bottom of the workbook to move to a new worksheet. Double-click on the Sheet 2 tab and type Grade Book.

3.      Click cell A1 to select it.

4.      Type your name and press ENTER.

5.      In cell A2, type the name of your class (for example, Science, English, or Math).

6.      Press DOWN ARROW to go to cell A3.

7.      Type Period 1.

8.      Press DOWN ARROW to go to cell A4.

9.      Format the column width. Type 1st Quarter and press ENTER four times to move to cell A8.

10. Click the Attendance tab to switch to that worksheet.

11. Select cell A3. Press SHIFT and drag down to the row containing the last student name and number you have entered. Then, release the mouse button. All selected cells are highlighted.

12. Right-click anywhere in the selected cell area and click Copy.

13. Switch back to the Grade Book worksheet and select the same number of cells you selected in the Attendance worksheet, starting from cell A8.

14. Right-click the selected cells and click Paste. The names are copied.

15. In cell D6, type Test 1 16-Oct-1999 .

16. Move to the next cell in the row and repeat the step with the following column headings (these headings are used later for an example of calculating grades):

17. When you are finished, cells D6:R6 should be filled with the above titles.

18. Adjust the column widths on the worksheet.

19. Save your work.

20. To rotate text and cells, see “Formatting rows and columns” later in this chapter.  

Using automatic fill

Activity 5: Using automatic fill

1.      Open the Attendance worksheet if it is not already open,  In cell C1, type Monday.

2.      Click and drag the fill handle to select the cells through cell G5. Do not save your changes.  

Entering and formatting titles

Activity 6: Entering and formatting titles

1.      With the Grade Book worksheet open, click cell A1.

2.      On the Formatting toolbar, click the Font box and then click Antique Olive (or any other font you like).

3.      On the Formatting toolbar, click the arrow next to the Font Size box, click 14, and then click Bold.

Justifying text in rows

Activity 7: Justifying text in rows

1.      On the Grade Book worksheet, select cell A2.

2.       On the Formatting toolbar, click Merge and Center.

3.      Select cells A8 through the last cell containing a student name.

4.      On the Formatting toolbar, click Align Right.

Rotating text in rows

Activity 8: Rotating text in rows

1.      In the Grade Book worksheet, click the row header 6.

2.      On the Format menu, click Cells.

3.      Click the Alignment tab.

4.      Drag the Orientation bar to 45º, or type 45 in the Degrees box.

5.      Click the Border tab and click Outline and Inside.

6.      Click OK. The text and cells are now at an angle. Format the column widths.

Changing cell indent

Activity 9: Changing cell indent

1.      Select cells A7 to the last cell in the column containing a student name.

2.      Click Increase Indent on the Formatting toolbar. Click again. Notice how the selection is now indented.  

Completing the data entry

Activity 10: Completing the data entry

1.      To fill in the scores for the students, place the cursor in the appropriate cell.

2.      Type in the test, quiz, or homework score where appropriate. Enter 100 into cells D7 through R7. This will reflect the highest points possible.

3.      Save your work.

Hiding columns

Activity 11: Hiding columns

1.      Open the Grade Book workbook if it is not already open.

2.      Click the column A header to select the column.

3.      On the Format menu, point to Column and then click Hide.

4.      To unhide your columns, on the Format menu, point to Column, and click Unhide.

Adding a formula for totals

Activity 12: Adding a formula for totals

1.      Open the Grade Book worksheet if it is not already open.
First you want to get a total for the points earned.

2.      Click cell S6 and type total points.

3.      Click cell S7.

4.      Click the Edit Formula button (the = sign).

5.      Click the Name Box drop-down list and then click SUM.

6.      In the Number 1 box, type D7:R7 (the colon indicates a series).

7.      Click OK.

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, make the changes, and then press ENTER.

You have the total for the points possible, but you need the sum for the students’ columns. You could type the formulas in cells D13 through H13. But that would take some time and you might make some mistakes. Excel XP can make it very easy for you.

To fill all of the other cells, select cell S7, place the cursor on the handle in the lower-right corner of the cell, and drag down to the last cell in the column. Excel XP not only copies the formula into each cell, but also adjusts the formula for each column so that it is correct for that column of numbers.

Adding a formula for percentages

Activity 13: Adding a formula for percentages

1.      Click cell T6 and type percentage.

2.      Click cell T7. You need a formula here that divides the number of points earned by the number of possible points. Then you'll convert it to a percentage.

3.      Type =S7/$S$7 (the cell with the total divided by the constant total in cell S7).

4.      Press ENTER. The number 1 appears in cell S7.

5.      To change the number to a percentage, click cell T7 and then on the Format menu, click Cells.

6.      Click the Number tab. In the Category box, click Percentage.

7.      In the Decimal Places box, type 1.

8.      Click OK. You should now see 100.0% in cell T7.

9.      Now, just as you copied the formulas earlier, you drag the fill handle to copy the formula in T7 to the rest of the column. Because you used $S$7 instead of S7, the number does not adjust to the total in the column in which you are working. To find out more about constants, ask the Office Assistant.  

10. Save your work.

Creating letter grades from percentages

Activity 14: Creating letter grades from percentages

1.      Click cell X7, or any cell outside the range of your grades, and then type Percentage.

2.      Click cell Y7, or the cell next to the one you just typed in, and then type Letter Grade.

3.      In cell X8, type 0. The range for the lookup table should always start with the lowest number.

4.      In cell Y8, type F. This represents the letter grade associated with values greater than zero. When you add the next value, it will become the lower cutoff for the next letter grade.

5.      In cell X9, type .599. Any value greater than this will be assigned the next letter grade.

6.      In cell Y9, type D.

7.      In cell X10, type .699 and then type C in cell Y10. In cell X11, type .799 and then type B in cell Y11. In cell X12 type .899 and then type A in cell Y12. This completes your lookup table. Continue to add the formula to your grade sheet that calculates the letter grade for each percentage.  

8.      Click cell U6 and type Grade.

9.      Click cell U7 and then click Edit Formula (the = sign).

10. Click the Name Box drop-down list and then select LOOKUP. If LOOKUP is not available, click More Functions and then choose LOOKUP from the Function Name list.  

11. In the Select Arguments dialog box, select lookup_value,lookup_vector,
result_vector and then click OK. Use this type of formula when you have more than one column in your lookup table. The LOOKUP formula window appears with the cursor in the Lookup_value field.  

12. Click cell T7. This assigns the percentage earned as the value compared to the lookup table.

13. Click in the Lookup_vector field and then drag across cells X8 through X12. This assigns your grade cutoffs to be compared to the percentage in cell T7.

14. Click in the Result_vector field and then drag across cells Y8 through Y12. This assigns the letter grade to the corresponding percentages and will be the result found in cell T7.

15. Click OK. The letter “A” should now be in cell U7.

16. In order to keep the cells in the lookup table as constants for the rest of the column, you need to add “$” before each of the values in the formula for the lookup table. When you are done the formula should look as follows:

=LOOKUP(T7,$X$8:$X$12,$Y$8:$Y$12)

17. Click the fill handle in cell U7 and drag to the bottom of the column. The assigned letter grades appear in each of the cells.

18. Save your work.

ASSIGNMENT ONE DUE!!!

Save your work! This completed document will be used as your first assignment. Email as an attachment to assignments@wresa.org . Don't forget to include your password if you password protected the document.

You have now completed this module. Take a few minutes to review and practice what you have learned.