Home

Office Information
Location
Hours
Contact Information
Staff
Opscan Forms

Services
Exams
Evaluations
Research
Our Archives

Help
FAQ
Opscan Forms
Understanding Output
Excel

Other Information
Alternatives to Opscans
Test Design & Analysis
Testing Memos

excel

Using Excel to Manage Grades

 

These instructions can be used to create an Excel spreadsheet that will total raw scores and average raw scores, percent scores, and T-scores, with or without exam weighting. They may be used for Excel 97 (PC) and Excel 98 (Mac) versions and above.

 

Remember:
Make Backup Copies of All Your Files on a Disk or Other Media.
Keep Them Somewhere Safe.

The following sections are included:

1. Creating a Folder on the Desktop to hold your Files

2. Creating an Overall Excel file for the Class

3. Copying Exam Printout Data into your Overall Excel File

4. Creating a Masterlist of IDs & Names

1. Creating a Folder on the Desktop to Hold Your Files
^ Back to Top of Page

You will need to create a folder at the start of each semester to hold your Overall Excel file and any other data files that may pertain to your class. This is also a good place to put the exam printouts that Test Scoring e-mails to you. If you will be doing the averaging for more than 1 class, create 1 for each class.

You can put your new folder anywhere but the Desktop is a good place to start, you can move it later if you wish. Name the folder something like Mktg3104, Spring_2004, for example.

 

2. Creating an Overall Excel File for the Class
^ Back to Top of Page

In this section you will create the spreadsheet that will hold all the data from the exams, create the averages, sums, or whatever you wish. What follows is a brief outline of the steps you will follow to create your new Excel file. Each step will be explained in detail below the outline.

Open Excel.

Add more worksheets, if necessary. Rearrange and Rename them.

Start a Title worksheet.

Type in a row of column headings on the MasterList worksheet.

Save your new Overall file.

Open Excel:

Double click on the Excel icon on your Desktop or locate the program among your Applications. Excel will open to a blank spreadsheet with (usually) 3 worksheets listed at the bottom. The Sheet1 name will have a white background (this is called the Active Sheet) and all the other sheets will have a gray background.

 

Add More Worksheets, Rearrange and Rename Them:

Decide how many worksheets you will need. You will need 1 for the Title Page, 1 for each exam, project, quiz, etc., and 1 to hold the overall data. For 4 exams you will need to add 3 worksheets to the 3 that are listed.

Click Insert. Click Worksheet. The first new worksheet is Sheet4 and it was automatically put in front of Sheet1.

Repeat this procedure if you need to add in more worksheets.

You can rearrange the worksheets by dragging and dropping the worksheet tab (Sheet1, Sheet2, etc.) to put them all in order. A small black triangular point will indicate where the dragged worksheet will go.

Rename the worksheets to something more meaningful. Double click on the tab for the worksheet name, such as Sheet1. This will highlight the name. Type in a new name then hit the Enter or Return key. Generally, Sheet1 is renamed to Title. Use Sheet2 to hold all your data. You may want to name it Overall, MasterList, All Exams, SP04 Class, etc. Rename all the other worksheets.

Keep the worksheet names as short as possible because there is a limited amount of space on the worksheet line at the bottom of the screen. If you have a large number of worksheets you may not be able to see all the tabs at one time. You can use the arrows at the left side of the bar to move from worksheet to worksheet. (This does not change the Active Sheet; it just moves the list of tabs from side to side.) The first arrow will move you directly to the first sheet. The last will move you directly to the last sheet. The 2 in between will move you 1 sheet at a time.

You can always tell which sheet you are viewing by looking at the worksheet name in the bottom bar. It will always have a white backgroud (the Active Sheet).

Why a Title Page is a Good Idea:

The purpose of a Title Page is to summarize the data included in the spreadsheet, to list the functions and formulas used, and, basically, to give a viewer an idea of what you did, how you did it, and why you did it. This is useful if someone other than yourself needs to work with the spreadsheet.

Add Column Headings to the Masterlist Worksheet:

Switch to your MasterList worksheet if you are viewing some other worksheet.

Click on the "B" (for Bold) on the Toolbar.

