Grade Centre - Upload grades from Excel
Not all assessments can take place inside the LMS (e.g.online file submissions). For these cases, you can use the Grade Centre to record and share marks for assessments that have been taken place externally (e.g. oral presentations or paper exams). To do this, you will need to create a manual column and have a list of the results that students have achieved.
In this example, a "Final Exam" column will be created in the LMS, where the results data will be manipulated in Microsoft Excel and the marks will be uploaded to the Grade Centre.
1. Create a column in the Grade Centre
- Open the Grade Centre by going to Control Panel > Grade Centre > Full Grade Centre
- Click the Create Column button at the top of the page
- A new page will open. Enter a Column Name (we will use Final Exam for this example), then enter Grade Centre Display Name and Description if desired
- Select a Primary Display and Secondary Display from the dropdowns. In this example we will use the Primary Display default of Score.
- The Primary Display will be visible to both staff and students
- The Secondary Display is only ever visible to staff
- Select a Category appropriate for the column from the dropdown (e.g. Test)
- Note: You are able to make your own categories via Grade Centre >> Manage >> Categories
- Enter a numeric value for Points Possible (the highest mark that the assessment task can have)
- There is an option to Add associated rubrics, but for this example it is not needed
- More information is available in the Blackboard Assignment - Rubrics guide
- Enter a Due Date if desired. A calendar event will automatically be added to the subject calendar with a reminder for this date.
- Select from the options to:
- Include this column in Grade Centre calculations - to have the option to use this data in a Weighted Column, for example
- Show this column to students - Immediately show this information to students in My Grades. This setting can be changed at any time.
- Show statistics (average and median) for this column to students in My Grades - Automatically calculated and made available to students via a link
- Click Submit to save this column. A confirmation will show on screen, and the new column will be created at the end of the Grade Centre table.
2. Download the Grade Centre spreadsheet
The Grade Centre requires a certain spreadsheet format for the marks to be uploaded. By downloading the current Grade Centre spreadsheet, we can ensure the correct format is followed.
- From the Full Grade Centre view, select Work Offline >> Download
- Choose Selected Column from Select Data to Download, then select your new column, "Final Exam"
- Leave the other options as default for Delimiter Type: Tab and Include Hidden Information: No and Download Locations: My Computer (unless you have an explicit reason to choose otherwise)
- Click Submit. A second screen will appear.
- Click the Download button. A file will be saved to your computer with a name in the format: gc_subjectID_column_date-time.xls
- Open the grade centre download file in Microsoft Excel
Important: The downloaded file contains column IDs and that must not be removed if you wish to upload the file to the LMS once you have edited it. The number that appears after the column title is generated by the LMS and is a unique ID which must not be deleted. Without this system generated number, the data will not be recognised when uploaded.
Note that the automatic naming convention for the downloaded file may help provide information about the contents:
- gc_subjectid_downloadfilter_timestampgc = Grade Centre
- subjectID = University of Melbourne subject code (e.g. HIST10005_2018_SM2)
- downloadfilter = may be fullgc (all data in Grade Centre), column (single column of data downloaded, or svdownload (if a smart view is used to filter data before download)
- timestamp = year-month-day-hour-minute-second (e.g. 2018-10-30-17-19-09 = downloaded on 30 Oct 2018, at 5:19:09 pm.
Note: Even though this download file has an XLS extension, it is still a text file. This may cause messages to appear when opening or closing the file. Just click Yes when prompted to open and save without a problem.
3. Merge the assessment data with the Grade Centre data
Note: The number that appears after the column title (e.g. Final Exam [Total Pts: 100 Score] |1528559) is generated by the LMS and is a unique ID which must not be deleted. Without this system generated number, the data will not be recognised when uploaded.
It is assumed that you already have a spreadsheet containing the data for this assessment. We will refer to this information as your "final exam data file".
- In the downloaded grade centre spreadsheet, select all the data in the sheet (click the arrow in the top-left corner or press CTRL+A on your keyboard) then turn on the column filter by selecting Data > Filter from the ribbon.
- Sort the data by Student ID as smallest to largest.
- Switch to your final exam data file and ensure the information is also sorted by Student ID from smallest to largest.
- Select JUST the grading data for the final exam (do not select the Student ID data or the column title identifiers) and copy these cells from the final exam data file.
- Switch to the downloaded grade centre file in Microsoft Excel, then click in the cell directly underneath Final Exam (cell E2 in this example).
- Paste in the data into the grade centre spreadsheet.
- Save a new version of this spreadsheet
- Select Save As
- Give the file a logical name (e.g. final_exam_upload)
- Choose the file type Text (Tab delimited) (*.txt)
- You now have a file on your local computer that contains information in a format that may be uploaded to the grade centre.
It is not necessary to re-sort the file to be uploaded; when uploading data to the Grade Centre, it may be ordered by any column. Note that Student ID is used in this example as a trusted unique identifier found in external sources as well as the LMS. However, the LMS utilises the Student Username column to identify users when uploading this data. It is important that this column is not deleted or manipulated in any way.
4. Upload the data to the Grade Centre
- From the Full Grade Centre view, select Work Offline >> Upload
- Click Browse My Computer to locate the file created in the previous sequence.
- Leave the delimiter type at Auto.
- Click Submit.
- A screen appears asking for confirmation of the data to be uploaded. In the extended example below, you can see from the Data Preview and Messages columns that only the information in the Final Exam column is being changed.
- Click Submit.
- The updated grade centre will be visible and the green message at the top of the screen notes how many grades have been uploaded.
This guide was last updated 18 Jul 2018.
Report any errors or omissions in this guide to firstname.lastname@example.org