Creating Gradebooks Using MS Excel

Introduction

Grade books can be paper or electronic, though there are definite advantages to creating and maintaing them electronically. An Excel grade book calculates student grades automatically, saving hours of work. If configured properly, it can be uploaded to Webcourses@UCF Grades, and thus report grades back to students in a secure environment. Building a grade book through Excel also offers the chance to revisit the course design and the assessments that have been chosen. Do the relative weightings of assignments correctly match the desired assessment plan? Do the assessments accurately provide information about student learning objectives?

Template for Download

This is a grade book with weighted averages and multiple assessments that you can download and use as the basis of your own grade book. Note: to make this "fit" your class, you will need to not only add/delete columns, but adjust the formulas accordingly. The template includes a letter grade "lookup table" already in place and will calculate letter grades automatically. To make this "fit" your class, you'll also need to adjust the grade ranges to match your syllabus.

Download the Excel template (.xls) here.

View the six minute video on how to customize this template for your own class.

Automatically Generate Letter Grades

It is possible to use IF-statements in a formula to calculate and assign letter grades to students based on a semester percentage; however, it is simpler to use a VLOOKUP table. The template (above) includes a VLOOKUP table, which uses the syntax:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

where all you really need are the the first three operators. Lookup_value refers to the cell where the semester percentage can be found. The table_array is the "name" of the table (you will have to highlight all the cells of the table, then just above A1 you will see a spot in Excel to "name" the region you've highlighted. In the case of the example above, the table is named "grades.") The col_index_num means that the formula will look to that column (in the case of the example, the second column) as the "output" of the formula, the item to be deposited where you want the letter grade to show up.

In the case of the template above, the formula is =VLOOKUP(K3,grades,2). That exact phrase was pasted in the cell L3, and then "spread out" over the rest of the column to look up the right grade (you do this by left-clicking L3 to highlight it, move the cursor to the lower-right corner of L3 until it becomes a black plus-sign, and then left-clicking (and holding the click) while dragging down the cursor over the rest of the column).

The example VLOOKUP table in the Excel template can be adjusted for different grade ranges or to remove rows (if not using plus/minus grading).

Dropping Test Scores, Automating "Whichever is Higher", NC Grades, and "Current" Grades

Dropping the Lowest Test Score (Or possibly dropping the final exam): the MIN function
To drop just one test score, first add up all the tests and then subtract the lowest from that "range". This would look like: =(Test1+Test2+Test3+Test4)-MIN(Test1, Test2, Test3, Test4). Simply substitute the cell number for each test, and you will have the "best three" tests added together in that formula.

Dropping More Than One Lowest Test Scores: the SMALL function
To drop more than one test score, use the SMALL function to isolate the "second-smallest" or "third-smallest" number. The formula =SMALL(K1:Q1, 2) provides the "second-smallest" score in the range of cells K1 through Q1. As this only isolates what the second-smallest number is, this would need to be part of a larger formula (for instance, it could be added to the MIN function as shown above to also drop the second-smallest score).

Automating "Whichever is Higher": the MAX function
Like the MIN function, the MAX function looks between two cells and "returns" whichever is the higher of the two (it can also look for the highest score in a whole range, rather than just comparing two cells). The syntax is simply =MAX(K2, L2) for two cells K2 and L2, or you can add more cells separated by commas. This formula is often used as part of a larger formula.

Choosing NC vs F grades: the MAX function
The MAX function can be very helpful in determining whether a student deserves an NC grade rather than F. See the mathematics gradebook (below) for an example. Keeping the Spreadsheet Current "As of Today": the COUNT function
The COUNT function simply adds up how many cells are populated with content in a given range, with the syntax =COUNT(F2:M2) to search from F2 through M2. Your formula could make use of this to provide an ongoing, "as of today" grade for your students. This would be especially useful for grades that are ongoing, such as homework or tests, but will not apply to onetime events late in the semester, such as a project or paper.

Example of SMALL, COUNT, and MAX
Tammy Muhs (Mathematics) provides this sample gradebook that makes use of SMALL to drop more than one score, COUNT to provide a score "as of today," and MAX to determine if the appropriate grade should be NC rather than F.

Video Tutorials

This video describes how to build a grade book using weighted averages and multiple assessments of each type.
Note: this is the most common type of Excel grade book.

Seven minute version: proceeds through the steps somewhat rapidly.

Twenty seven minute version: provides the same information, but explains each mouse click and the set up in more detail. You may also wish to view videos in the "fundamentals" section below.

Fundamentals

Excel Basics
If you are brand new to Excel, you may wish to view this nine minute video on the basics of navigating and using Excel. It covers such topics as cells, formatting columns, auto-fit, using "drag" to extend results down the column, and the concept of using formulas.

Basics on Formulas and Algebra
Help creating the formula that captures the weighted averages of your grade breakdown. This is especially useful if you'd like a refresher on the algebra needed to provide weighted averages. (five minute video)

Grade Book Basics
Here are videos explaining how to build sample grade books of increasing complexity:

Sample #1: grade book using cumulative points for the semester (six minute video)

Sample #2: grade book using weighted averages (twelve minute video)

Advanced
After mastering the basics, many instructors opt to incorporate some advanced strategies and formulas into their spreadsheets:

Other Resources

The following websites may be helpful in creating Excel grade books:

 

Faculty Spotlight View Other Award Winners

Elena Flitsiyan
College of Sciences Elena  Flitsiyan Teaching undergraduate physics is the most satisfying aspect of my responsibilities as a faculty member at UCF. I am of the firm opinion that modern society requires a better understanding of the mechanisms governing our universe, at all scales, for us to fully develop ourselves in a way respectful to our enviro...

John Schultz
College of Sciences John  Schultz As a teacher, I believe my role is to generate excitement and interest in the classroom and to challenge students by providing a dynamic and experiential learning experience. Experiential learning not only increases student interest by building a learning community in the classroom, but also increases student unde...

Regina Gresham
College of Education Regina    Gresham My responsibilities are plentiful as I seek to inspire my students with a desire to learn. I consider education to be invaluable. I find the establishment of a positive, caring learning environment, one that encourages students to "believe in yourself with dedication and pride" to be priceless. It is an envir...