Type in a short name or abbreviation for the headings. Column A is for the ID, Column B is for student last name, and Column C is for student first name or initials. These 3 columns will be used for the MasterList data from the final exam or Registrar's file. In the next columns you can type in a heading for the exam, quiz, or project data you will enter later, such as, Test1 %s, Project1, etc.

 

Save the New Class File (PC):

Click Save As. If you know the the path to the folder you want to use you can type in the full name and location, such as:
c:\windows\desktop\Mktg3104\Overall

If you don't want to type all of this in you can simply click thru the folders until you get to the Mktg3104 folder. Then you can just type in the file name, such as Overall. The .xls extension will be added by Excel.

Click Save, then close the file.

 

Save the New Class File (Mac):

Click Save As. Type in a name for the new file, such as, Overall, etc.

Click Save, then close the file.

3. Copying Exam Printout Data into your Overall Excel File
^ Back to Top of Page

When you browse thru an exam printout file provided by Test Scoring you will see the Overall Class Statistics, an Item Analysis, a Student Grade Listing (with Names)and a Histogram.

The purpose of this section is to move a copy of the Student Grade Listing (with Names) into your Overall Excel workbook.

Here is an outline of the steps you will need. Each step will be explained in detail following the outline.

1. Open the exam printout file with any text editing program, such as BBEdit Lite on the Mac or WordPad on the PC.

2. Select the Student Grade listing with Names portion (highlight).

3. Use the Copy command to put this data onto your Clipboard.

4. Close the printout file.

5. Open the Overall Excel file and Paste the data into the correct worksheet.

6. Use the Text to Columns Wizard to separate the data into columns.

7. Save and Close the Overall Excel file.

Open the Exam Printout File:

Open the printout file using your editor. If the printout does not line up in columns change the font to Courier or Courier New (these are called non-proportional fonts).

The printout files we send are plain ASCII text files. They are named 03jun020.txt, for example. The first portion of the name is the scanning date and the last 3 digits represent the file number. The .txt extension means that this is a text file.

Select and Copy the Student Grade Listing (with Names):

The section of the printout you want to copy is titled "Examinee Scores for Group 1". (If the instructor had the students code in a group number then include all the Group listings down to the start of the Histogram section of the printout.)

Highlight the first student's line of information by clicking at the left side of the line. (This student may or may not have a name listed. The top of the listing is the location for student IDs that are not in our current names file, usually due to late enrollment or, more often, to miscoding their IDs.)

Hold down on the Shift key.

Scroll down the listing until you reach the last name (or just scroll down to the start of the Histogram and scroll up a few lines to the last student).

Release the Shift key. The area you scrolled down should be highlighted. Some instructors have their students code in a Group number, if this is the case you will get some title information in the middle of your data but you can edit it out later.

To copy the selected area either use the shortcut key combination of Ctrl + c on the PC or Command + c on the Mac.Or, under Edit, click on Copy.

Close the printout file.

 

Paste the Data into the Overall Excel Spreadsheet:

Following are the steps you will use to paste your student information

Open your Overall Excel spreadsheet. It should be located in your Class folder on the Desktop unless you have moved it.

Click on the tab at the bottom of the window for the exam you are processing. This makes it the Active Sheet.

If the cursor is not in cell A1 then move it there either by simply clicking in cell A1 or by using the shortcut key combination of Ctrl + Home. (Ctrl + Home sends the cursor directly to cell A1.) If you have added in a row of column headings move the cursor down to the A2 cell.

Paste the data into the spreadsheet by using the shortcut key combination of Ctrl + v on the PC or Command + v on the Mac. Or, under Edit, click on Paste. All the data will be put into column A so everything will appear jumbled.

Skip this step if all your students are in Group 1. If you used group numbers now is the time to delete the title lines for the extra groups.

Highlight the blank lines and group titles between the groups. (You only need to highlight the portion in column A since all the data is really in this one column.)

Under Edit, click Delete.

Repeat these instructions for any other group dividers.

If column A is not highlighted do so now. Click on the first ID in column A. Use the shortcut key combination of Shift + Ctrl + DownArrowKey to highlight all the data in column A or just click and drag down the pointer to the last ID.

 

Using the Text to Columns Wizard:

Under Data, click on Text to Columns.

In the Step 1 of 3 Dialog box make sure that Fixed width has been selected (it will look like a circle with a dot in the center).

