Google Sheets is a web-based spreadsheet application that provides an efficient way of automating and sharing a breakdown of students’ grades. This application is part of the Google Docs Editors suite of productivity software, which includes Google Docs, Slides, Drive, and others. Google Sheets is is a free alternative to Microsoft Excel, which shares similar features but can be cost-prohibitive for some educators. Additionally, the spreadsheets can be viewed anytime on the cloud with a Google account and internet access. This allows students to monitor their performance in the course and to potentially improve their scores.
A Few Useful Google Sheet Functions
This grading spreadsheet can be utilized more effectively by using spreadsheet functions that automate or improve the grading process. Functions are commands that can be typed within a spreadsheet cell, prefaced by an equal sign (=). This article provides a guide for the breakdown of the more complex functions (e.g., COUNTIF, SUMIF, RANK, VLOOKUP) and a few visual display commands with some examples. Students can easily access detailed updated breakdowns of their grade online, and it offers them the opportunity to become more proactive in improving their English abilities in class.
Setting up the Google Sheet
This academic grading system is easy to implement. First, send a link of the grade spreadsheet to the students. The link is only for viewing and does not give them editing privileges. Students are then taken through the various columns with grade components and corresponding percentages indicated. Finally, they are asked to save the link on their internet browser for convenient access.
Although not a complete solution, to address possible university administration or students’ concerns, students’ names are hidden and only their ID numbers are shown. Fortunately, in two years of using this system I have not received any complaints. Other teachers’ experiences may vary depending on the university or department.
COUNTIF for Checking Attendance
COUNTIF (see Figure 1) tallies a total number of characters from a range of cells that meet a certain condition. By utilizing COUNTIF, teachers can manage attendance records and identify students who may require intervention due to habitual absences or lateness. Furthermore, COUNTIF can also be applied to track other class measurements, such as submissions counts and providing insights into student engagement.
Equation: =COUNTIF (CELLRANGE, “CHARACTER”)
In this equation:
CELLRANGE (AC6:AP6) represents the range of cells containing attendance data, spanning weeks 1 to 14 of the semester under the Absences column.
The “CHARACTER” parameter denotes the condition that COUNTIF should count. In this scenario, “x” indicates an absence, while “o” signifies attendance.
Under the Absences column, CELLRANGE (AC6:AP6) includes class attendance from weeks 1 to 14 of the semester.
Figure 1
COUNTIF Example
SUMIF for Tracking Classwork
The SUMIF function (see Figure 2) enables users to aggregate values within a specified range based on predefined criteria. Similar to COUNTIF, using SUMIF helps students keep track of multiple submissions as well as understand the consequences of missed assignments but with an emphasis on in-class activities or assigned homework.
Equation: =ARRAYFORMULA(SUM(IF(CELL
RANGE=”CHARACTER”, NUMERICALVALUE for CHARACTER , NUMERICALVALUE for NOCHARACTER)))
In this equation:
CELLRANGE (G5:P5) represents the range of cells containing submission data for the “Warm-up Writing” column. The characters “o” and “x” indicate submissions and non-submissions of writing activities, respectively.
When the character “CHARACTER” (e.g., “o”) is found within CELLRANGE, the corresponding NUMERICALVALUE for CHARACTER is assigned (2). Conversely, when no such character is found, the NUMERICALVALUE for NOCHARACTER is applied (0).
ARRAYFORMULA function is employed for calculations involving multiple values produced by the IF function, ensuring correct calculations across the entire range. Its inclusion is necessary to prevent errors, such as “#VALUE!”.
Figure 2
SUMIF Example
RANK for Peer Grade Comparison
The RANK function (see Figure 3) sorts the table numerically and assigns a numbered ranking. The incorporation of rankings alongside total grades shows students their academic standing among their peers in class. This adds an element of competition and pushes students to exert more effort in class. This transparency may also encourage support among students who are underachieving.
Equation: =RANK(CELL, CELLRANGE, FALSE)
In this scenario:
The Grade column displays students’ total grades, with each student’s grade referenced by the CELL (AA9).
The CELLRANGE (AI$4:AI$29) represents the range of total grades for all students in the class.
By utilizing the RANK function with the FALSE parameter, grades are ranked in descending order. This means that higher grades receive lower numerical rankings, indicating superior performance.
Figure 3
RANK Example
VLOOKUP for Importing Data
VLOOKUP is a function that retrieves data from different sources by searching for a specified value and returning a value in the same row from a specified column. This function is useful because it eliminates the need to manually transfer data from different locations of the spreadsheet. In this regard, it allows the teacher to focus on more important aspects of teaching, such as curriculum and professional development.
Equation: =IFERROR(VLOOKUP(CELL, CELLRANGE, VALUE1, FALSE), VALUE2)
In this equation:
CELL (C11) represents the value to be searched.
CELLRANGE (raw!B:C) indicates the range where the search will be conducted, with “raw!” indicating a separate tab in the spreadsheet.
VALUE1 (2) specifies the column index from which the value should be retrieved if a match is found in CELLRANGE (raw!B:C). In this case, it’s the second column (column C).
FALSE indicates that an exact match is required for the search.
If the VLOOKUP function fails to find a matching student ID, the IFERROR function ensures that it returns VALUE2 (0 in this case), preventing errors in the formula.
Figure 4
VLOOKUP Example
Additional Visual Options
The following Google Sheets’ features provide different cell display options and are accessed from the drop-down menu bar located in the top part of the application (see Figure 5).
Figure 5
Drop-Down Menus
Conditional Formatting for Highlighting Data
Conditional formatting (see Figure 6) is a display tool in Google Sheets that allows changes to the design and appearance of cells. After selecting this feature under the menu as FORMAT-CONDITIONAL FORMATTING, it gives you more design options when certain cell parameters are set, such as changing the cell’s color background.
Figure 6
Conditional Formatting Example
Using conditional formatting, you can assign different colors to cells. In this case, the colors of gold, silver, and bronze were given to the top three values, mimicking the medals used in a competition. Other uses of conditional formatting include highlighting the highest scores of a column range or showing if a student has passed or failed the class by using the highest exam score.
Insert-IMage for Adding Images
Utilizing the menu item INSERT-IMAGE, you can add photos within the cells. As shown in Figure 7, an image of a university logo was placed on the upper left-hand side of the spreadsheet, adding a professional appearance to the spreadsheet.
Figure 7
Logo
Dropdown for Checklist
By selecting INSERT - DROPDOWN from the menu, you can create display text as a list of choices that serve as a reminder. In one application, this function (See Figure 8 under the column Workshop) indicates whether students have received feedback or not, labeled by either “done” or “not yet.”
Figure 8
Dropdown Example
Conclusion
Based on a year’s use of Google Sheets, the result of this shared grading policy and spreadsheet application has been overall positive to both the teacher and the students. Some students asked to resubmit low-scored assignments and take more responsibilities during class activities, potentially leading to higher overall grades. As a teacher, the ease of customizability and automation has influenced me to consider different ways of making grading more transparent, efficient, and interesting for the class. As such, I encourage teachers to try out this application, either as a viable replacement or as a supplement to their assessment needs.
