In this tutorial, I am going to show you step-by-step procedures on how to create a leaderboard in Excel. You can use these steps for any type of dataset to rank a list of populations. Throughout this tutorial, you will also learn some important Excel tools and techniques that will be very useful in any Excel-related task.
How to Create a Leaderboard in Excel: with Easy Steps
Step-1: Making Base Excel Dataset to Create Leaderboard
In this first step, we will create the base dataset which we will then update to create a leaderboard in Excel. Follow the steps below to do this.
- First, create a simple data table as in the image below and type in the names of the sales persons.
- Next, enter the values for the average sale unit for each salesperson.
Read More: How to Create Scorecard in Excel
Step-2: Inserting ROW Function
After we have created the starting data table in Excel, now we have to generate the rank values to create the salesperson leaderboard using the ROW function. Below are the steps to achieve this.
- To begin with, go to cell B5 and insert the following formula:
=ROW()
- Then, press Enter to confirm the formula and this will give you the row number in cell B5.
Step-3: Modifying ROW Formula
As we can see from the previous step, we have a value of 5 as the starting rank which is not what we want. Rather we are looking to start the ranking with 1. Let us see how we can modify the previous formula.
- To start this step, navigate to cell B5 and type in the formula below:
=ROW()-4
- Again, press the Enter key and copy this formula using the Fill Handle.
- Consequently, you should get the ranking numbers in ascending order.
Read More: How to Create a Scoring System in Excel
Step-4: Sorting Performance Values in Excel to Create Leaderboard
Now, in this last step, we will sort out the average sales values to create the leaderboard in Excel.
- For this, select the whole data table and go to the Data
- Now, click on Sort under Sort & Filter.
- Next, in the Sort window, check the My data has headers box as in the image below.
- After that, select Average Sales from the Sort by drop-down option.
- Then, select Largest to Smallest under the Order drop-down option.
- Now, click on OK.
- Finally, this should rank the sales persons according to their average sales values.
Things to Remember
- The ROW function yields the row number of the cell containing the formula when no reference is given.
- We can specify a cell or a range of cells as the argument of the ROW function.
- The outcome is an array of size {4,5,6} that spills vertically into three cells, starting with the cell that contains the formula in Excel 365, which supports dynamic array formulae.
- Similarly, to get column numbers, you can use the COLUMN function.
- To count the number of rows, use the ROWS function.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
I hope that you were able to apply the methods that I showed in this tutorial on how to create a leaderboard in Excel. As you can see, it involves quite a few steps to achieve this. So carefully follow these while applying them to your own dataset. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please let me know in the comments.