The box also contains a preview of how the Wizard thinks your data should look. Click Next.

In the Step 2 of 3 Dialog box you will see your file with the Wizard's suggested column breaks.

Remove the breaks that are incorrect by double clicking on them. You will probably want to remove the breaks within the students' name.

You can move a break by dragging it to another location.

You can add a break by clicking in the Data Preview window. A new break line will appear.

NOTE: You do not have to put breaks between all the columns if you do not want to keep the entire line of data. For example, if you only want the ID, Name, and Percent score it is not necessary for you to put breaks in the columns to the right of the Percent score. The columns that contain No.Omitted, Seat No., and Form Letter can be left together.

The Step 3 of 3 Dialog box shows you a new preview of the column breaks. You will see the label "General" above each column. This is OK for both numeric and text data.

You do not need to do anything if you want to keep a column.

For any columns you do not want, highlight it, then choose the Do Not Import button. The label above the column will change from General to Skip Column.

When you are done click on Finish. The Dialog box will disappear and your data will be separated into columns.

 

Save the File:

If everything looks OK, click on Save. This is the procedure you will use for each exam. Once the data is in the spreadsheet you can make any changes by editing the worksheet.

When to Copy and Paste the Student Grade Listing into the Overall:

You can Copy and Paste from the student listing file as soon as you receive the printout, if you wish. If you decide to do this you can do your editing in the Overall Excel file. This is the preferred method. Otherwise, you will need to keep track of all the edits you want to make after you transfer the data to the spreadsheet or you will need to create a temporary file to hold them.

Editing the printout file directly is not a good idea. If you lose the file you will need Test Scoring to send you a replacement. The corrections you have made will need to be done over.

 

Editing Exam Data in the Spreadsheet:

Open the Overall Excel file then click on the tab for the worksheet you need to add students, change grades, correct IDs, etc. You can put any students you add at the top of the file, the student names does not have to be in alphabetic order. Make sure you put the data into the correct columns and have no blank lines within the file.

To add a row at the top of the worksheet click on the row number for the first student. (He is probably in Row 2-Row 1 should have column headings in it.) Under Insert, click on Rows. The new blank row will push all the other rows down by 1 row.

To delete a row of student information highlight the row number. Under Edit, click Delete. (If you just highlight the ID, name, and other data in the row then, under Edit, click Delete you will get a Delete Dialog box that wants to know if you want to delete a row or column. Click Row.)

 

Keeping Backup Copies:

Always keep at least 1 backup copy of your Overall Excel file. If something happens to your computer you can put your floppy disk copy into any other computer that has the same version of Excel with no conversion problems at all. Computers with more recent versions of Excel should have no difficulty in reading your data either.

Make new copies of the file after each exam is pasted into the Overall file. You may end up with 5 or 6 versions of the file on your disk. This is very helpful in cases where you find that you have an error and need to go back to a previous version of the Overall file. Just make sure you keep a record of the name you have used (and what the contents of the file are at that moment).

 

You may have a list something like this:

Date	Contents	Filename
Sept. 17	Overall file created, Exam 1 entered.	Ver1
Oct. 22	Exam 2 entered.	Ver2
Nov. 19	Exam 3 entered.	Ver3
Dec. 2	Master NameList entered.	Ver4
Dec. 8	Final exam entered.	Ver5

After averaging is complete you will want to make a copy of the last version of the spreadsheet for your backup disk.

In addition to Excel files, you may want to keep a copy of each of the exam printout files too.

 

4. Creating a Masterlist of IDs & Names
^ Back to Top of Page

Why This List is Needed:

This list will contain the IDs and student names. This is the list with which you compare all other exams. If a student is not on this list none of his exam grades will appear on this worksheet, although he may be appear on 1 or more of the exam worksheets. This list is the foundation of the worksheet even though it may be added to the Excel file late in the semester.

You can create this list from an exam or from the official class roll.

Creating the List From an Exam:

You can use the same printout file you used to import the exam, just eliminating all the information except the IDs and names.

See "Copying Exam Printout Data..." for step-by-step instructions.

Creating the List From the Class Roll:

You can access your current class roll on Blackboard or HokieSpa. These sites allow you to download the file to your PC or Mac. Both sites will have directions for doing this.

Page last updated August 7, 2008 .