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


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.


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.


<< Go Back to Scoring | Formula List | Learn Excel

What is ExcelDemy?

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

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

4 Comments
  1. How would I make one that changes automatically when the scores are changed?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Mar 7, 2024 at 12:58 PM

      Hello NADHAY SARY

      Thanks for visiting our blog and sharing your requirements. You wanted a Dynamic Leaderboard that updates automatically when the Average Sales will be changed.

      OUTPUT Overview:

      I am delighted to inform you that I have developed an Event Procedure and Sub-procedure using VBA to fulfil your goal.

      Excel VBA Code:

      
      Private Sub Worksheet_Change(ByVal Target As Range)
      
          Dim AffectedRange As Range
      
          If Not Intersect(Target, Me.Columns("D")) Is Nothing Then
      
              Set AffectedRange = Me.Range("D5:D" & Me.Cells(Me.Rows.Count, "D").End(xlUp).Row)
              
              If Not AffectedRange Is Nothing And AffectedRange.Cells.Count > 1 Then
                  Call SortRangeByAverageSales
              End If
          
          End If
      
      End Sub
      
      Sub SortRangeByAverageSales()
      
          Dim ws As Worksheet
          Dim i, lastRow, lastRowRank As Long
          
          Set ws = ThisWorkbook.Sheets("Sheet1")
          
          lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
          
          With ws.Sort
              .SortFields.Clear
              .SortFields.Add Key:=Range("D5:D" & lastRow), _
                              SortOn:=xlSortOnValues, _
                              Order:=xlDescending
              .SetRange Range("C5:D" & lastRow)
              .Apply
          End With
          
          ws.Range("B" & lastRow).ClearContents
          lastRowRank = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
          
          For i = 5 To lastRowRank
              ws.Range("B" & i).Value = i - 4
          Next i
      
      End Sub
      

      Follow the steps: Right-click on the sheet name tab >> View Code >> Paste the given code in the sheet module >> Save >> Return to the sheet and make your desired changes.

      Hopefully, the code will help you in reaching your goal.

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  2. Hello, this question is regarding the code provided by Lutfor Rahman Shimanto. I have more columns I want to also update with the username and point. Is there a way to modify the code so the whole ROW moves?

    This is my sheet

    https://imgur.com/DQ8V1ZZ

    Note that more week columns will be added in the future

    Many thanks!

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Mar 28, 2024 at 12:34 PM

      Hello Ignacio Chavez

      Thanks for thanking me. Though, I was unable to access the link you have given, I understand your requirements. I have modified my previous VBA code in such a way that this time, it will identify the columns dynamically (Assuming column headings are in row 5).

      SOLUTION Overview:

      Excel VBA Code:

      Private Sub Worksheet_Change(ByVal Target As Range)
      
          Dim AffectedRange As Range
      
          If Not Intersect(Target, Me.Columns("D")) Is Nothing Then
      
              Set AffectedRange = Me.Range("D5:D" & Me.Cells(Me.Rows.Count, "D").End(xlUp).Row)
              
              If Not AffectedRange Is Nothing And AffectedRange.Cells.Count > 1 Then
                  Call SortRangeByAverageSales
              End If
          
          End If
      
      End Sub
      
      Sub SortRangeByAverageSales()
      
          Dim ws As Worksheet
          Dim i As Long
          Dim lastRow As Long
          Dim lastColumn As Long
          Dim lastRowRank As Long
      
          Set ws = ThisWorkbook.Sheets("Sheet1")
      
          lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
          lastColumn = ws.Cells(5, ws.Columns.Count).End(xlToLeft).Column
      
          With ws.Sort
              .SortFields.Clear
              .SortFields.Add Key:=ws.Range("D5:D" & lastRow), _
                              SortOn:=xlSortOnValues, _
                              Order:=xlDescending
              .SetRange ws.Range(ws.Cells(5, 3), ws.Cells(lastRow, lastColumn))
              .Apply
          End With
      
          ws.Range("B" & lastRow).ClearContents
          lastRowRank = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
      
          For i = 5 To lastRowRank
              ws.Range("B" & i).Value = i - 4
          Next i
      
      End Sub

      Hopefully, you have found the idea. I have attached the solution workbook; good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo