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:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard