Using Microsoft Excel to create a leaderboard is not hard at all if you follow these step-by-step instructions. Leaderboards are a great way of motivating any team, be it a classroom, gym buddies or a sales department. What’s really nice is that you can use Microsoft’s OneDrive integration to save your leaderboard in the cloud and give others access. At the same time you can limit who can edit the leaderboard.
As a bonus we’ve included a good-looking template for you to download at the end of this post.
Let’s look at how to create a leaderboard and share it using Excel.
To get started, create a new file with 3 columns: Rank, Name and Score. See screenshot below. We’ve also added some dummy data to work with.
Note that the rank column is empty and that our scores are not sorted.
Create an auto incrementing field in Excel
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
You’ll see that after pressing enter, cell
A2 now contains
2. This is not what we want, because it should say
but it is correct, because we are in the second row 😉. The solution is quick: we alter the formula to say
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
- Select the data
- Sort it
We select the data, by clicking in the top left cell and then dragging the mouse to the bottom right cell. Our spreadsheet now looks like this:
Now for the sorting.
In the ribbon of the Excel interface, click the “Sort” icon. You should see the following dialog pop up.
Enter the values you see in the screenshot and click on ok.
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.
Share the leaderboard by uploading it to the cloud (optional)
You can upload your Excel file to share it with other people. To do this you need a OneDrive account.
Click on the green “Share” button at the top right like on the screenshot below, then click on “Upload”.
After this has completed, click the share button again. You now have the option to share a “view only” link.
That’s it! Your new leaderboard is now in the cloud and the link is in your clipboard. Share the link via email or however suits you best.
Downloadable leaderboard template
We promised you a template to download and here it is. Simply click this link to download it.
One more thing
By the way, all of this functionality is also provided by Keepthescore.co but in a much better looking and easier to use package.
Please let us know if you have feedback in the comments below. Bye 👋.