If you have thought about adding an element of gamification to your classroom, having students level up might be fun to do. How do you do this….. The answer is always a spreadsheet 🙂
Create a Spreadsheet
In Google Drive (http://drive.google.com) create a new Google Sheets.
Set your Levels
Remember when playing a game it is easy to get from level 1 to level 2. When students complete their first task they should be able to level up. Suggestion to not make the number of points for level 2 more than the first task will be worth.
When I play World of Warcraft and I start a new toon I can get to level 14 in one day. Since I am working on my doctorate I do not have as much time to play but I have been stuck at level 85 for a year. What I have learned from this is that you gradually increase the difficulty. You want students to feel early success since success breeds success. You do not want it to be too easy to level either. Consider how many points your assignments or activities are typically worth, for the first few levels the gap between the levels is just above that assignment point value.
Points and then Level
Since we are using gamification you might want to call your assignments or tasks “quests.” Rename the first tab “quests.”
If you know all of the quests in advance you can list them on the first tab. Consider having a column to check off the quest. A column to name the quest, use a fun name! A column to provide a description of the quest and a column for the amount of XP points that it is worth.
If you are going to create the list as you go, you will need a 2nd spreadsheet.
Master Quest List
Go to Google Drive and create a new Google Sheets. On the first tab in this spreadsheet create the column headers that describe your quests. List your quests, quest descriptions and XP on this spreadsheet. You can add more to it later.
Copy the URL at the top of the spreadsheet.
Go back to the original spreadsheet.
The first column is the student check off column.
To import the quests into column B, in cell B1 type the formula
=importrange(“paste URL here”,”Sheet1!A:C”)
Using =importrange will allow the students level up sheet to reflect the information on your master quest list. Any changes you make to the master quest list will be reflected in the students level up sheets.
You will want to somehow display to students what level they are. You may want to do this on a different tab or have it as a value on the list of quests. It just depends on where you write your formulas.
You want the “Earned XP” column to provide points to the student if they check off the quest. In the cell next to the quest and under the “Earned XP” column write the formula
What this says is that if the check off cell is blank (empty quotation marks), then the earned XP has a value of zero. Otherwise, credit the student with the amount of XP this quest is worth.
If you click on cell E2, where the formula is, you will notice a small box in the bottom right hand corner. Click and drag that corner all the way down the sheet. This copies the formula to all of the quests you have, and will have.
Total Points Location
Somewhere in the spreadsheet you need to add up all of the XP points the student has earned. You can do this in any cell. If you want it on the same sheet as the list of quests you can use the formula
You will want to compare the number of points the students has earned with the levels chart. I recommend cell G1 so that it is next to the amount of points earned. In the cell that you want to display the students level type the formula
This says to look up the value of the total points in the table with range A2 to B40 (Your number of levels may be more or less than mine, adjust the range accordingly), and the students level is in the 2nd column of your table.
If you want to get fancy your formula can be written as
=”You are level “&vlookup(F1,levels!$A$2:$B$40,2)
Distribute to Students
For the student level up sheet, each student will want their own copy. This will allow them to keep track of their quest completion themselves. You can share the link with students as anyone can view, thus requiring the students to use the File menu to make a copy. Alternatively, you can post the student level up sheet to Google Classroom
CLICK HERE to link to master quest list
CLICK HERE to link to sample student level up sheet
CLICK HERE for a more complex example
CLICK HERE for the personalized quest list I use with my university students