Becoming a Master of Data Overlords

With all of the assessment data that we as teachers collect over the course of the school year, keeping track of everything can seem very daunting. If you are collecting data in your GAFE account, here is an easy way to make a report that will isolate one student at a time. You can set this up for a grade book, monitoring progress during a unit, or any other reason you would collect data from your students.

Almost every time you would export data about your student, it shows up in a spreadsheet the same way: student names going down the left side in rows and different data sets going across the columns. 

Even if you lock down the first row with the student's name, you still have a lot of data on the spreadsheet. It can seem overwhelming. Follow along to create a isolated, vertical report that is easy to view. 

 Watch the video below or follow the step by step guide below to create this report

Video Walkthrough:

Walkthrough

• Step 1: Open up a sheet with all of your student data. 
• Step 2: Create a new sheet by hitting the plus button on the bottom left of the spreadsheet window. 
• Step 3: Always rename your new sheets to keep track of things: I've renamed mine Student Reports

• Step 4: If you want, delete all of the extra rows and columns that you won't need (I only needed 3 columns for my reports.

Adding a Drop-down menu in Google Sheets:

• Right click on the cell in which you'd like the dropdown menu (I chose C1)
• Select Data Validation
• Where it says Criteria, select the range from your original sheet that contains student names
• Click save

Transpose Function:

• The transpose function in Sheets allows you to take the horizontal data, and flip it to vertical. 
• The transpose function should look like this: =TRANSPOSE(array_or_range) 
• If you are using a separate sheet, remember to add the sheet name (exactly as it typed) and an ! to denote the sheet. Sample: =TRANSPOSE(Sheet1!A1:AJ2)
• Put in your Transpose function in cell A2 - Now you see my assessment titles listed vertically below.

Filter Function:

• Now that we have the assessment names and headers inputted as a vertical report, now we need to populate the data from individual students. We will have to use the filter function in conjunction with the transpose function. 
• The filter function allows us to select all of the data we want to filter from, then we create a condition that will show us anything that relates to the criteria we selected. 
• We need to not only create a filter, but we also need to transpose it so that it follows the format of the previously created vertical layout. 
• We are going to put this in cell C2 (Sheet names match the sample in the images above)

Now when you select a name in the dropdown menu, the data sets change based on whatever name shows up in C1. 

To make it even better, add some conditional formatting to the cells to give you a quick glance look at how students are progressing.