How To Export and Format Gradebooks in Canvas
Part I: Finish Grading
- Finish grading (this is going to be the final snapshot of what your gradebook looks like and is turned in to the office to be archived)
- Look over your assignment group weighting and make sure it’s set correctly.
- Add your Semester 1 Final Grade assignment if you haven’t already and input the students’ final grade percentage.
- Look over your gradebook and make sure you have the Quarter 2 Final Grade and Semester 1 Final Grade columns all filled out.
- Make sure all students have a score for each assignment. If you are exempting a score for a student, (I know, I said it’s not possible and technically it isn’t, read on), you would need to put a score that matches that student’s average for that particular assignment’s grade category. This would keep it from altering the overall grade for the student and is a time consuming process reserved only for extreme cases of charity. Example: student X has 89.5 in the category “Projects” and you want Assignment A (an assignment in the “Projects” category) to be not counted for Student X. You mark an 89.5% for Assignment A for that student. When you export the gradebook it will have a column marked “Current Score” and “Final Score”. The final one includes the ungraded assignments as zeros and the current one does not. We will need to use the “Final Score” to determine their final grade in the course.
Part II: Export Your Gradebook
Next we’re going to export your gradebook data into an Excel spreadsheet.
- Go into your gradebook. Click the settings dropdown (the gear icon) and select “Download Scores (csv)”.
- Click “Okay” and it will open in Excel.
- Go to File --> Save As and select the format “Excel Workbook (.xlsx)”
a. Change the name to one that follows the format: TeacherLastName-CourseName-Section-Semester#-Term-(ID).
Note: The “Section” will come from the IC course/section number which should also be listed in your Canvas long name.
b. Make sure that the “.xlsx” remains at the end of your file name.
c. Change the “where” to somewhere that you keep happy files that always turn out perfectly. Click “Save”.
d. Now you should have a sweet Excel file all ready to be transformed from the ugly duckling it currently is into a somewhat less ugly duck.
Part III: Formatting the Excel File
Now we have a big Excel spreadsheet. Armed with this, Canvas, and Genius we’re ready to make this a gradebook file worthy of state inspection!
We need to re-order the columns we have and then add some new ones. Below this list are directions on what needs to get done. Please start with number 1 and move through it. You can reference this list as you will end up with the following columns when complete:
A. Student Name (Last Name, First Name)
B. Student ID (will need to rename the column from “SIS Login ID”)
C. Grade Level (from IC, yes required, sorry)
D. Enrollment Status (EN date, first access, “Analytics” from within “People” can give you that)
E. Exit Status (W date if applicable, taken from Genius, coded yellow)
F. Semester 1 Pre-Exam Grade (or “Semester 2…”, calculated score)
G. Semester 1 Final Exam (or “Semester 2…”, calculated score)
H. Semester 1 Grade (or “Semester 2 Grade”, calculated total score, will need to rename the column from “Final Score”)
I. Semester 1 Final Grade (or “Semester 2…”, letter grade)
J. Final Citizenship (letter)
K. All Assignments follow
Note: If you have multiple final exam columns your “Semester 1 Grade”, “Semester 1 Final Grade” and your “Final Citizenship” will have different column letters to make room for your extra exam columns.
For more information about cutting and pasting columns in Excel, check out the following link:
Note: As we go, remember if it’s correct – SAVE – SAVE – SAVE as you make changes.
1. Right click on the “B” of the second column and click “delete”.
2. Right click on the “B” of the new second column and click “delete”.
3. Rename cell A1 “Student Name” and rename cell B1 “Student ID”.
4. Delete the following columns:
- Section column
- Any Progress Grade column
- Quarter 1 Final Grade column if you have it
- Current Score (second to last column)
- Any superfluous columns that you may have created, (such as progress reports or EN Date columns or any column not holding data, you can even delete your “Semester 1 Final Grade” column if you’d like as it will be the “Final Score” column that gets renamed “Semester 1 Grade” the two columns should have identical data so it may be a good practice to line up those two columns next to each other to make sure.).
5. Delete the following rows using the same deletion procedure.
- Test student row, Marco Castro (if you have him in class), and row 2 (if it’s mostly empty, that one holds the “muted” info if you've left a grade muted).
6. Rename the Quarter 2 Final Grade column to “Semester 1 Pre-Exam Grade” and rename the last column (“Final Score”) to “Semester 1 Grade”.
7. Now that we’re done pruning we need to move some info around. Right click on the “C” of column C and select “insert” which adds a blank column to the left. Do this two more times.
You now have a blank column C, D, and E. Your column F should be the “Semester 1 Pre-Exam Grade” column that you renamed.
8. Name Column C, “Grade Level”, Column D, “Enrollment Status”, and Column E, “Exit Status”. Don’t worry for now that the names of the columns cover each other up.
9. Now we need some more columns. Right click on the “G” of column G’s header and select “Insert” adding a blank column to the left. Do that a total of four times. (If you have more than one Final Exam gradebook column due to multiple final exams, then you’ll want to create enough extra blank columns to be able to move all the final exams in there. If you have three final exams then you’ll need to create 6 total blank columns instead of four for example.)
10. You now have 4 (or more) blank columns to the right of your column F “Semester 1 Pre-Exam Grade” column. We need to name them (in order), “Semester 1 Final Exam” (if you have multiple then use the header names of your multiple final exams), “Semester 1 Grade”, “Semester 1 Final Grade” and “Final Citizenship”.
11. Now your column headings should match the above list.
12. We need to add two rows that we’ll use later. Right click on the “1” of row one and select “insert” twice.
13. Add the data necessary into the blank columns we created. Grade level, Enrollment status (with an “EN” in front of the date), Exit status (with a “W” in front of the date), cut/paste the final exam grade into the corresponding column/s, cut/paste the last column (previously “final grade” now labeled “Semester 1 Grade”) and paste it over the top of the same titled column “Semester 1 Grade”, add the letters needed in Semester 1 Final Grade and Final Citizenship.
Note: You’ll be able to open your Canvas gradebook and see the final letter grades, make sure the students are in the same order. If you use a formula to convert the grade scores to letters just make sure that they are the same as the Canvas letter grades as well as what was reported to the SIS as their final grade.
14. Have you saved lately?
15. Right click on the “3” of row 3, select “Format cells”. Click on the “Alignment” tab.
16. Set the orientation to 90° and check the check mark next to “Wrap text” in the “Text control” section (middle of the current “alignment” tab). Click “Ok”.
17. Adjust the column width. Select the entire sheet by pressing Ctrl+A then double click on the line dividing column A and column B (or any other columns).
18. While the entire sheet is selected, change the font to Arial Narrow and 10 pt.
19. In the new, blank row 1, type the name of this document:
20. In the new, blank row 2, type the CORRECT grading period, see example:
2nd/4th Qtr= Fall2013 – QUARTER TWO SEMESTER GRADES mm-dd-yyyy
(mm-dd-yyyy is last day of the semester)
21. Select the entire sheet again (Ctrl+A) and select the option to add borders to all cells. From the Home tab (#1) (in Formatting Palatte on Macs, select the border type that shows all borders), select the drop down next to the border icon (#2) and select the “all borders” option (#3).
22. Students that have withdrawn need to be highlighted in yellow. Select the row with the withdrawn student by clicking on the number of the row. Then, from the Home tab (Formatting Palette à Borders and Shading on the Mac) click on the paint bucket to change the “fill color” to yellow.
23. Students that were a “NO SHOW” must be highlighted in purple. Same process as above.
24. Go to the “Page Layout” tab (Formatting Palette --> Page Setup for the Mac) and change the orientation to “landscape”.
25. In the same Page Layout area, click “Print Titles” which brings up a Page Setup screen, type the following in the appropriate fields and click “Ok”:
a. Rows to repeat at top: $1:$4
b. Columns to repeat at the left: $A:$B
This should keep your students’ info on each page and the assignments at the top of each page.
26. With the Page Setup screen still open, click the Header/Footer tab (on a Mac click “View” --> “Header and Footer…”) Look for the dropdown menu under Footer and select the footer that will show your name, course name, and page # (you may need to scroll down to find the option with your info AND the page number). Press “Ok”. Now this info will be at the bottom of each page.
27. Be sure to look over your column and row sizes and see if any of them can be “squeezed” while still showing the information (such as the width of grade columns). This will save pages in the event the Academy needs to print off a hard copy of your gradebook.
28. Look over your document and save.
29. Save a copy to your computer.
30. Send an electronic copy to Pierre Leonard for our gradebook archives.
You are done! If you need assistance with this process please email or call Neal Shebeck. Thank you for your hard work!
- Articles by Systems & Software
- Articles by Audience
- Articles by Category
- Course Work
- System Requirements
- Topic #: 16127-388
- Date Created: 01/03/2014
- Last Modified Since: 12/14/2016
- Viewed: 52