How to Create a League Table in Excel (4 Easy Methods)

Tocreate a league table in Excel, we’ll use the dataset below, containing some team names and their points totals. We used Microsoft Excel 365 here, but you can use any available Excel version.

Dataset to create a league table in excel


Method 1 – Using the RANK Function

Steps:

  • Enter the following formula in cell D5:
=RANK(C5,$C$5:$C$11,0)

Using RANK Funcion to create a league table in excel

Formula Breakdown

  • RANK(C5,$C$5:$C$11,0) → the RANK function returns the rank of a range of numbers.
  • C5 → is the number.
  • $C$5:$C$11→ is the reference.
  • 0 indicates descending order.
  • RANK(C5,$C$5:$C$11,0) → becomes
    • Output: 5
  • Explanation: Chelsea F.C is at rank 5.
  • Press ENTER.

You can see the result in cell D5.

  • Drag down the formula with the Fill Handle tool.

Here is the complete Rank column.

Read More: How to Make a Table Bigger in Excel 


Method 2 – Using the COUNTIF Function

In the following dataset, we have the Team Name, Total Points, No. of Wins, No. of Draws, Points, and Rank columns. First, we will calculate the Points column. Then, using the COUNTIF function, we will determine the Rank column.

Step 1 – Calculating the Points Column

Teams receive 3 points for each win and 1 point for each draw.

  • Enter the following formula in cell F5:
=(D5*3)+E5

This simply multiplies the No. of Wins in cell D5 by 3 then adds the No. of Draws in cell E5.

Calculating Points to create a league table in excel

  • Press ENTER.

You can see the result in cell F5.

  • Drag down the formula with the Fill Handle tool.

As a result, we have the complete Points column.

Step 2 – Ranking the Teams

We will use the COUNTIF function to determine the Rank of the Teams based on their Points totals.

  • Enter the following formula in cell G5:
=COUNTIF($F$5:$F$11,">"&F5)+1

Applying COUNTIF Function to create a league table in excel

Formula Breakdown

  • COUNTIF($F$5:$F$11,”>”&F5)+1 → counts the cells that meet a certain criterion.
  • $F$5:$F$11 → is the range of cells
  • “>”&F5 → is the criterion.
  • +1 → adds 1 to the returned result.
  • COUNTIF($F$5:$F$11,”>”&F5)+1→ becomes
    • Output: 4
  • Explanation: 4 is the rank of team Chelsea F.C.
  • Press ENTER.

You can see the result in cell G5.

  • Drag down the formula with the Fill Handle tool.

The complete Rank column is filled.

Read More: How to Make a Decision Table in Excel 


Method 3 – Using the MATCH and OFFSET Functions to Order Rankings

Here, we have an unordered Rank Table. Let’s use the MATCH and OFFSET functions to arrange the teams by Rank.

Step 1 – Using the MATCH Function to Get the Position Column

  • Enter the following formula in cell C17:
=MATCH(B17,$D$5:$D$11,0)

Applying MATCH Function to create a league table in excel

Formula Breakdown

  • MATCH(B17,$D$5:$D$11,0) → searches for a specified value in a range of cells.
  • B17 → is the lookup_value.
  • $D$5:$D$11 → is the lookup_array.
  • 0 →indicates an exact match.
  • MATCH(B17,$D$5:$D$11,0) → becomes
    • Output: 2
  • Explanation: 2 is the position of Rank 1 in the Team Name and Rank Table.
  • Press ENTER.

You can see the result in cell C17.

  • Drag down the formula with the Fill Handle tool.

The Position column is filled completely.

Step 2 – Using the OFFSET Function to Find Team Names

  • Enter the following formula in cell D17:
=OFFSET($B$4,C17,0)

Use of OFFSET Function to create a league table in excel

Formula Breakdown

  • OFFSET($B$4,C17,0) → returns a part of a dataset with a specific height and width, situated at a specific number of rows down and a specific number of columns right from a given cell reference.
  • $B$4 → is the reference.
  • C17→ is the row.
  • 0 → is the column.
  • OFFSET($B$4,C17,0) → becomes
    • Output: FC-Barcelona
  • Explanation: FC-Barcelona is the Team Name for Rank 1.
  • Press ENTER.

You can see the result in cell C17.

  • Drag down the formula with the Fill Handle tool.

We have a league table with the teams ranked in the correct order with the correct Team Names.


Method 4 – Creating a League Table with Equal Values

In the following dataset, cells C6, and C7 have equal Points, as so cells C8, and C9. Let’s rank our teams despite some of them having equal Points.

We added an Increment column to our dataset.

Steps:

  • Enter the following formula in cell E5:
=C5*D5

This simply multiplies Points by Increment to get the Total.

Calculating Total to create a league table in excel

  • Press ENTER.

You can see the result in cell E5.

  • Drag down the formula with the Fill Handle tool.

The Total column is filled.

Now no teams have equal points, so we can rank them according to their Total.

  • Enter the following formula in cell F5:
=RANK(E5,$E$5:$E$11,0)

Creating League Table with Wqual Values in Excel

Formula Breakdown

  • RANK(E5,$E$5:$E$11,0) → the RANK function returns the rank of a range of numbers.
  • E5 → is the number.
  • $E$5:$E$11→ is the reference.
  • 0 indicates descending order.
  • RANK(E5,$E$5:$E$11,0) → becomes
    • Output: 7
  • Explanation: Chelsea F.C is at rank 7.
  • Press ENTER.

You can see the result in cell D5.

  • Drag down the formula with the Fill Handle tool.

The Rank column is complete.


How to Create League Table with Auto Sorting

The Data Set below is unsorted. Let’s auto-sort it using the SORT function.

The SORT function is only available in Excel 365 or later versions of Excel.

Steps:

  • Enter the following formula in cell B17:
=SORT(B5:C11,2,-1)

Formula Breakdown

  • SORT(B5:C11,2,-1) →The SORT function sorts a range of cells according to a specified sort_index and order.
  • B5:C11 → is the table_array.
  • 2 → is the sort_index.
  • -1 → indicates ascending order.
  • Press ENTER.

The sorted league table is returned.


 

Download Practice Workbook


Related Articles


<< Go Back to Excel Table | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo