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!

 

Resources

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.

Resources
SchoolTool Download

SchoolTool Home Page

The SchoolTool Book

References
SchoolTool Book. (n.d.). GNU General Public License. Retrieved from http://book.schooltool.org/system-requirements.html.

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

All screenshots were taken from the SchoolTool Web site.

Tellico -Organize Your Collection

As the new school year approaches, I thought that I’d shift gears again and write about something every teacher could use, but that few do: a means to electronically manage your classroom library and other resources. Tellico is an open source application that allows users to do just this. Tellico has been developed for the K Desktop Environment for UNIX and Linux, but is also available for Microsoft Windows and runs fine in UNIX/Linux without KDE. With Tellico, users can organize books, comic books, music and other media.

tellico,energize education

Tellioc’s opening screen

Upon launching Tellico, it can be seen that there are no surprises in terms of its interface. There is a menu at the top of the screen with a toolbar below this and a search tool to the right of the toolbar. Below these are three panes: one long one on the left and two panes, one on top of the other, on the right. The pane on the left lists authors for the given category. The top pane on the right lists books by the selected author and the bottom right pane provides information about the selected work, as shown in the screenshot.

Everything that you can do with Tellico can be done through either the menu or the toolbar. For example, clicking on the New button on the toolbar provides you with a list of catalogs that can be created. Here are the types of items that Tellico can be used to organize: books, bibliographic entries, comic books, videos, music, trading cards, coins, stamps, video games, wines (probably not at school, but home?), board games, and file listings. Plus there is a generic template available for other items not included in this list.

tellico,energize education through open source

The Search window

Once a type of collection has been established, most of the routine tasks can be handled using the toolbar. Tool tips provide users with more information about each button. For kicks, click on New and select New Book Collection. Now, let’s just jump in an do a search together. Clicking on the Search button opens the Internet Search window. Items can be searched by Title, Person, ISBN or Keyword. For my search, I chose HTML, XHTML & CSS by Elizabeth Castro. You may choose your own book.

My previous experience as a copy cataloger in a local library has taught me that the ISBN is often the fastest way to search, so that is the search criteria I will use. I select ISBN from drop-down menu under Search Query and type me book’s ISBN in the Search field left of this. You can also search for multiple ISBNs by clicking the checkbox next to Multiple ISBN/USP Search to the left, just below the Search field. To the right of this, select your Search source. Options include the Library of Congress (US), Google Book Search and ISBNdb.com, among others. I chose the Library of Congress. When ready, click the Search button right of the right of the drop-down box.

tellico,collections,energize education,christopher whittum

My book has been found!

Surprise! My first search produced no results. I then tried searching ISBNdb.com and found my book. The key here, folks, is to be persistent and to be prepared to alter your search criteria. Just because the item doesn’t turn up, doesn’t mean that it isn’t out there. Notice that publication and cataloging information appear in a pane at the bottom of the Search window. Click the Add Entry button and the item will be added to your new catalog. Clicking the Save button opens the Save As dialog box. Here you can name your collection and select where to save it. All collections are saved in Tellico’s native format (.tc).

One of Tellico’s strongest features is the ability to customize fields of data for a given type of catalog. Clicking on the Fields button opens the Collection Fields window. Here fields can be removed, added or modified as users would like. Very useful for customizing your database. Another wonderful feature is the capacity to check materials out to borrowers. Simply click on the item in question, click Collection and choose Check-out… and the Loan Dialog window opens. Here you provide the borrower’s name and, optionally, a due date via the integrated calendar and you’re all set. You can even add a reminder to the aforementioned calendar.

tellico,collection organization,energize education

A new entry in my catalog!

The Settings menu provides easy configuration in a number of ways. The Filter option allows for querying of your collections using a wide range of criteria. Tellico can also be used to generate bibliographies for collections, something that could be very helpful with student research projects. The Configure Tellico option allows users to configure Tellico’s general functioning, printing, templates and data sources. Librarians should note that with the yaz library installed, Tellico can access z39.50 servers and read MODS and MARC (USMARC/MARC21 and UNIMARC) formats. I have been unable to determine, either way, whether or not Tellico supports exporting to MARC format. Finally, Tellico has a wonderfully integrated help feature.

Tellico could be just the thing you need to track classroom resources. You could even set up an old laptop in your classroom for just this purpose and have students do data entry for your books. This would be a great way to build skills such as literacy and problem-solving. Materials could even be checked out via this laptop. So, get started now and let Tellico relieve you of the stress of worrying about lent materials.

Resources
Tellico Download

Tellico Handbook

References
Stephenson, R. (2011). Tellico [computer software]. GNU General Public License.

Stephenson, R. (2011). The Tellico handbook. GNU General Public License. Retrieved from https://docs.kde.org/trunk4/en/extragear-office/tellico/tellico.pdf.