Using LibreOffice Calc to Make a Gradebook

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.

energize education, libreofice calc

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.

energize education,libreofficeYour 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.

energize education,energize education through open source,libreoffice calc

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.

energize education, libreoffice calc

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.).

energize education

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).

energize education, libroffice calc

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

SchoolTool: Global Student Information System

As the school year is still young, I thought that I’d move away from curriculum and focus on something every school should have -a student information system (SIS). So, what is a student information system? A student information system is a Web-based application that runs on a server. Teachers can log onto this server and put in and analyze such information as attendance and grades. Students and parents can log onto this system to view information about themselves and people under their supervision, such as attendance and grades. What sets SchoolTool apart from its commercial counterparts is that it is designed with schools in developing nations in mind.

schooltool,energize educatrion,student information system

SchoolTool’s integrated gradebook

So what can one do with a student information system? As mentioned above, data can be collected on such things as attendance and grades, but also on assessments, student interventions and student participation. This data can be used to generate reports and report cards. There is an integrated calendar for organizing school-based activities. This calendar also works as timetable management software for larger school-based events. There is a contact management component as well.

schooltool,energize education through open source,christopher whittum

Tracking student interventions with SchoolTool.

So what’s so wonderful about SchoolTool? First of all, SchoolTool is open source. Beyond that, SchoolTool is free. A further analysis of SchoolTool’s features really requires a breakdown by category. In terms of demographics and personal information, fields of demographic and other data can be customized as needed and stored for each person. These fields can contain a wide variety of data, including, but not limited to, textual, date and boolean (yes/no). In terms of contact management, all of the expected fields are present (address, email, etc.). Furthermore, a single student can be shared with multiple adult contacts and multiple students can be shared with a single adult contact.

schoooltool,energize education,chris whittum

Using timetables to organize school functions.

Documents specifying students skills, standards or outcomes can be generated. Administrators and teachers can generate reports by student, section or teacher. SchoolTool’s integrated gradebook provides an intuitive and familiar gradebook interface for tracking and analyzing student grades. Report cards can be generated in PDF format. An integrated journal allows teachers to track attendance and daily participation. The integrated intervention system can be used generate goals and to collaboratively track student progress between teachers, students, parents and other stakeholders. These are just a few of the features with which SchoolTool empowers users.

If nothing else, this article should inspire you to take a look at SchoolTool. If you’ve heard of it or evaluated it before, hopefully this will inspire to to take a second look. One of the taglines associated with this blog is “educational technology doesn’t have to be expensive.” I’d say SchoolTool is exemplary of that.

SchoolTool Download

SchoolTool Home Page

The SchoolTool Book

SchoolTool Book. (n.d.). GNU General Public License. Retrieved from

Shuttleworth, M. (n.d.). SchoolTool {computer software}. GNU General Public License.

All screenshots were taken from the SchoolTool Web site.