Using Excel to Manage Grades | Tips & Shortcuts
Sections 1-4: Getting Started | Sections 5-6: Range Names & VLOOKUP
Sections 7-9: Averaging | Sections 10-12: Post Averaging
10. Printing your Worksheets (Including Sorting into ID Order)
11. Assigning Letter Grades from Sums or Averages
12. Finding Students who are not in your Masterlist of IDs & Names
Printing a Worksheet, Selection, or Workbook:
Click on File then Print. The printing default is to print the worksheet you are viewing (the active worksheet). You may also print the entire workbook (this includes all worksheets, even blank ones) or you may print a highlighted selection of data on a worksheet.
You should check the Preview before printing to make sure that your data will fit on a page. If you have lots of columns of data (or your columns are very wide) you may need to change the Print Setup (under File) to Landscape. There is also a Scaling section on the Page Setup screen. You may want to check the Adjust to Fit button.
Sorting the Worksheet into ID Order:
After you have printed a copy of your MasterList worksheet for the class you will need to sort the worksheet so you can print out a copy for posting. There are 2 AutoSort buttons on the Toolbar. One is labeled A->Z and the other is Z->A.
Click on any student's ID in the MasterList worksheet. DO NOT highlight the entire column. See note on AutoSort under "Sorting Student Names."
Click on the A->Z AutoSort button.
Printing a Worksheet Without Names:
After sorting into ID order the easiest way to print a copy of the worksheet without names is to temporarily shrink the name columns by moving the columns to the left. Leave enough space so you can unshrink them later. It won't matter if some of the first letter shows in the column, it shouldn't print. Use the Print Preview button to make sure the names are omitted. Be sure to unshrink your columns later.
Alternately, here is a method for completely hiding a column:
Click on the column heading for the column you want to hide.
Click on Format, then on Columns.
Click on Hide. The column is now hidden from view. A solid bar is placed in the heading row to denote the missing column.
To put back the column click on Format, then on Columns.
Click Unhide. If you have hidden several columns that are next to each other you may have to highlight the columns to the left AND right (together) then click Format -> Columns -> Unhide.
Sorting Student Names:
The best (and safest) method for sorting multiple fields of data is to use the Sort Dialog box. Assume the last name is in Column B and the first name is in Column C.
Under Data, click on Sort.
Under Sort by, locate Column B. Select Ascending. Under Then by, locate Column C. Select Ascending.
Since your data columns should have a header label click on Header Row.
Click OK. The Sort box will close and the worksheet will be sorted.
Note: You can use the AutoSort key on the Toolbar but you must be very careful. You must highlight only 1 cell in the column before using the button. If you make a mistake and highlight the entire column and then AutoSort, the column itself will be sorted but the rest of the worksheet will remain exactly where it was before. Thus, the last name will not be with the correct ID, first name, and exam data. If you do decide to use AutoSort, sort the initials/first name column first then do an AutoSort on the last name.
By default, all alphabetic sorts are non-case sensitive. This means that there is no difference between Smith and smith as far as these sorting routines are concerned. (There is a way to change this in the Sort Dialog box under Options.)
When doing an alphabetic sort all the rows of data that do not contain a student name are placed at the bottom of the data, not at the top.
If the sums or averages you have computed are the final class grades for the students you can use this section to assign a letter grade to their scores. If the sum or average represents only a part of the final class grade you may skip this section altogether.
You will need an additional worksheet (to hold the score/letter grade lookup table) and 2 more Range Names defined.
Insert a New Worksheet:
If you don't remember how to add a worksheet to your overall file here's the method to use:
Click Insert, then click Worksheet.
It will be named Sheet 1.
Rename the worksheet by double clicking on the title tab at the bottom of the window. You can call it LetterGrades or whatever you wish.
Move the worksheet after the MasterList worksheet.
Type in your Final Score Breakdowns and Letter Grades: In Column A you will put the LOWEST score for a letter grade and arrange the scores in ascending order down the column.
In Column B you will put letter grade assigned to that score.
Here are two examples of this worksheet:
EXAMPLE 1
| EXAMPLE 2
| |||
A
| B
| A
| B
| |
SCORE
| LETTER
| SCORE
| LETTER
| |
Notice that there is no score in Column A for "No Grade". A blank in the sum or average column (or an actual score of 0-4) will be assigned "No Grade" in the new letter grade column. In the first example the range for an "F" is 5-59. Anything 90 and above will be assigned an "A". If you accidentally use the same score twice such as 80 B- and then put 80 B on the next line a student with a final average of 80 will be assigned a "B".
Example 2 shows a more complicated, but more flexible, scheme. With this table an instructor is able to indicate borderline students. He can then decide if the student should receive the lower or higher letter grade, usually based on class participation, extra credit, etc.
These score breakdowns may change every semester and they may also be different for each of your classes.
If you add in Column Headings use Bold.
Create Range Names:
You will need a Range Name for the table of letter grades and one for the sums or averages in your MasterList worksheet.
Highlight the entire table of score breakdowns and letter grades. Include the Headings if you made a row for them.
In the Names box, enter a Range Name, such as, LetterGr. If you need instructions see the section "Setting Up Range Names".
Switch to your MasterList worksheet.
Highlight the column of sums or averages that you want to assign letter grades.
In the Names box, enter a Range Name, such as, Total or Avg. Don't use the word "Sum" or "Average" because they are the names of functions. The program will allow you to do this but it can be very confusing to anyone who looks at your worksheet Range Names and formulas.
Check to see that your new Range Names were correctly assigned by clicking on the Range Name in the Name box and seeing what is highlighted.
Using the VLOOKUP Function:
Review the step-by-step instruction in the VLOOKUP section if needed. In that section you were comparing 2 identical numbers and then moving the exam score if they matched.
Here you will looking at the student's sum or average then trying to find it in the table. You won't be looking for an exact match for the score. (If you did it that way you would have to list ALL numbers as scores in Column A, say from 0 to 100. Instead, the function will be used to find the approximate match to the score.)
The function will be typed into the column to the right of the sum or average in the MasterList worksheet. Here is the function:
=VLOOKUP(Score,'GrTable'!LetterGr,2,true)
Score is the Range Name for the sum or average column on the MasterList worksheet.
'GrTable'! is the name of the worksheet where the score/letter grade table is located. Since worksheet names are considered text they are put between quote (') marks. The exclamation mark (!) is placed after the worksheet name. Note: there is no comma between the worksheet name and the Range Name that follows.
LetterGr refers to the Range Name for the data in the GrTable worksheet.
2 tells the program to use the data from the 2nd column (B) of the GrTable worksheet.
True is the default (and can be omitted from the formula). The function will look for the student's score in the table. If the score is listed then the letter grade will be assigned. If the score is not listed it will assign the letter grade for the score that is less than the student's score. For example, if a student's average was 82 under example 1 he would be assigned a "B" and under example 2 a "B-".
If you have typed in the function correctly and all the arguments (such as the Range Names) have been defined when you hit the Enter/Return key you should see a letter grade (or "No Grade") instead of the formula.
Check the letter grade and the student's score against the table in the GrTable worksheet to make sure that the grades are being assigned correctly.
Copying a Function from a Cell to Other Cells:
If the function works you will want to copy it down the column into each cell. You can use the Fill Handle method described under the section titled "Entering the VLOOKUP Function".
Error Messages:
If you have accidentally left a value of #N/A in the sum or average column you will see a #### instead of a letter grade. Remove the #N/A and the error message should turn into a "No Grade" if you have used a row for blank grades in your table.
You will get a #N/A in the letter grade column if a student has a score that is less than the lowest score in your table.
If you receive any other error message check in the VLOOKUP section for explanations.
If you have a small-to-medium sized class you may have a few students who took one or more of your early exams then dropped the class or a few students who did not take the final. It is usually easy for the instructor or GTA to keep track of them. If this is so, you can skip this section.
If, however, there is a large difference between the number of students who took the first exam and the number of students in your MasterList, you may want to consider the following method for finding your dropped or extra students.
This method consists of running VLOOKUPs for each exam. You should also use it for the final exam (unless it was the one used to create the MasterList of IDs and student names). What you want the function to do is to match IDs and then move a column of dummy data (used as an indicator) from the MasterList to the exam.
Create Additional Range Names:
You will need a Range Name for the entire MasterList and one for each of the exams you want to match. These ranges will overlap the ranges already created for the original functions but that is OK. Below are the steps using Exam1 and MasterList as the worksheets.
Create a Range Name for the ID column of Exam 1. You can name it IDExam1, for example.
Create a Range Name for the entire MasterList INCLUDING one blank column to the right of the last column with data (either the average or Letter Grade column). (We will assume that the blank column is Column I, or 9.) You can name it Master9, for example. In the Heading Row type in something like "Check" as a label for the blank column. (This will indicate to you that you have used this column.)
Create the VLOOKUP Function for Exam 1:
Make Exam1 the Active Sheet. In the blank column to the right of score type in the following function:
=VLOOKUP(IDExam1,'MasterList'!Master9,9,false)
IDExam1 is the Range Name for the ID column of Exam 1.
MasterList is the worksheet name, in quotes and followed by an exclamation mark.
Master9 is the Range Name for the ID column of the MasterList.
9 is the number of the blank column included in the Master9 Range Name.
False means that the IDs in the 2 worksheets must match.
If there are no errors in the formula you will get either of the 2 following values:
0 means that the IDs match. Almost all of your students will have this value. These are the students who are listed in both your MasterList and Exam 1.
#N/A means that this student is not in your MasterList. These are the students you are looking for.
Hint: If you do a Descending Sort (Z->A) on this column all the #N/A values will be at the top. Just highlight any 1 cell in the column and click on the Descending Sort button.
Below are 2 different methods of dealing with the extra/dropped students. You may use one of these or create your own method for handling these students. The simplest method is to create a new worksheet to hold these students. The alternate method is more complex but consolidates all your students.
What to Do With These Students Method 1
Create a New Worksheet:
If you want to keep these students completely separate from the students on the MasterList you can create a new worksheet. Name it something like ExtraStu. Move it after the MasterList. Copy and Paste the extra students from Exam 1 into the new worksheet. Add a row of labels at the top of the worksheet for Exam 1 scores, Exam 2 scores, etc.
Exam 2 extra students can be added in beneath the Exam 1 students (if they are unique). If they took Exam 1 there should be a row for them already. Just type in their Exam 2 score in the correct column. Do the same for each of the remaining exams.
What to Do With These Students Method 2
Add Them to the Top of the MasterList:
If you wish to have all your students together in the MasterList you can Copy and Paste them into your main worksheet. BE VERY CAREFUL. Excel does not work the same as WORD or most word processing programs. If you copy the extra students and try to simply paste them into the top of your MasterList you will end up pasting over good data (and the program will not tell you that you've done this). Word processing programs will move everything down for you when pasting, Excel does not, unless you use Insert Paste instead of the simple Paste command.
Before you start copying and pasting make a note of the first few students' names and Sum/Averages in the MasterList so you can check for them when you have finished.
There are 2 different methods you can use; the results will be the same. The Blank Row method may be more suitable to the novice user of Excel; advanced users may prefer the shorter Insert Paste method.
Blank Row Method:
Count the number of extra students in Exam 1.
Insert the same number of rows at the top of your MasterList. (Highlight the Row number "2". Click Insert, then Rows. To add more rows you can use the shortcut key combination of Ctrl + y on the PC or Command + y on the Mac. This repeats the last instruction.)
Highlight all the extra students (IDs, names, and scores) in Exam 1.
Copy (Ctrl + c on the PC or Command + c on the Mac).
Highlight the first blank ID cell at the top of the MasterList.
Paste (Ctrl + v on the PC or Command + v on the Mac) the students into the MasterList.
Insert Paste Method:
In the MasterList count the number of columns used by the data-don't forget the Check column. This is the number of columns you will need to highlight in the Exam1 worksheet.
In Exam1 worksheet highlight the ID, both columns of name, the score for Exam 1, and enough blank columns to equal the number used in the MasterList. Since the MasterList has data beyond the Exam 1 scores you need to match up the number of columns from the copied cells to the number of columns used by the MasterList. (If there hadn't been any data in any column to the right you wouldn't have needed to do this.)
Copy (Ctrl + c or Command + c).
In the MasterList, highlight the ID in Row 2. (It belongs to the first student in the MasterList.)
Under Insert, select Copied Cells, then Shift Cells Down.
General Directions for Additional Exams (Both Methods):
Create a Range Name and run a VLOOKUP for the exam, following the instructions at the beginning of this section. Do a Descending Sort to move the extra students to the top of the worksheet. Print out a list of the IDs, names, and scores for the Exam 2 students who have values of #N/A.
Check the top of the MasterList for these students; some may be among the students that you added from Exam 1. If you find one, type in his exam score in the correct column. This may leave just a few students to copy to the MasterList.
If the students you need to copy into the MasterList are not in contiguous rows you can either do multiple Copy-Pastes or use the following instructions:
Highlight all the columns you need for the first student.
Hold down the Ctrl key (PC) or the Command key (Mac).
Highlight the next student, etc. When you have highlighted all the students you need release the Ctrl or Command key.
Copy the student rows. Make sure that you have included all the necessary columns based on whichever method you are using.
From here you can proceed to either inserting blank rows in the MasterList and pasting the copied data or to the Insert Paste method.
No matter which method you use, make sure that you move the scores into the correct columns of the masterlist after pasting.
(After pasting the scores will appear in the Exam 1 column. They must be moved.)