In this era of Student Information Systems, the idea of using an electronic spreadsheet to create a gradebook may seem a bit outdated, but not all schools or districts can afford an SIS or have the resources to implement one. Furthermore, homeschoolers hardly need an SIS to track student performance. For these groups, an electronic spreadsheet makes a fine gradebook and, in keeping with this blog’s focus on open source, I’m going to use LibreOffice Calc, a spreadsheet in an open source office suite, to do this.
Look at all those wonderful cells!
First, a quick vocab check. A spreadsheet is an application designed to work with numbers. It’s divided into columns and rows, much like a checkbook register or ledger. An individual spreadsheet is referred to as a worksheet. Columns are identified by uppercase letters, called column headers. Rows are identified by numbers, called row headers. An exact spot where a column and row meet (e.g. A1) is a cell. An individual cell would be identified, as shown above, by putting the column and row headers together (e.g. A1). When a cell is clicked on, it becomes the active cell, which is indicated by a bold outline around the cell. While working with the cells of a spreadsheet, the mouse pointer turns into a rectangle with which individual cells can be selected, or multiple cells, when clicking-and-dragging. Cells can be customized in terms of the data they contain (text, number, etc.) as well as in terms of physical appearance (font color, background color, etc.).
While typing in a cell, the contents will appear in the formula bar below the menu and toolbar near the top of the screen. Near the bottom of the screen are sheet tabs. Tabs allow users to have multiple worksheets in one spreadsheet. This is ideal for a subject in which multiple types of data are collected (e.g. homework grades, assessment scores, etc.). Simply click the + to add a new tab as needed.
Your first step is to determine what grades you are going to record. This is important as it affects the labels for each column head, among other things. A spelling grade book might just have a date that corresponds to each weekly spelling test. A math grade book might include multiple worksheets (one for homework, one for classwork, etc.). For the sake of this article, I chose to create a math grade book. Put some thought and planning into your gradebook and you’ll get more use out of it.
In regards to my platform, I’m using LicreOffice 5.0 on Xubuntu Linux 16.04.4. You should be able to do what I’m going to show you in any spreadsheet application, not just Calc. You may want to consult your application’s documentation in regards to formula syntax. Let’s get started and open Calc (or the spreadsheet application of your choice). At the top of each column, you will want a column heading that will serve as a label. The first cell in each column is reserved for a label and is exempt from sorting by default (we’ll take a look at sorting shortly). In the first cell of the first column, type “Student Name“. I’m putting student names (last name (well. the first initial of the last neme for the sake of confidentiality), first name) in one cell so that they can be sorted alphabetically. Thus, if I get a new student, I can add his or her name to the gradebook and re-sort the names alphabetically so the new student’s name will be sorted in with the others. The next header depends on the subject for which you’re making the gradebook. As this tab will be for homework grades, I’m using dates.
Resizing cells is easy.
If you look at my screenshot, you’ll notice that my “Student Name” label doesn’t quite fit. Most spreadsheet applications allow for easy column-width adjustment, which certainly aids in legibility. Positioning the mouse pointer between two column headers turns it into a double arrow. Just hold down the left mouse button and drag to the right to resize the column (as shown in the screenshot). Row height can be adjusted in a similar fashion. Regarding legibility, I also increased my font size from the default of 10 points to 12 points. To do this, click on the “button” to the left of the A, B, C,… column headers and above the 1, 2, 3… tow headers. This selects all cells in a worksheet and works in every modern spreadsheet application I’ve used. Once you’ve done this, any changes you make (e.g. font size) will affect the whole worksheet.
Click and drag to automatically fill in dates.
In the first cell of the second column (B1), I typed the date (9/1/17) which is automatically formatted as 09/01/17. Hit the Tab key, which moves the cursor to C1, and enter 9/8/17. One of the cool things about electronic spreadsheets is that they can detect patterns. Click on CI, drag over cell D!. Now move the mouse pointer to the lower right corner of cell D1. It should turn to a set of cross-hairs. Click and drag a few cells to the right. and continue on to select cells EI, Fi and so on. The dates will automatically be filled in. Thus you can fill in dates for the entire semester/trimester/term). For the last column (column I) of the semester/trimester/term, type in the heading of your choice (Average, Grade, etc.).
Look at the formula in the formula bar.
In cell I2, type the following (also shown in the formula bar screenshot): =average(b2:h2). In human terms, this means that cell I2 will present the average for the numbers listed in cells B2 through H2, as seen in the screenshot. As I look at my results, I see that my averages contain multiple decimal places. This is fine for complex mathematics, but I just want to display to the average to the closest half point. Right-click on the I column header and choose Format Cells.... In the Format Cells dialog box, click on the Numbers tab. Below the Category field are available Options. From here, change the number of decimal places from the default (blank) to 2 or 1, depending on what your desire is, and click OK. After having done this, you’ll see how much more approachable the numbers are (check out the screenshot).
Our finished gradebook
One more thing that I’d like to share is the ability to rename worksheets to help yourself and others navigate your gradebook. Double-clicking on a sheet tab opens the Rename sheet dialog box. Simply type in the new name and click OK or press Enter. It’s that easy to customize your new gradebook. You should now be able to create a gradebook yourself using a spreadsheet application. If you have any questions, please contact me. Good luck creating gradebooks!
LibreOffice Web Site