Gradebook...in...Space!

When I'm out and about with educators, I always like to ask what they need their data to do. Shiny software can be great, but all too often, I hear from teachers and administrators that they're stuck with pre-packaged analysis and don't have the flexibility to do what they want. Using Excel might not fill in all of the gaps, but I like to think about the ways it can answer the questions educators have.

After prompting a discussion about what they'd like to see, a teacher talked about how she'd really like to overlay her gradebook onto the student seating chart. I thought that was a great idea. We often look at classroom data through the lens of time, but we don't often see it in space.

This exchange happened a couple of years ago, and I finally sat down to develop the idea. In this post, I'll share the most basic version; but there are plenty of additional options to add on. I started by modifying the Beginner's Gradebook to include 30 students, then drew a very traditional seating chart on another worksheet. I filled in students names below each "desk." Keep in mind that you could make any arrangement that you want with the spreadsheet: a different number of desks, four students at a table, horseshoe arrangement, the remaining room set up (with the one kid you always need to have next to your desk). Lots of possibilities.

Next, I highlighted the range of assessments on the Grades sheet. I named the range "Assessments." I'm creative like that.

On the sheet with the seating chart, I created a data validation list using the Assessments range. This way, I could display different data on the seating chart. (Need a refresher on creating the data validation? Watch here.)

Then, I added an INDEX/MATCH equation in the "desk" cell for every student. Here is the one for Cathy Andrews: =INDEX(Grades!C8:AJ8,MATCH(T3,Assessments,0))  The formula tells Excel to look on the spreadsheet with the Grades, in the range of assessments (columns C - AJ) for Cathy Andrews (row 8), and match the score for the assessment on the Grades sheet with the one selected in the dropdown list (T3).

I would like to find a different way to do that part. Having to individually list which kid matches each row of data is not particularly friendly. And, when you move kids around in the room, you'll need to remember to move their equations with them. Since you can't have the formula MATCH on two variables (name of student, title of assessment), I've done it the cleanest way I can think of...but I'll keep hunting. If you have a solution, I'd love to hear it and hope you'll share in the comments of this post.

Finally, I added some conditional formatting to the "desk" cells.  The end result is a heatmap'ish thing like this:

It's kind of fun to play with. I did manipulate a few things in the data as discussion points. Does Dorothy Gale's performance second quarter catch your eye? Would you call home to find out? Are there some areas of the room outperforming others? Do you think something would change if you spent more time there or should you regroup students?

You can download the sample gradebook here. I'd love to hear your ideas about how to use this type of tool. I'm thinking about ways to incorporate attendance data...or graphs. What would you want to see from a birdseye view of your classroom?

Bonus Round
Are you attending the ISTE conference in June? Come hang out in my workshop. We'll play with Excel and other tools...and I'd love to hear more of your ideas about what would be useful data analysis for you.

Excel Gradebook for Intermediate Users: Part I, Getting Organized

Welcome back to the Roll Your Own Gradebook (RYOG) series. This post builds on the lessons from the beginner's series (see Lesson One; Lesson Two, Part I; and Lesson Two, Part II). In those posts, we used a single worksheet with student scores and another as a reporting tool. Now it's time to step it up a bit. We're going to use two different classes of data and one reporting tool. First, we'll set up a page just to organize many of the formulas and lists that will drive the reporting too. Then, we'll learn how to set up two data validation lists so we can sort by class and student name.

There is a "how to" screencast at the bottom of this post. You can also download the workbook for these sessions to use at home. Ready to earn your yellow belt in Excel?

When you open the workbook, you'll notice that there are three tabs for the worksheets: P1 Biology (which is the same data from the RYOG Beginner series), P2 Chemistry, and Report (which is nearly identical to the version in RYOG Beginner). We're also going to create a new one. So, click on the little icon next to the "Report" tab. Name this new tab Formulas. While you certainly can place the lists and formulas we will use on existing worksheets, you will have a cleaner and more manageable product if you place the "engine" that drives the dashboard in its own space.

While you're hanging out on the Formulas page, let's add some information to draw from later. Using cells A1, A2, and A3, create a range for the classes. (See example on the left.) While it might seem a little silly with just two classes for now, you can imagine what this might look like if you had multiple class periods to track or multiple subjects at elementary. If you're an administrator, this list might represent classrooms in your school or schools in your district. We're just going to ease into things with two for now. Then, create a named range for this information. If you've forgotten how to do this, highlight cells A2 and A3, then on the Formulas tab on the ribbon, click "Define Name." Choose a name like "Classes" and hit Enter. You're good to go. You can also revisit Part I of the Beginner's series for a refresher. Now, using the last names of the students on the P1 Biology and P2 Chemistry worksheets, create two more named ranges. I used P1Biology and P2Chemistry as the names. We're also going to insert two lists: one with the names of the standards for biology and one for chemistry.

Now, click on the Report tab. Let's get the data validation lists going. Highlight cells C4 through F4 and then the "Merge and Center" button on the ribbon.

This will create a single cell in that space. This is where we will put our first data validation (i.e. "dropdown") list to select a class. Remember how to do that? On the Data tab, select "Data Validation" and then in the Settings, choose to allow a List. For our source, type Classes. Hit enter and your list should be set up. Now, let's do something similar for the data validation for the Last Name. The difference will be what you use as the Source:

We're going to use a formula here instead of a range like we did above. Why? And what the heck do "INDIRECT" and "SUBSTITUTE" mean? Well, first of all, we need more than one list available for this cell. We need it to display one list if we're wanting to look at Biology data and an entirely different list if it's for Chemistry---and we just want to use one cell. The "INDIRECT" function tells Excel that the source used there depends on our cell with the first data validation. It will then match things up for us. The "SUBSTITUTE" piece is necessary because we have a space in the class names. Excel doesn't do well with that. So, by telling it to substitute a space (that's the part with the " ") with no space (the part with ""), we've eliminated the source of a possible error. If you do get an error message (e.g. "currently evaluates an error), don't freak out. All Excel is saying is that there's nothing selected in the first data validation list, therefore, it doesn't know what to do with the second one. Now your workbook is organized and ready to use.

Watch the tutorial below. Come back for the next post to find out how to use the IF function in order to fill in the information for the Report. In the final tutorial for this gradebook, we'll make use of our Formulas worksheet to create the dynamic data for our graphs on the Report.

Excel Gradebook for Beginners, Lesson Two: Part I, INDEX and MATCH

Once you have your data all in their places with bright shiny faces in your spreadsheet, you're going to want to have a clean way to extract it. This is where a Dashboard is handy. A Dashboard is a type of reporting tool which pulls together different kinds of data.

In our model, we'll have space for a student's name, a rundown of current scores, an overview of total performance and a space to show progress/growth. There are other things you might want to report---such as attendance or qualitative information. Do what you need to do.

In order to get individual pieces of data from the sheet with the scores to the dashboard, you are going to need two things:

1. A data validation list in a designated cell. I pick the cell beside "Last Name" for this. In creating this list, you will have a dropdown menu to select any student and the cell will become the "key" that will be used to extract the right data for the student and plug it into the empty spaces in the dashboard. 
2. A formula that uses both "INDEX" and "MATCH" functions. The INDEX function will tell Excel which column/table of data to draw from and MATCH will tell it which name the data goes with. Your formula will look like this: =INDEX(Column with Data for a Cell,MATCH(Cell on Dashboard with Last Name,Column with Last Names,0))    Why is there a zero at the end? It's part of the MATCH formula---it tells Excel that the match must be exact...no room for error. 

Want to see it in action? Watch the tutorial below.




Next time, we'll do the final piece: the sparkline graphs for the dashboard.