Create a spreadsheet leaderboard
If you run a club that involves members competing against each other, you can create a spreadsheet leaderboard that updates automatically.
You first use the RANK function to create a table containing names and scores, then a second table linked to it using the VLOOKUP function. This second table takes the points from the first to update your leaderboard. We’ll use Excel here, but our instructions also work in Libreoffice Calc and Onlyoffice.
Build your source table
First, create a simple table showing the points scored by each player. We chose three for a win, one for a draw, and zero for a defeat. Leave the Rank column blank. This is the ‘source’ data that your second table will use to update the leaderboard.
To show a player’s position, enter a RANK formula in the Rank column of your first table. To do this, we typed =RANK(C3,C$3:C$7,0) into cell A3 ( 1 in our screenshot below). C3 here refers to the cell we want to use to rank. In our table, C3 shows the number of points Ben has accumulated 2.
The range C$3:C$7 refers to the column containing the points from other players. Using $ means that as we copy and paste the formula into other cells in the A column, the C3:C7 range won't change. To do this, we click A3 and press Ctrl+c to copy. Now we highlight cells A4 to A7 and click Ctrl+v to paste the formula into them.
The final number in the formula can either be 0 or 1. The former ranks the data in descending order, so the firstranked entry is the highest number; the latter does the opposite.
Once you’ve pasted the function into cells A4 to A7, press Enter and the Rank column will be updated. In our example, it shows Jenna ranked 1, despite being placed in the middle of column B.
Create a second table that updates
Now click the plus (+) sign at the bottom of your spreadsheet to create a new sheet. This is where you’ll make the table that will be automatically updated, pulling in data from the first sheet. In our example, we only need columns for rank, name and points. We fill in the A column manually with ranks from 1 to 5, with Jenna at the top.
In cell B3 enter the formula =VLOOKU P($A3,SHEET1!$A$2:$G$7,2,FALSE) – see screenshot above. To explain what this does, we’ll start with cell A3, which shows ‘1’ – Jenna’s position in the leaderboard. Next, we reference the cells A2:G7 from our first table in Sheet 1. We then enter 2 because we are looking to pull name data from the second column in that table.
We end the formula with FALSE because we want an exact match. Again, we use $ signs throughout because we are looking to fix the references as we copy and paste our formula into other cells.
Click B3 and tap Ctrl+c to copy, then highlight the remaining cells in the B column and tap Ctrl+v to paste.
You must now enter a formula to make the second sheet pull in the data from the points column in the first sheet. For us, that means clicking in cell C3 and entering =VLOOKUP($A3,SHEET1!$A$2:$ G$7,3,FALSE). The only change to the previous VLOOKUP formula is 3 because the points in the first sheet are in the third column. Again, copy and paste the formula into the rest of the column.
Once that’s set up, whenever you update the points tally in the first table, it will be updated in the second – and the names will move up and down to show their new positions.