Techno Teacher

Tutorial: How to calculate grade averages and compare classes using Excel

*also see our sample item for a final worksheet showing this spreadsheet

Open Excel under Office Tools.  Using this program, you will be able to create a spreadsheet comparing the scores of your class to those at the same grade level in your school.  This data can then be presented to the faculty and used to modify instruction accordingly.

You will have a blank spreadsheet that you can insert scores on.  This is where you will insert the names of each teacher in a grade level, names of their students, the assignments each class has completed, and individual scores on these tasks.
Insert the title of each test or assignment in an individual cell (one the rectangular squares) on the top row.   Coming down the left side of the spreadsheet, you will insert the name of each teacher.
Type in student names for each teacher in the space where teacher name and assignment meet.  Do not worry about using multiple cells.  Then highlight the name of the teacher, right click, format cells, alignment, click on box that says merge cells.
Now focus on the top row that stretches across the page; the one on which you’ve typed the different assignments you’re looking at.   To the right of the student’s names, use another cell for scores.  Merge the two cells above these descriptions (where you’ve typed the title of the assignment)

As you continue setting up the spreadsheet, you will repeat the process you did for each teacher, basically creating a spot for each student and their score on each assignment.   On a Mac, you will copy by pressing “Command C.”  Do this after you’ve highlighted the area you wish to copy.  You will do this for each Name/Score Section for each assignment.

After you’ve created spaces to post each individual score, go in and insert these manually in the appropriate cells.  For Example, Bo Peep’s 13 in the screen shot above.

If you’re using this spreadsheet to compare the performance of different second graders, you want to use a color-coded system to note whether students are at the Benchmark, in a warning area, or in a zone far from the benchmark.  This will be different for each grade level in each school, but in our example we showed 69 & Below as the red zone, 70-79 as the middle zone, and 80-100 as at or above benchmark.

After you’ve entered the student data, you can highlight a cell and fill it with the corresponding color for these benchmarks.  This will be very beneficial when presenting your data or analyzing it.

*Here’s a tip to save time with this step: press Command, left click, then highlight all the scores within a category.  This way you can go up to the paint bucket, pick your color, and they will all highlight simultaneously.

You can use this color coded system to compare the same students multiple times, or different students at one time.  As you can see, there are infinite possibilities for using this function.

Now, for an average function.  You’ve entered all your data, color coded it, and now you want to use Excel to compare scores.  Come to the bottom of your spreadsheet (underneath where you’ve entered data) and type Average.  Then you can have scores all across the bottom in this row.

In the next box where you want the Average for that column to be, you will click on Auto Sum button which will give you a drop-down menu.  Click on “Average” and take your mouse to highlight the scores you want, using the Command button.  This will put the formula for this calculation into that cell.  Click enter, and you have your average for the data you selected.

You can now use these averages to compare students across classes within a grade level.  As you can see, Excel is a very useful program for analyzing and displaying student scores.  Use the spreadsheets for your own use, data when writing grants, and sharing at faculty and district meetings.