Microsoft Excel XP/X

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 formulas quickly, and testing and double-checking helps assure accuracy in your worksheet.

Creating ID Codes
Sorting Your Worksheet
Filling Down or Across
Using Custom Fills
Checking for Duplicates
Reporting Test Grades
Testing and Double Checking

Creating 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). Refer to Capturing Student Names for more information.

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

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).
Windows: Press [Enter]
Macintosh: Press [return]
The function is calculated and appears in the selected cell.

Sorting 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 will want to sort your worksheet.

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

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

Sorting Your Data

In your Excel gradebook, select the data to be sorted
From the Data menu, select Sort...
The Sort dialog box appears.
From the Sort by pull-down list, select the column for the primary sort (e.g., if the test scores you want to sort are in Column C, select Column C)
For an ascending sort, select Ascending
For a descending sort, select Descending
OPTIONAL: If you want a second and/or third level sort, complete the Then by section(s)
In the My list has section, confirm that the correct header row option is selected
1. If your selection includes header information (e.g., the names of items graded), select Header row
  If your selection has no header information, select No header row
Click OK
Your selection is sorted according to the criteria you selected.

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

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.

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.

To fill a formula or heading:

Select the cell that contains the formula or heading you want to fill
Using the mouse, place your pointer over the lower right corner of the cell (the fill corner)
NOTE: Before filling, verify that the pointer changes to a crosshairs.
When the pointer turns to a crosshairs, click and drag over the desired cells
NOTES:
You can fill cells up and down, to the right and to the left.
You can move in only one direction at a time.
When you finish filling the desired cells, release the mouse
The cells are filled.

Using Custom Fills

Custom fills allow you to select what information you want repeated. 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 fills or you can create your own custom fills from scratch.

Importing Custom Fills

Select the cells which contain the information you want repeated
NOTE: Excel will use only the information you select and will not continue trends.
Windows: From the Tools menu, select Options...
The Options dialog box appears.
Macintosh: From the Excel menu, select Preferences...
The Preferences dialog box appears.
Windows: Select the Custom Lists tab
Macintosh: Select Custom Lists
To use your selected list as a custom fill, click IMPORT
Your selection appears in the List Entries window.
Click OK
You are returned to your data sheet.
From your data sheet, select the last cell in the group you want to repeat
When the pointer changes to a crosshairs, click and hold the fill corner
Drag the corner in the direction you want the information to be copied
Release the mouse button
The cells will be filled with the information you selected.

Creating Custom Fills

NOTE: Custom fills will only work with word lists. Number lists do not work consistently.

Windows: From the Tools menu, select Options...
The Options dialog box appears.
Macintosh: From the Excel menu, select Preferences...
The Preferences dialog box appears.
Windows: Select the Custom Lists tab
Macintosh: Select Custom Lists
Under Custom lists, select NEW LIST
In the List entries text box, type the information you want to be in the custom fill, pressing [Enter] or [return] after each entry
Click ADD
Click OK
To use your list, place the cell pointer in any cell and type the first entry
Using the mouse, place your pointer over the lower right corner of the cell (the fill corner)
When the pointer changes to a crosshairs, click and hold the fill corner of this cell
Drag the corner in the direction you want the information to be copied
Release the mouse button
The cells will be filled with the information you provided.

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

To sort the data by the new code, select all the records (refer to Sorting Your Worksheet)
WARNING: If you do not select all the records, the sort will separate the codes from the correct students' names and grades.
From the Data menu, select Sort...
The Sort dialog box appears
From the Sort by pull-down list, select code, or whichever column your ID codes are in
Click OK
The sort will be completed.
Select the column to the right of the ID code column
From the Insert menu, select Columns
A new column will be inserted to the right of the ID code column.
In the cell to the right of the first student's 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's 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 (refer to Filling Down or Across)
The records that say "dup" have a duplicate in the class and must be given another ID code.

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

Testing and Double Checking

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 Troubleshooting Formulas.