You are level 43

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.

New Tab

In the bottom left of Google Sheets is a plus icon to add a tab. Add a 2nd tab. Double click on the 2nd tab and rename it to “levels.”
new tab 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

Let column A in your spreadsheet be the minimum points (XP) needed for a level. Let column B be the level number.
Levels in a spreadsheet

Quests (Assignments/Tasks)

Since we are using gamification you might want to call your assignments or tasks “quests.” Rename the first tab “quests.”

Quest List

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.

Gamification Columns

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.

Note that the “Check Off” column is not part of the master quest list. This column is only for the student quest list.
Screen Shot 2014-11-04 at 2.24.21 PM

Copy the URL at the top of the spreadsheet.

ImportRange

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”)

Make sure you replace the “paste URL here” with the URL from the 2nd spreadsheet. You also need the quotation marks around the URL.
Import range into column B

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.

Levels

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.

Total Points

In order to determine what level a student is, they need to add up all of their points they’ve earned. In the column next to XP create a column that says “Earned XP.”
earned xp

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
=if(A2=””,0,D2)

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.

Fill Down

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
=sum(E:E)

Vlookup

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
=vlookup(F1,levels!$A$2:$B$40,2)

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.

vlookup formula

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

Samples

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