Setting up a Test Results Spreadsheet
For some time I have been working on developing a means to learn more about the tests I give and give feedback to the students about their tests. I find the most effective way is through using Google Sheets or Excel. I tend to use Google Sheets because my work machine is a PC with the Office package (Word, Excel, PowerPoint, and Outlook) and my home computer is a Mac without the Office package.
This post will show how to begin setting up the spreadsheet file for one type of test. Subsequent posts will show some formulas I use and how to set up the reports page.
For a reading comprehension test that tests skills like words in context, main idea, and supporting information, I set up the main page with the test item number and the key under it like this.
I then add additional pages for each skill with the items from the test that criteria. To add additional sheets or pages, go to the bottom of the spreadsheet and click on the plus sign.
For example if question 1 and 3 are main idea questions, then that page will have links to question 1 and 3 on the new sheet. First, it is a good idea to rename the sheets. I renamed mine as student results and main idea.
Now, we want to link the main idea questions to the answer sheet. We can do this quickly using the formula bar. First, we find the cell we want to use as our landing space. From this space, we enter the = sign in the formula bar and switch sheets to the Student Results sheet and click on the desired space. In the formula bar, a formula should appear that looks something like this =‘Student results’!B2. The information between the apostrophes is the sheet title. The exclamation point tells us that we are linking to a different page. This is followed by the cell reference where the information will be copied from. I have experienced difficulties with this in Google Sheets and sometimes have to manually adjust the cell number to get the proper reference cell.
Next we fill the spaces with the reference cells to be able to copy the selected information when we record the scores as is shown in this video.
We now have the basic elements for setting up the spreadsheet. To complete it, we create new sheets for the the different skills and link them to the student results page. We complete the fills. We are now ready to record the student answers.