Organize class data in Sheets

Google Sheets can be used by teachers and students to easily aggregate, organize, and analyze information in one place. With advanced tools for sorting, formatting, creating and visualizing information with charts, Sheets can be used in a variety of scenarios.

Objectives

After completing this lesson, you will be able to:

  • Use Google Sheets to organize data for and about your class
  • Create and enter data into a Google Sheet
  • Share a Google Sheet
  • Analyze Sheet data by creating a chart

Scenarios

The following scenarios show examples of how educators use Google Sheets in their classrooms.

Store student scores & share with parents

A 10th-grade history educator keeps a manual gradebook of her students’ assignments, submitted homework, and test scores. She frequently has to answer questions from parents and students about whether they turned in assignments, what their score was, and what their cumulative score for the quarter is.

Each query requires her to consult her notebook and recalculate cumulative scores. She decided to use Google Sheets to manage her gradebook instead. Each student has her own row in the spreadsheet (using student ID number for privacy purposes). All students and parents can view the spreadsheet from school, home, or mobile device to check on their progress. This has saved the educator significant administrative time, leaving her more time to talk to students and plan lessons.

Manage student assignment records

A Year-7 literature teacher requires her students to read for at least 30 minutes every day. Her students fill out paper weekly reading logs and turn them in each Friday, which she would then review manually and mark the scores in a grade book.

Now, she uses a Google Sheet to summarize the students’ weekly reading logs. She spends a few minutes every Friday afternoon filling in the assignment data in a Google Sheet. These include questions about how much the students have read each week, what books they’re reading, and student ratings of each of these books. She can scan and summarize the results in a spreadsheet, rapidly identify students who are missing expectations, and collect a list of her students’ favorite books. She can also provide feedback to students based on how much and what they’re reading. (See activity below for a sample of what this Sheet looks like.)

Visualize and interact with data using charts

A math teacher is teaching his students how to create and interpret charts and graphs. He used to have the students collect data manually and create charts of basic information about themselves: birth month, favorite color, and height, for example. Collecting this data used to take an entire class period; creating the graphs used to take one or two additional class periods.

Now students enter data in the same spreadsheet while they’re in the computer lab; they then work in groups to create charts representing this data using Sheets. The entire lesson now takes only one class period, and the class can spend more class time analyzing the charts and data.

How-to

Create and enter data into a Google Sheet

To create a Google Sheet

  1. Navigate to drive.google.com.
  2. Select New and then Google Sheets.
  3. Give your Sheet a title.
  4. Enter data into cells by clicking on a cell and typing.

Share a Google Sheet

Spreadsheets and documents have the same sharing interface. To share a Sheet:

  1. Click Share.
  2. Add a list of email addresses, and select their level of access (view, edit, comment).
  3. If you want to email a link for others to view your spreadsheet, you can copy the URL from the navigation bar.

Analyze spreadsheet data: create a chart

Charts are a convenient way to analyze spreadsheet data visually and come built-in to Google Sheets. There are eight standard types of charts to choose from: line, area, column, bar, scatter, pie, map, and trend. Data displayed in charts is selected from the spreadsheet and updated whenever the spreadsheet is modified. The chart always has the most accurate information automatically.

To create a chart:

  1. Label the data you will include in your chart by typing the name of the data type in a cell above the relevant data.
  2. From the Edit page of your spreadsheet, select the cells of data you would like to include in the chart.
  3. From the Insert menu, select Chart or click the Chart icon on the menu bar. 

  4. In the Data section, make sure the range of data is correct and specify if you want to switch rows/columns.
  5. Under the Charts tab, select the type of chart you would like to create: Line; Area; Column; Bars, Scatter, etc.

  6. Under the Customize tab, enter information for your chart in the following fields: Title; Legend; Background, etc. Your fields will vary based on the type of chart you select.
  7. Specify other features and styles of chart components.
  8. Preview your chart and edit as necessary.
  9. Click Insert. The chart will appear in the spreadsheet.

Classroom connections

You and your students can use the information you have learned in this lesson in the following ways:

  • Build resources for your lessons - When you use Google Sheets, the sheet becomes a dynamic link back to the web. This means that Google Sheets can detect patterns in your spreadsheet by using Google Search and fill in the rest of the pattern directly into your spreadsheet thus saving you lots of time when creating teaching resources that have patterns.
  • Benefit from a pooling of efforts and results - You can ask students to investigate a topic and add their results to the project/activity spreadsheet. (Look at an example.)
  • Use them as data collection sheets with students for science experiments - Google spreadsheets can be used to generate data; share and discuss experimental findings; and look for consistencies, inconsistencies, patterns, and outliers. (Look at an example.)

Learn more


Activity

Part A. Create a Google Sheet

Option 1. Create a new Google Sheet that you could use in your classroom. Add data in at least five cells. Share the Google Sheet with at least one friend or colleague.

Option 2. Practice creating a sheet with this data:


Share the Google Sheet with at least one friend or colleague.

Part B. Analyze data using a chart

Using the data above, create a chart to compare how many minutes each student read during the week of 9/4/2014. Which student read the most during the week of 9/4?

Extra credit

Based on the data above, which book is the most popular (has the highest average rating)?

Part C. How will you use Sheets in your classroom?







Please answer all questions in order to receive credit for this project.








Please answer all questions in order to receive credit for this project.

Discussion