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

The procedures to create a leaderboard in Excel in this tutorial can also be used to create any type of dataset to rank a list of data.

### Step 1 – Making a Base Excel Dataset

Lets create the base dataset which we will then update to create a leaderboard in Excel.

STEPS:

• Create a simple data table as in the image below and type in the names of the sales persons.

• Enter some values for the Average Sales for each sales person.

### Step 2 – Inserting ROW Function

Now let’s generate the rank values to create the salesperson leaderboard using the ROW function.

STEPS:

• Go to cell B5 and enter the following formula:
`=ROW()`

• Press Enter to confirm the formula, which will return the row number in cell B5.

### Step 3 – Modifying the ROW Formula

We now a value of 5 as the starting rank, which is not what we want, because we want to start our ranking with 1. Let’s modify the formula to accomplish this.

STEPS:

• Navigate to cell B5 and enter the formula below:
`=ROW()-4`

• Press Enter.
• Copy the formula to the other cells using the Fill Handle.

You should now have the ranking numbers in ascending order.

### Step 4 – Sorting Performance Values

Now let’s sort the average sales values to create the leaderboard.

STEPS:

• Select the whole data table.
• Go to the Data tab.
• Click on Sort under Sort & Filter.

In the Sort window that opens:

• Check the My data has headers box.
• Select Average Sales from the Sort by drop-down option.
• Select Largest to Smallest under the Order drop-down option.
• Click OK.

The sales persons should now be ranked 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.
• In Excel 365, which supports dynamic array formulae, the outcome is an array of size {4,5,6} that spills vertically into three cells, starting with the cell that contains the formula.
• To get column numbers, you can similarly use the COLUMN function.
• To count the number of rows, use the ROWS function.

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

Get FREE Advanced Excel Exercises with Solutions!
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

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

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

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

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!

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