Creating the workbook

Activity 1: Creating the workbook

1.      If necessary, open Microsoft Excel.

2.      On the File menu, click New.

3.      Click cell B2 and then type Student List.

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

You can use Excel X worksheets to collect and analyze information, including student records, lesson notes, school activity budgets, and professional organization information, and data. Students may use Excel 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.      Select the worksheet by clicking the Sheet tab

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

1.     To move or copy worksheets to another workbook, make sure that the target workbook is open in the same instance of Microsoft Office Excel.

 Note   You cannot move or copy worksheets between workbooks that are open in separate instances of Excel. If a workbook is opened in a separate instance of Excel — for example, this can happen when you open that workbook from a Windows SharePoint Services site — make sure that you open that workbook in the same instance of Excel instead by browsing to it in the Open dialog box (Microsoft Office Button, Open).

2.     In the workbook that contains the sheets that you want to move or copy, select the sheets.

How to select worksheets

Tip    When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. To cancel a selection of multiple worksheets in a workbook, click any unselected worksheet. If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets on the shortcut menu.

Keyboard shortcut  To move to the next or previous sheet tab, you can also press CTRL+PAGE UP or CTRL+PAGE DOWN.

3.     On the Home tab, in the Cells group, click Format, and then under Organize Sheets, click Move or Copy Sheet.

 Tip   You can also right-click a selected sheet tab, and then click Move or Copy.

4.     In the Move or Copy dialog box, in the To book list, do one of the following:

·         Click the workbook to which you want to move or copy the selected sheets.

·         Click new book to move or copy the selected sheets to a new workbook.

5.     In the Before sheet list, do one of the following:

·         Click the sheet before which you want to insert the moved or copied sheets.

·         Click move to end to insert the moved or copied sheets after the last sheet in the workbook and before the Insert Worksheet tab.

6.     To copy the sheets instead of moving them, in the Move or Copy dialog box, select the Create a copy check box.

 Note   When you create a copy of the worksheet, the worksheet is duplicated in the destination workbook. When you move a worksheet, the worksheet is removed from the original workbook and appears in the destination workbook only.

Tips

·         To rename the moved or copied worksheet in the destination workbook, right-click its sheet tab, click Rename, and then type the new name in the sheet tab.

·         Worksheets that you move or copy to another workbook will use the theme fonts, colors, and effects that are applied to the destination workbook.

·         To change the color of the sheet tab, right-click the sheet tab, click Tab Color, and then click the color that you want to use.

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-2010 .

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 Home Tab and Alignment Section 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.      In the Alignment Section, click the Orientation button.

3.      Chose "Angle Counterclockwise".

5.      Click the Border button (In the Font Section) and click "All Borders"..

6.      The text and cells are now at an angle. Format the column widths by double clicking between the column headers.

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 in the Formatting section. 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.      In the Cells section of the Home Tab, Click the Format button , point to Hide & 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 f_x sign).

5.      Click the SUM function and then OK.

6.      In the Number 1 box, type D7:R7 if it is not already there. (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 in the Cells section of the Home Tab, click the Format button and choose "Format 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.

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 f_x sign).

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

  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 cgeorge@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.