Merge all your Students' Data into a single Excel File

School has begun again as hot summer days gave way to classrooms full of students. With budgets tighter than they have been in a while, most teachers are facing classrooms with more students in them; at the same time state and national standards for achievement are continuing to rise. Never before have teachers been expected to do so much for so many.

To that end, the teachers in my school create data binders at the beginning of the year to get to know their students and work to support their learning.

Unfortunately, not all the data needed to get to know the students can be found in one nice, neat, concise report. While some teachers like combining data from multiple sources by hand (often because they are uncomfortable with using Excel), I find the sortability function of a spreadsheet to be worth any time it takes to make the report on the computer. Luckily, I turn to my trusty friend, the Merge Table Wizard, to do the heavy-lifting for my data binders.
Merge Tables Wizard helps quickly combine data from two tables

One piece of information teachers need to know about their students is their demographic information. The federal accountability ratings (from the No Child Left Behind Act) are based on student performance of all students and various subgroups on a state test of math and reading. The groups that are rated nationally are:

  • All students
  • Major Ethnic and Racial Subgroups (defined state-by-state)
  • Economically Disadvantaged (based on free and reduced lunch status)
  • Limited English Proficient Students (LEP)
  • Special Education Students

I start with the Enrollment + Ethnicity report because it includes all the students in my class:
First I merge the Enrollment and Ethnicity report

Next, I will add in the LEP status from my LEP spreadsheet.

Notice that in the first spreadsheet, there is one column for StudentName (meaning first and last name are combined in one cell) while the other spreadsheet has last and first name separated. Because of the way different spreadsheets format student names, I use the Student ID to match with the Local ID).

This second spreadsheet contains only the subset of LEP students--this would not make a good master table because it contains only those students who fit this criteria.
Pull data from smaller tables and use values that are convenient to match

Then comes the Special Education Status from that report.
Add information from different reports like Special Education Status

Finally, we add in the Economically Disadvantaged status:
You will see the status only for the corresponding students

Now, what was once 4 reports is magically transformed into one report:
You get all data merged into one spreadsheet

There are many other pieces of information that might be included in a teacher's data binder:

  • Previous year test scores (overall or broken down by objective)
  • Gifted/Talented status and area of giftedness
  • 504 Modifications
  • Language of state test
  • # of absences
  • # of referrals

By utilizing Merge Tables Wizard and data from different reports, teachers can begin to get to know their students and what support they need to put into place to help each student be successful in class.

As the year progresses, teachers will add to their binders data from district benchmark tests, short-cycle assessments, and universal screeners. They then analyze that data to see which students need support on particular standards and which concepts need to be retaught to the entire class.

Because state requirements vary (my state of Texas' system also includes a science and social studies tests), it is important that teachers know what tests, what grades, and what subgroups their school's ratings are based on.

You may also be interested in

Ukraine flag War in Ukraine. Here's what Ablebits is doing to make sure our team and projects are safe.