Creating An Excel Gradebook

Creating a Excel gradesheet doesn't need to be difficult

Example Gradesheet

USCTA - Wiki Gradesheet.xls (created by Greg Placencia)
This will help you develop your own grade sheets. Be sure to save the file.
There are three sheets in the gradebook.
"Class List" holds all basic student data: name, email, etc. The names automatically are transferred to all the other sheets so you don't have to copy them.
"Grade Summary" compiles all the data. The percentages on top can be changed to suit your needs and will automatically weigh the final total.
"Homework" can be used to keep track of homeworks and give an average. This average is automatically transfered to the "Grade Summary" and weighed depending on the values you give.

Why should I use Excel to keep my grades?

Excel can be a very powerful tool for keeping track of grades. Even a very simple worksheet can be used to keep track of names and automatically calculate grades. More powerful functions embedded in the worksheet can include those that calculate grade distributions and even that chart student progress. With a little imagination and some time, Excel will help you see class standing - and even that of a single student - to improve student progress better ensure accountability for instructors.

Creating a list of student names

Creating a list of students can be very time intensive. More often than not you will be given a department printout of names that requires you to enter them or transfer them to course management software manually. Blackboard and many other course management programs offer a shortcut through their gradebook module, however.
The first step is to export the gradesheet. In blackboard you can do this by going to Gradebook (found in Control Panel -> Gradebook) and clicking "Download Grades". This will give you a listing of all the students officially enrolled in your class on Blackboard. It's a basic list, and you may need to clean it up, but there are ways that Excel can be invaluable in managing the list.
In conjunction with Word, for example, you can even develop a quick means of wrangling names for even the largest classes. For example: cut and paste the worksheet into word and then convert to text to use the search-and-replace function. This will help you isolate the first from the last names, which can then be converted back to a table (one column for first and another for last names. Plugging this back into a worksheet, you can then sort by first or last name. Next, you can concatenate the first and last names into a third column using the "&" function, with last name appearing first. Excel is very flexible with text once you know how to use it.

Tracking student data

Excel is first and foremost a matrix. If you thought basic functions defined the software, you have overlooked one of the most powerful features: making information visible. At a glance you can see how students are performing in relation to their peers; whether they have completed all of the assignments; and even what is the chosen topic of their term paper. But that's not all: Excel allows the teaching assistant to make stand out data that is aberrant. Has an assignment not been completed? Has a paper topic not been approved?
Excel has somewhat sophisticated 'conditional formatting' tools that allow you to specify text and cell attributes based on cell content. If you want to highlight students that have not returned a paper topic as requested, you can format cells to indicate a color (red, say) that conspicuously reminds you of the late responders for which no topic has been returned. Likewise, you can indicate green where the topic has been approved. You can also combine conditions for a given range of cells, too.


Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.