Google Sheets: Clear Visuals for Tracking Student Success

by Christina Hyatt, OTAN Subject Matter Expert

Using Google Sheets in a shared drive helps teachers and staff track student progress quickly and easily. It combines relevant student data in one place, including work completed, CASAS scores, and graduation progress. Before Google Sheets, student progress was paper-based and stored in the classroom. Now, staff can view and update information online, anytime, from anywhere.

This shared tracker solves three problems:

1. Combining student progress and testing data in one place.
2. Allowing all involved staff—teachers, paraeducators, counselors, and administrators—to view updates as needed.
3. Filtering and sorting using technology to find student data quicker.

The tracker makes it easy to manage data for 200+ students across multiple course sections and learning formats. This guide explains how to use it, customize it, and apply it in different programs.

Understanding the Data

The tracker has several tabs to organize and display student information.

Check-In Tab

This tab keeps a record of all student-teacher interactions, such as weekly Zoom meetings, emails, and in-person check-ins. It also tracks key updates, including reasons for absences, plagiarism violations, welcome email correspondence, and awarded registration hours. The key across the top reminds users how to code the interactions. Programs can adjust this tab to fit their unique needs.

Figure 1

Check-In Chart

The “Check-In” Tab shows various interactions with students. Student names are formatted to turn yellow when registration hours are awarded.

CASAS Tab

This tab is used to track CASAS initial test scores and monitor student progress in achieving gains. Conditional formatting is applied to highlight performance levels: blue indicates ABE-level scores, while yellow represents ASE-level scores. Teachers rely on this tab to track post-testing status and identify which students still need to achieve gains. Additionally, the tab stores essential information, including student identification numbers, email addresses, and their learning modalities.

Figure 2

CASAS Tab

CASAS tab uses formatting colors to easily identify students who still need to be post-tested.

Filters make it easy to track students who need follow-up testing. A built-in conditional format highlights student names in pale yellow when the row indicates "stop" or "done," signaling that post-testing is complete. Filtering the first column by color quickly identifies students who still need to post-test, allowing teachers to send email invitations. This feature is especially important for managing testing when the majority of the students are learning online.

Figure 3

Filter by Color

Filter by color makes it easy to see which students have finished post-testing and which students still need a post-test.

Courses Tab

The "Courses" tab outlines each student's pathway to graduation, tracking progress across 25 academic courses with clear color-coded indicators. Conditional formatting shows:

• Light pink ("x"): The student completed the course in a previous school year.
• Dark pink ("c"): The student completed the course in the current school year.
• Neon green ("e"): The student is completing the course online using Edgenuity.
• Blue ("p"): The student is currently working on the course using paper packets.
• Numbers: Indicate partial completion, showing how many of the 5 required credits are finished.
• White: This course still needs to be completed.

This organized layout provides a clear snapshot, making it easier to help students decide which courses to focus on next, assign new courses remotely, and track potential graduates.

Additional columns track:

• The number of courses not yet started.
• The number of courses completed in the current school year.

These tools allow teachers to monitor students who need more support, encourage goal-setting, and track progress. A running total of completed courses and the number of students who have finished them is also included for broader program oversight.

Figure 4

Courses Tab

Color coding makes it easy to see the progress of each student. Additional formulas make it easy to keep track of the progress and outcomes for the diploma course.

Potential Grads and Graduates Tabs

To support tracking the pathway to graduation, two additional tabs are used:

1. "Potential Grads" Tab:
   This tab lists students who need five or fewer classes to graduate. At the end of each year, students who do not finish are moved to the bottom of the tracker. This creates a running list of past students who could potentially return to complete their diploma. The counselor periodically contacts these students to encourage re-enrollment.
2. "Graduates" Tab:
   This tab tracks students who have graduated during the current school year. It also serves as a graduation checklist for teachers, helping ensure that graduating students have completed their post-tests, returned Chromebooks and textbooks, and met with the counselor for their exit interview.

Additionally, this tab analyzes graduates outcomes by tracking whether they were enrolled in AM or PM classes and whether they attended in-person or online. This helps identify trends and patterns in graduate success.

Figure 5

Potential Grads

The “Potential Grad” tab monitors the students closest to finishing in time for graduation.

Benefits of Using a Shared Tracker

The shared tracker enhances team communication and ensures consistent support for students. It includes links to essential resources, such as:

• For students: Welcome emails, class syllabi, and planners.
• For Teachers: Tutorials, curriculum links, and tracker instructions..
• General Resources: Orientation PowerPoint, book checkout form and spreadsheet, and a list of approved courses.

These links make the tracker a centralized hub for teachers to quickly access classroom resources and tools.

Storing the tracker and other student records in a Shared Google Drive instead of Google Drive offers additional benefits:

• Data remains accessible to the entire team.
• Managers decide what type of access each team member has. They can easily add or remove members or change their access.
• Shared ownership reduces the risk of losing important information tied to individual accounts. Unlike personal Google Drives, shared drives protect the data if a team member leaves.
• Files stored in a shared drive do not use space from your personal Google account.

This setup fosters collaboration, improves efficiency, and ensures long-term accessibility for all team members.

Steps to Download and Customize the Tracker

1. Click the link to access the tracker. (Make a button titled Adult Education Tracker with the link)
2. Select “Make a Copy.”
3. Rename it and move it to a Google Shared Drive.
4. Adjust permissions to share it with your team.
5. Customize the tracker to meet your program’s needs.

You can find a tutorial on many of the basic and intermediate features of Google Sheets on OTAN’s website.

Gathering and Storing Information

Updating the tracker regularly is essential to keeping the data accurate and useful. Determine a schedule for updates, such as weekly or bi-weekly, and assign responsibilities to key staff members. While editing rights should be limited to maintain data integrity, viewing access can be broader to ensure transparency. The tracker’s collaborative features allow team teachers to communicate and coordinate effectively. Storing the file in a shared Google Drive folder ensures easy access for all authorized users. Regular maintenance is also necessary to address conditional formatting issues and update student drops or new enrollments.

Customizing the Tracker for Other Programs

The tracker is flexible and can be adapted to fit the needs of various adult education programs. Here are some examples of how other programs might customize it:

• Attendance Tracking: Add tabs or columns to monitor daily attendance for in-person or online classes.
• Certification Programs: Use color-coded indicators to track progress in obtaining specific certifications or credentials.
• GED/HiSET Prep: Create a tab to track progress toward readiness for testing and subject scores for tests taken.
• Workforce Training: Include fields for job placement, internships, or skill development milestones.
• Language Acquisition: Add sections to monitor ESL levels, class participation, and CASAS ESL scores.
• EL Civics for ESL Students: Add a tab to track student progress in completing EL Civics objectives, including assessments, participation in civic engagement projects, and achievement of life skills milestones.
• Support Services: Track referrals for counseling, childcare, or other student support resources.
• Community Programs: Incorporate volunteer hours, community engagement activities, or workshops attended by students.

These customizations allow the tracker to meet the unique goals of different programs while maintaining its collaborative and efficient design.

Bringing it All Together

The shared tracker is a simple and effective tool for managing student progress. It keeps all data in one place and updates in real time. Teachers and staff can track post-tests, course completion, and potential graduates easily. The shared drive ensures everyone has access, even if team members leave. This tool helps both in-person and online programs stay organized and focused. Most importantly, it helps educators give students the support they need to succeed.