As a classroom teacher in an elementary school setting, we are responsible for knowing a lot about our students. Because we teach all subjects, we need to know how that student is doing in math, reading, writing, science, and social studies. We use many, many assessments to track this. Some assessments are done in the moment through exit slips or observations or they could also be just a daily check in. We might or might not record these observations. However, they add to the knowledge a teacher has about a student in a particular area or academically, overall.
We also have summative assessments. These are usually the ones that carry more weight and are more formal. Some of these might consist of a final unit writing piece, a social studies culminating project, a unit math assessment or a running record to check for reading level accuracy, fluency, and comprehension. In addition, we also have outside assessments that we track. Schools might be tracking running records through Fountas & Pinnell or Columbia Teachers College Reading Workshop. Your school might be doing MAP testing, Aims Web or some other state or government mandated testing. When you are dealing with on average, from 20 to 30 students in a class, remembering all of this these fine details can be quite daunting. Having a good system to record track and keep this type of information is important when you need to speak with parents or report on student growth and academic concerns.
In order to tackle the situation, in the elementary school where I work, we started by using some spreadsheets. We decided that we would just focus on collecting formative assessment information. All of our outside assessments which we use MAP and Aims Web, the data was collected externally through other sources. We did not have a way of combining the data together in order to see a holistic view of the child academically. We need to find a way to do this. We decided to eventually combine internal and external assessments into one master spreadsheet.
Setting up data can be daunting, but if you can have a plan from the beginning, it can be manageable. The key is making sure your data collection tool or spreadsheet is set up correctly to automatically gather data through the use of formulas and calculated fields. We started off with each teacher having their own spreadsheet. We then had a master spreadsheet that we used to collect all of the individual class information. When setting up spreadsheets, it is important to leverage the power of formulas in order to keep information consistent, automatic and minimize human error. We took advantage of the ‘query’ and ‘vlookup’ functions to make sure data from our Roster tab filtered directly to the other tabs in our spreadsheet. That way, when the roster was updated, information in all the other tabs would update automatically. These are two simple formulas that seem obvious to people who know about them, but I have encountered so many spreadsheets where these formulas were not used, leading to data inconsistencies.
Color coding the spreadsheet was also helpful for teachers to know where to input data. In this example, columns in blue are coming from the Roster tab. Columns in orange are where teachers directly enter data. Columns in gray are calculated fields that convert letters and words, otherwise known as strings, to numbers. Setting this up in the spreadsheet is helpful for calculating qualitative data. The purple columns are imported external data sets.
Having spreadsheets is a great way to collect all data in one place. However, for most teachers, looking at rows and columns of numbers was not the most efficient way to find out about their students. This is where we decided to find a way to make it more visual. As with any visualization project I work on in school, I talk with teachers and administrators to find out what exactly we want to know. Having data is one thing, being able to understand and use it efficiently is a completely different topic. For this project, I started sketching out thoughts and ideas generated from other teachers. I then shared my sketches with teachers and received feedback before working on the digital version. Once I had a good idea of what to create, I started drafting the digital version using Google Data Studio. I decided on GDS because of the ease of accessibility and the ability to share with others in our secure school domain.
From there we imported our information to GDS and decided to create a profile by answering some of the important questions. Some of these questions we want to answer where things such as where is the student struggling? Where is the student succeeding? How much growth has a student made? How much growth has a student made compared to the rest of the class? How many reading levels has a student ground? Is the data consistent with of our external and internal assessments? These questions help us frame the types of information that we wanted to share in our student profile.
For math units, we measure growth with percentage. We can also compare to the overall grade level averages. For writing, we use a rubric. We expect students to grow one grade level or 1.0 on the scale we use. Therefore, when measuring student growth for writing, we do not use percentages but rather points for growth. Similarly, we can measure growth by comparing pre and post assessments for a unit. We also made sure to have the average scores and average growth for the grade level to provide some baseline data.
For reading assessments, we use the Columbia Teacher’s College Reading Workshop reading levels. Depending on the grade level the expected growth is different. We use the recommended levels of growth chart to represent whether a student is considered to be Concern, Progressing, or Secure. If a student shows more than one grade level above the end of year level, they are considered Above. Finally, we add in the information for external assessments.
The reason I prefer to use dashboards for these types of exploratory data is to allow teachers the ability to choose what they want to see when they want to see it. In this case of the student profile, a teacher can choose any student’s data to look at at anytime. Whenever new assessment information is added to the spreadsheet, the dashboard will update automatically. Through the use of filters, students can be selected. In addition, if working with multiple classes, teachers can be selected to narrow down the choice of students. Other filters could be set up, for example, if you wanted to see information for only the first semester or first quarter of the school year.
If you are curious about how all this works, download the student profile data set and the student profile template. It will look a bit messy at first, so feel free to watch a demo video I did for the #GEGUK Google Innovator group. If you would like help setting up a specific project for your school or organization, please feel free to contact me.