Creating Gradebooks Using MS Excel


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:


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.


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)

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

Abby Milon
College of Health and Public Affairs Abby     Milon Education has always been an important and valued part of my life. My parents impressed upon me the need to obtain an education because unlike material items an education cannot be taken away from you. I obtained my Bachelor of Science in Agriculture, Master of Science and Juris Doctor from the University ...

Tison Pugh
College of Arts and Sciences Tison   Pugh The one common feature of all medieval literature, despite differences in authors, cultures, and genres, is that it is very, very old. When beginning my courses, I often face resistant students who have predetermined that, because of its age, the literature under examination is useless, if not altogether d...

Kenneth White
College of Business Administration Kenneth  White Formal education is highly correlated to national productivity and individual accomplishments. The skills that students develop help a nation’s productivity while at the same time reward individuals for their commitment to excellence. Teaching is, I believe, one of the most important endeavors taking pla...