Creating an online leaderboard in Google Sheets is a great way to keep score and motivate any team or group. You can use the sharing function of Google Sheets to "broadcast" your leaderboard to as many people as you want. At the same time you can ensure that only selected individuals can update the information.
Let's look at how to setup a leaderboard and share it using Google Sheets.
To get started, create a new Google Sheet with 3 columns: Rank, Name and Score. See screenshot below.
Note that the rank column is empty and that our scores are not sorted.
Create an auto incrementing field in Google Sheets
The first thing we will do is add a formular to the Rank column to ensure that it always displays a series of numbers
beginning at 1 and then counting up. We'll do this using the row function. Go to the cell A2 and enter the following
formula =row()
You'll see that after pressing enter, cell A2 now contains 2. This is not what we want, because it should say 1 --
but it is correct, because we are in the second row 😉. The solution is quick: we alter the formula to say =row()-1.
We now need to copy the formula to all the rows below. Click on the cell you edited and hover your mouse over the square caret, click and drag downwards. It should now look like this:
Sort the scores
Next we want to sort the leaderboard so that the highest score is at the top. This requires 2 steps
- We "freeze" the top row of the spreadsheet to ensure our titles (Rank, Name and Score) always remain at the top (and are not moved elsewhere due to the sorting)
- We use Google Sheets' inbuilt sorting.
To freeze, click the "view" entry in the menu, then click on "freeze", then click on "1 row" as shown below:
You should now see a thick grey line under the first row of your table. Now for the sorting.
Go to the header of the column that contains your scores (in our case C) and click the small triangle. In the resulting
menu select "Sort sheet A-Z". See below:
That's it! Your new leaderboard is now correctly sorted. If you add new players or change players' scores, you'll need to sort again using the step above. Note that you only need to freeze once!
Share the leaderboard
The final step is sharing the leaderboard. You probably want to share the read-only view of the leaderboard, so here are steps for doing that:
- Click on the green "PUBLISH" button at the top right.
- Click on "change to anyone with the link" to allow anyone to view the leaderboard (see below)
You can now copy the link and share it. Here's the link to the leaderboard used in this article.
You can also give editing rights to certain users.
Upload an Excel file to create an instant online leaderboard
By the way, all of this functionality is also provided by Keepthescore.com but in a much better-looking and easier-to-use package. You can add themes, edit fonts, publish your scoreboard online, edit it on mobile, and much else besides.
NOTE Currently this only works for Microsoft Excel files. A Google Sheets integration for Keepthescore.com is coming soon!
Just click on this button to get started (no signup required!):
You can read more about what you can customize in the customization guide.
