This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2003/2004

Gradebook: Additional Tips

Excel has other features that can make using your gradebook much easier. Using ID codes can protect students' privacy in posting grades or when reporting grades to students via email. Sorting data while keeping the correct grade with the correct student is also useful to see rearranged data. Copying cell formulas using the Fill command allows you to copy quickly. Testing and double-checking help assure accuracy in your worksheet. 

return to topCreating ID Codes

You can either obtain students names for your Excel gradebook by using the gradebook or getlist in the subject line of an email. The following instructions will work only if you used the Gradebook option (Gradebook returns each student's University ID; getlist does not). For more information, refer to Capturing Student Names.

Use the RIGHT function to capture a certain number of digits from any string of digits. Refer to Calculating with Functions for more information.

  1. Select any blank cell

  2. Type =RIGHT(cell ID, number of desired digits)
    EXAMPLE: If 1234567 is in cell B2, the formula =RIGHT (B2, 4) would give the right 4 digits in cell B2 (4567 in this example).
    ID codes created by RIGHT function

  3. Windows: Press [Enter]
    Macintosh: Press [return]
    The function is calculated and appears in the selected cell.

return to topChecking for Duplicates

Sometimes among the unique student ID codes, the last four digits of two or more student IDs could be the same. If you create your own unique ID codes with these digits, you have a risk of getting duplicate ID codes in a class. To check for this, follow these instructions.

  1. Sort your data by the ID code
    NOTE: For more information, refer to Using the Sort Command.
    WARNING: If you do not select all the records, the sort will separate the codes from the correct student names and grades.

  2. Select the column to the right of the ID code column

  3. From the Insert menu, select Columns
    A new column is inserted to the right of the ID code column.

  4. In the cell to the right of the first student code, type a formula to compare codes
    If the current code equals the previous code, the word "dup" will display; otherwise, a dash "-" will display.
    EXAMPLE: If the first student code is in C2, the formula would be =IF(C2=C1,"dup","-")
    Cells contain the formula to find duplicate codes

  5. To copy this formula into the following cells, drag the fill handle down the column
    For more information, refer to Using the Fill Command.
    The records that say "dup" have a duplicate in the class and must be given another ID code.

return to topSorting Your Worksheet

After entering the scores for a particular test, you may want to see a list of the scores from highest to lowest, or vice versa. To do this, you may need to sort your worksheet. For more information, refer to Using the Sort Command.

WARNING: Be very careful sorting your worksheet. You must select all rows and all columns that you want sorted. This includes student names, ID numbers, grades, etc. You cannot merely select the column containing the scores. If you select only one column, Excel will give you a warning dialog box informing you that due to your limited selection, your scores will no longer be affiliated with the proper students. You must choose to either Expand the selection or Continue with the current selection and click the appropriate button.

Sorting Hints

return to topFilling Down or Across

The Fill command is similar to copy and paste but is completed using the mouse instead of menu choices. The Fill command can be used to copy a formula for each of the students. It can also be used to complete headings that have a trend (e.g., Quiz1, Quiz2, Quiz3). You can fill cells up and down, to the right and to the left.

The Fill command uses the Fill box in the lower right corner of the selected cell. When you place your mouse over the fill corner, it turns to black crosshairs ( + ) indicating you are ready to begin filling. For more information, refer to Using the Fill Command.

NOTE: If Excel recognizes a pattern in the information you entered, the additional cells will contain the next item in the pattern. For example, if the first cell contains the day Sunday, Excel will fill the following cells with Monday, Tuesday, etc. Other examples include filling for the months of the year and hours of the day.

return to top Using Custom Fills

Custom fills allow you to select what information you want to repeat. For example, if you wanted Quiz1, Quiz2, Quiz3...Quiz6 repeated, custom fills allows you to do this. Using the regular Fill function would start at Quiz1 and continue to QuizX, where X is the point at which you stop dragging the mouse. You can import data that currently exists in your worksheet to use for custom fill or create your own custom fills from scratch.

return to topReporting Test Grades

One method for reporting grades (interim or project grades) to your students is via email. Some faculty will create unique ID codes for each student. Then, using the same techniques for preparing your gradebook for submitting grades, send an email of the class grades to the classlist. Since each student has a unique code, the students can see all of the grades; however, they do not know what grade a specific student received.

NOTE: To help eliminate the possibility of guessing specific students' grades, you may want to sort the grades by the unique code.

return to topTesting and Double Checking

When you are writing formulas and functions to perform calculations in your gradebook, perform the following checks:

If your formula does not work, make sure you have the correct cells and/or cell range entered into your formula. For more information on troubleshooting your formulas, refer to Correcting Circular References.

Excellence. Our Measure. Our Motto. Our Goal.