Using Excel to Manage Grades in Ted

Often instructors or TAs have need to use an external editor to manage their students’ grades, such as Microsoft Excel. This instruction sheet will assist TAs and professors in:

• Downloading the Class Data from Ted
• Editing the Data in Microsoft Excel
• Uploading the Edited Data to Ted, and
• Making Grades Available to Students

The advantages to managing grades in this fashion include:

• Retain a local copy of students’ grades on your computer
• View and edit the table all at once in Excel
• Utilize functions such as lookups, summation, distribution with which you may already be familiar in Excel, and
• Avoid retyping or reentering data, including students names, once they are available in Ted.

Downloading the Class Data from Ted

To manage Ted grades in Excel, you should first download a spreadsheet from the class's Grade Center. This will give you a spreadsheet that is properly formatted to copy grades back in to Ted's Grade Book.

1. Go to your class's Full Grade Center by clicking the arrows that follow Grade Center in the Control Panel.

2. From the Work Offline button above the right side of the gradebook, select Download.

   

3. Ted presents you with a number of options for how to download the spreadsheet. Under Data, you can Select [which] Data to Download. If you plan to use or enter many grades in Excel, select Full Grade Center. If you will only be adding new columns, select User Information Only.

   It is important to keep in mind that if you do not download a column at this point, and later add a column of the same name to the spreadsheet, it will be duplicated when you upload it back to Ted - you will not have an option to 'match' it to an existing column.
   
   This can especially be an issue if your class is exceptionally large: For such classes, you may encounter an error when you try to upload your gradebook. In that case, the only solution is to split the gradebook in half and upload them separately. If you have not created the columns before downloading the gradebook, that will result in duplicate columns.

   

   The other options you may leave unchanged, unless you have your own reasons for changing any of them. ClickSubmit to continue.

4. Ted will then package your gradebook into a file, and present a Download button; click it to download your gradebook.

   

   Bug in download

   The current version of Ted contains a bug: The first cell of the spreadsheet may show "Last Name" when you open it. If that is the case, clear the cell's contents and enter just Last Name before you upload your scores to Ted.

   From Firefox or Internet Explorer, the file you download will have the extension '.xls'. However, note that this is not a regular Excel file, but a tab-delimited or comma-separated file that has been given the .xls extension to ensure that it opens directly in Excel.
   
   Because of this, you may get an error stating that The file you are trying to open is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?. Click Yes to open the file.
   
   Excel will open the file as "[Read Only]", meaning it will not allow you to make changes to the original file. You should go to the File menu and select Save As before continuing. Set Save As Type to either CSV (Comma delimited) or Unicode Text (*.txt), and save the file.
   
   Excel will then warn you that your gradebook may contain features that are not compatible with this file type. Click Yes to continue.

5. Internet Explorer and Firefox will ask what you want to do with this file. Save the file somewhere you can easily access, such as your Desktop or a folder you use for other class files.

Editing the Data in Microsoft Excel

The first five columns of the gradebook are "Information Columns" - that is, information about each student in your class. These are read-only, and if you make changes, they will be ignored. Do not change or delete the Username column. You may remove the other columns ('Last Name','First Name','Student ID', or 'Last Access') if it is convenient for your purposes, but the Username column must be present and must appear before any "Grade" columns.

"Grade" columns, in this case, are any columns in the spreadsheet besides those five "Information" columns. Any grade columns you download from Ted will include two pieces of information: The column's name in Ted, and a number unique to each column in Ted. For example, "Midterm 1" will show up as "Midterm 1|4847".

To add a new grade column, simply enter a name for the grade column in the first row, in the column following the rightmost grade in the spreadsheet. Enter your students' scores in that column.

When you are finished, save your changes by going to the File menu and selecting Save. Excel will warn you that the workbook contains features that will not work or may be removed if you save it in the selected file format. ClickYes/Continue to keep the workbook in this format. You can then close the workbook or quit Excel. (Excel will ask you again if you want to save your changes, even if you didn't make any. Click No.)

Uploading the Edited Data to Ted

1. Return to the Full Grade Center for your class. From the Work Offline button above the right side of the gradebook, select Upload.

   

2. Next to the upload field labeled Attach Local File, click Browse (in Safari, Choose File), and choose the file you saved containing your grades. Click Submit to continue.

   

3. The Confirmation page will show the columns being imported, and note if anything was changed for each column. In the example below, the Messages column notes when there are "No Data Updates", if it will "Add Column" or "Update Name", and that it will not make any changes to calculated columns. The columns that have changes will show the new scores for each student in that column for you to review.

   The Upload column contains a checkbox for each row. If any of the messages indicates a change that you do not want, uncheck this box to skip that column.

   Click Submit to continue.

   

4. Ted will return to the Full Grade Center, and display a note summarizing the changes made to the gradebook, including the number of columns uploaded and a list of the changed and added columns.

Making Grades Available to Students

Grades are available to students immediately, but are not immediately included in point totals and weighted columns. If you only need students to see the scores you have uploaded, and do not need to include them in later calculations, you are finished!

First, some background. For every column, Ted tracks the Points Possible for that column. That allows Ted to convert a score in that column to a percentage, or to a Letter Grade. It also allows a score to accurately reflect its contribution to Total or Weighted Total columns.

However, when you upload a new column, its data is considered "Text", not a numeric Score. Since you cannot perform math on text, it cannot be included in any further calculations. By changing the column to display something besides "Text", you are telling Ted to convert that text into a numeric Score, which may then be used for further calculations.

Ted can also display Scores as a Letter Grade. It maintains one or more Grading Schemas - that is, a table that lists which scores correspond to which grades, and vice versa. It contains one built-in Grading Schema called Letter. You will see this listed when you decide how to display the column.

You can change which scores correspond to which letter grades from the Full Grade Center, by going to the Managemenu and selecting Grading Schemas. You can create your own grading schema from scratch by clicking Create Grading Schema. You can re-use an existing schema by selecting from the button next to the Letter schema, select Edit to make changes to it, or Copy to use it as the starting point for a new schema.

1. From the button's drop-down menu in the column header, select Edit Column Information.

   

2. In the Column Information section, change the following fields:

   • Points Possible: Enter the number of points that is considered a full score. Extra credit is allowed - for instance, if the Points Possible on an assignment is 100, you may give a student 110 points. It is important for the Points Possible to be accurate if you will be using a Weighted Column to calculate your final score, or if you would like to display scores as percentages or as letters, since it uses the Points Possibleto determine the percentage, weight, or grade on the letter scale.
   • Primary Display: Change this to Score, Letter, or Percentage. With any one of these options, Ted will convert all the scores in this column to numerical values out of the points possible you entered above.
   • Category: Ted can easily combine multiple columns in your class into a final grade when you identify aCategory for every one of your columns. For example, if you are uploading scantron scores from a Midterm exam, the Category could be Tests.

   Click Submit to continue.

   

   Ted will warn you that When converting text columns, all numeric values will be treated as a score out of the defined points possible and used to translate the value to other display options. Values that cannot be converted to the selected type will display as zero after the conversion. Continue? Click OK.

   Unfortuantely, if you enter letter grades into the Excel spreadsheet, even valid letters from the grade schema will be converted to zero points. However, if you type a letter grade directly into the Full Grade Center, the percentage from the Will Calculate As column in the Grade Schema will be used for the score.

You are finished (phew)! The grades are now available to students and displayed as you like.

If you have any questions with this process please feel free to email IWDC at iwdc@ucsd.edu, or call 858.822.3315.