How to Create a Leaderboard in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

how to create a leaderboard in excel

  • Next, enter the values for the average sale unit for each salesperson.

entering values to create a leaderboard in Excel

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()

entering row formula to create a leaderboard in Excel

  • 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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo