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?
This document discusses various Excel features that can make using your gradebook much easier. Creating ID codes can protect students' privacy in posting grades or when reporting grades to students via email. Sorting data is useful for focusing on different pieces of information (e.g., last names, score for a particular assignment). Copying cell formulas using the Fill command allows you to copy quickly. Testing and double-checking help assure accuracy in your worksheet.
You can obtain students' names for your Excel gradebook by using either the gradebook or getlist command with email (gradebook returns each student's University ID; getlist does not). For more information, refer to Using Getlist and Gradebook (Outlook or Entourage). Since this method of creating ID codes uses students' University IDs, the following instructions will work only if you use the Gradebook option.
The RIGHT function allows you to capture a specified number of digits from the far right of any string of digits. For more information, refer to Calculating with Functions.
Select any blank cell
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).
If you create your own ID codes by capturing the last few digits from the student ID codes, two or more students could have the same code. Rather than checking visually for duplicates, you can use Excel's IF function.
The IF function is a logic test to see if a condition is true or false. You specify the value to be returned in either case. In this example, if the ID code in a cell is the same as the one above it, the formula returns dup. If it is not the same, the formula will return a dash.
Sort your data by the ID code
NOTE: For more information, refer to Sorting Data (Win | Mac).
WARNING: If you do not select all the records, the sort will separate the codes from the correct student names and grades.
Select the column to the right of the ID code column
Windows: On the Home command tab, in the Cells group, click theon the INSERT button » select Insert Sheet Columns
Macintosh: From the Insert menu, select Columns
A new column is inserted to the right of the ID code column.
In the cell to the right of the first student code, type
=IF(cell IDx=cell IDy,"value_if_true","value_if_false")
EXAMPLE: If the first student code is in C2, the formula would be =IF(C2=C1,"dup","-")
To copy this formula into the following cells, drag the fill handle down the column
The records that say "dup" have a duplicate in the class and must be given another ID code.
HINT: For more information, refer to Using the Fill Command (Win | Mac).
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 Sorting Data (Win | Mac).
WARNING: Be very careful sorting your worksheet. 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.
Since sorting can rearrange your data in undesired ways, we recommend that you save your work before you start the sort. If the results are not what you expected, you can always close without saving and then reopen the file.
One of the keys to a successful sort is to select all of the data that you want to sort. If you have a collection of related data that is in eight columns but you select only two, the two selected columns are what will be sorted, losing the relationship to the other data.
If you have blank rows within your sort range, the blank rows will appear either at the bottom or top of the sorted results, depending on your sort order.
The Fill command is similar to copying and pasting but is more versatile. Fill Command (Win | Mac) can be used to copy a formula for each of the students. It can also be used to automatically complete headings that have a trend (e.g., Quiz1, Quiz2, Quiz3). You can fill cells in any direction.
The Fill command uses the Fill corner in the lower right of the selected cell. When you place your mouse over the fill corner, it turns to a black crosshairs ( + ) indicating you are ready to begin filling. For more information, refer to Using the Fill Command (Win | Mac).
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.
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 (Win | Mac) data that currently exists in your worksheet to use for a custom fill or create your own custom fills (Win | Mac) from scratch.
One method for reporting 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, you can 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.
When you are writing formulas and functions to perform calculations in your gradebook, perform the following checks:
Confirm the results by manually calculating some results
Review results for reasonability
Perform cross-checks for calculations
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.