Tech Tip #3 – Making a Gradebook in Excel, Part 1

One popular use of Excel among faculty is to keep your own gradebook of your students’ grades. This is an excellent way to look at several useful features in Excel, so I’m going to step through this exercise in several parts.

In this part, we will look at how to format your gradebook.

Open a new Excel file. We’ll make some column headings for student names and the graded assignments in your course. In the top left cell, type “Student Name.” This text will typically spill over into the next cell (see picture).



I recommend you make this first column wider so the text fits entirely on one line in that column. To do this, move the cursor to the dividing line between columns A and B. When it is in the right place, it will turn into the cursor with arrows pointing left and right (see picture)



Hold down the right mouse button to drag the column to the width you desire, then release.



You can do this for other columns, such as homework assignments, tests, and student ID number (NOTE: if you keep ID numbers in your spreadsheet, be sure you store the Excel file on the network server where it will be secure).



In the picture above, we have multiple columns for homework assignments. The word “homework” is very long compared to the number that will likely be recorded in each column, so will take up a great deal of horizontal room in your spreadsheet. In addition, you may wish to have some better grouping of assignments. To address both of these, let’s create some headers.

First, click on the “1” that designates row 1. This should highlight the entire row.



Then go to the menu Insert-Rows. This will insert a row above the selected row




Now, you can type in the header “Homework” and just the assignment number in the original row.



To make this a little more neat, we can also shrink the column sizes so they take up less space. Hold down the right mouse button on column B and then drag the cursor across all the columns with homework assignments – this will select them all.



Then, resize the first column to the size you want – all the other selected columns will resize to the same width.



For even better visibility of your headers, use bold, or larger font. See example with some student data. I HIGHLY recommend you put the class and semester in your gradebook (as in the example below) just to reduce confusion. As an exercise, try to reproduce the spreadsheet below, or create a similar format for your class.



That’s enough for now. Next time, we’ll go over some functions to ease the pain of calculating grades.