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. 

Make Goobric Count for You

I have been using Doctopus and Goobric (click link to see the walkthrough) extensively this semester already. One of the things that I was struggling with was that Goobric doesn't automatically add up the scores from a rubric so that I can easily enter the grade into a gradebook.

I was trying forever to figure this out, but then I realized how silly I was being. You can simply set up a SUM formula for the columns of the rubric so that it will automatically add up all of the elements of the graded rubric.

Create the formula:

• Go to the column that says grade (create when you run Doctopus)
• Goobric adds columns based on the categories of a rubric you assign to the document
• In the grade column, enter the formula (or change based on the needs of your rubric):     
•  =SUM (l2:r2) - I typed in each cell, just so you can see the range that this formula will do
• Since my rubric is scored on columns l, m, n, o, p, q, r, I need to add those columns together **or however many columns you have for your rubric**
• My rubric has 6 categories, so there are 6 cells that I am adding together
• If your Goobric has 4 categories, your formula will look like: =SUM (l2:o2)

Even more magic:

Now, instead of entering this formula for every row (changing l2 to l3 etc.), there is a great trick to replicate that formula for every row. 

In the lower right hand corner of the cell with the grade, you have a blue box. Click the blue box and drag it down the column till the last student on your roster, and it will replicate that formula for every row. You'll see in the illustration that each row that has grades from the rubric will be added up in the grade column (J).  

This will save you time trying to add up each element of the rubric. And if you set up the formula before you grade it, your grade (sum) will show up even if you aren't in the spreadsheet.