Home Google Tips and Tricks How to Create a Digital Badge Leaderboard

How to Create a Digital Badge Leaderboard

by Emily Hopkins
Voiced by Amazon Polly

TCEA Area 15 Director, Sherry Hall, has an excellent idea for creating a digital badge leaderboard to enhance student engagement. You can create your own spreadsheet and badges or edit this template that Sherry has already created! This template is ready to go with badges, tabs, and formulas– it just needs your customized class list, skills, and badges. If you want to get creative and make your own, let’s walk through how Sherry does it. Watch the video and/or check out the steps outlined below!

How to Create a Badge Leaderboard Worksheet Using Google Sheets by Sherry Hall

Create Your Tabs

Type sheets.new in your Chrome search bar to open up a new Google Worksheet, and follow the steps below to get started.

Tab 1: Open Google Sheets and create a “Badge Leaderboard” tab. In Sherry’s example, she has her class list in the first column, a title and image in the first row, and the tools students are working on mastering as titles in row two (Drive, Docs, Slides, etc.). Here, we can see she is working on skills in Google Workspace.

badge leaderboard spreadsheet example

Tab 2: Make a copy of the leaderboard tab by right-clicking on the tab and selecting “Duplicate.” Name this tab “Teacher,” because it’s just for you. Place checkboxes in each cell under the skills for each student name by highlighting the cells, clicking “Insert” in the menu bar, and then clicking “Checkbox” in the drop-down menu. Checkboxes will appear in all the cells you’ve selected.

badge leaderboard teacher tab

Tab 3: Make a copy of the “Teacher” tab and name this tab “Badges.” Remove the class list column and the row one image, so only the tools appear. Insert the badges you’d like each student to receive for mastering each tool. To do this, select the cell, click “Insert” in the menu bar, click “Image,” and select the “Image in cell” option. We can see here that Sherry has inserted coordinating badges for each tool.

virtual badges

Set Up Data Validation

  1. Go back to your “Teacher” tab, and click on “Data” in the menu bar.
  2. Then select “Data validation” in the drop-down menu.
  3. A pop-up box will appear, and you will need to make sure the criteria says “Checkbox,” the “Use custom cell values” box is checked, and set checked boxes to be “TRUE” and unchecked boxes to be “FALSE.”
  4. Once these are set, click “Save.”
data validation Google Sheets

Protect Teacher and Badges Tabs from Student Editing

  1. Under “Data” in the menu bar, click “Protected sheets and ranges,” and a menu will appear on the right side of the screen.
  2. Select the “Sheet” tab, and click “Set permissions.”
  3. Click “Restrict who can edit this range, and select “Only you” from the drop-down menu.
  4. Click “Done.”
  5. Click on your “Badges” tab and do the same!
Google Sheets Tab Permissions

Insert the IF Formula

I promise this isn’t as hard as it seems! You will only need to do one row of formulas, then drag the row of formulas down to fill the page, and Google Sheets will magically take care of the rest.

  1. On the “Badges Leaderboard” tab, you will need to insert an IF formula in the first student’s row for each skill. Note: You can copy and paste the one I provided below and edit it as needed.

2. To do this, you will structure your formula like this:

IF Formula
Copy this formula to edit and use in your Google Sheet—> =IF(Teacher!B3=TRUE, Badges!$A$2, )

3. Once you’ve inserted the formulas in each row one cell for the first student, select all the cells in row one for that student, click the blue box in the bottom right corner of the last cell, and drag down over all the cells, like this:

Watch to learn how to quickly fill cells with a formula in Google Sheets!

4. As students master each skill, check the boxes in the “Teacher” tab, and the “Badge Leaderboard” will auto-populate with badges! Pretty neat.

Badges Leaderboard
Example of Badges in the Badge Leaderboard Tab

Sherry offers several free resources:

You can also download the entire folder of resources here. Thanks to Sherry for this fantastic idea! I can see this being used in a variety of ways at any grade level. How will you use a digital badge leaderboard? Leave us a comment to let us know.

You may also like

Leave a Comment

You've Made It This Far

Like what you're reading? Sign up to stay connected with us.

You have Successfully Subscribed!