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

If you want to create a league table in Excel, you have come to the right place. Here, we will walk you through 4 easy and effective methods to do the task smoothly.


Download Practice Workbook

You can download the Excel file and practice while you are reading this article.


4 Methods to Create a League Table in Excel

In the following dataset, you can see the Team Name and Points columns. Using this dataset, we will go through 4 easy methods to create a league table in Excel. Here, we used Microsoft Excel 365. You can use any available Excel version.

Dataset to create a league table in excel


1. Using RANK Function to Create a League Table in Excel

In this method, we will use the RANK function to create a league table in Excel. Here, the RANK function will rank the teams based on their points.

Steps:

  • First, we will type 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.
  • Afterward, press ENTER.

Therefore, you can see the result in cell D5.

  • Moreover, we will drag down the formula with the Fill Handle tool.

Hence, you can see the complete Rank column.

Read More: Create Table in Excel Using Shortcut (8 Methods)


2. Applying COUNTIF Function to Create League Table in Excel

In the following dataset, you can see the Team Name, Total Points, No.of Wins, No. of Draws, Points, and Rank columns. First, we will find out the Points column. After that, using the COUNTIF function, we will find out the Rank column. Hence, we will create a league table in Excel.

Let’s go through the following steps to do the task.

Step-1: Calculating Points Column

In this step, we will use a  formula to calculate the Points of the teams.

Here, as each No. of Wins equals 3 points and each No. of Draws equals 1 point, we will use the following formula in cell F5 to get the Points of each team.

=(D5*3)+E5

This simply multiplies D5 by 3 and then adds the result with E5.

Calculating Points to create a league table in excel

  • At this point, press ENTER.

Therefore, you can see the result in cell F5.

  • After that, we will drag down the formula with the Fill Handle tool.

As a result, you can see the complete Points column.

Step-2: Ranking Teams

In this step, we will use the COUNTIF function to find out the Rank of the following dataset based on the points gained by the teams.

  • To begin with, we will type 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 → the COUNTIF function counts the cell number that meets a certain criterion.
  • $F$5:$F$11 → is the range of cells
  • “>”&F5 → is the criterion.
  • +1 → it adds 1 with the returned result.
  • COUNTIF($F$5:$F$11,”>”&F5)+1→ becomes
    • Output: 4
  • Explanation: 4 indicates the rank of team Chelsea F.C.
  • After that, press ENTER.

Hence, you can see the result in cell G5.

  • Furthermore, we will drag down the formula with the Fill Handle tool.

Therefore, you can see the complete Rank column.

Read More: How to Make a Correlation Table in Excel (5 Handy Ways)


Similar Readings


3. Use of MATCH and OFFSET Functions

Here, we have a Team Name and Rank Table. Using this table, we will find out the Position and Team Name to create a league table in Excel.

Here, we will use the MATCH and OFFSET functions to do the task.

Let’s go through the following steps to do the task.

Step-1: Using MATCH Function to Get Position Column

In this step, we will use the MATCH function to find out the Position of the league table with respect to the Team Name and Rank Table dataset.

  • First of all, we will type 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) → the MATCH function 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
  • Later, we will press ENTER.

Therefore, you can see the result in cell C17.

  • Moreover, we will drag down the formula with the Fill Handle tool.

Hence, you can see the complete Position column.

Step-2: Using OFFSET Function to Find Out Team Name

In this step, we will use the OFFSET function to find out the Team Name of the league table with respect to the Team Name and Rank Table dataset.

  • First of all, we will type 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) → The OFFSET function returns a section from a data set with a specific height and a specific 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.
  • Moreover, we will press ENTER.

As a result, you can see the result in cell C17.

  • Later, we will drag down the formula with the Fill Handle tool.

As a result, you can see the complete Team Name column.

Read More: How to Create a Table Without Data in Excel (2 Easy Methods)


4. Creating League Table with Equal Values in Excel

In the following dataset, you can see that C6, and C7 have equal Points. Along with that, cells C8, and C9 have equal points. In this method, we will show you how you can create a league table when there are equal points.

Here, you can see that we added an Increment column to our dataset. This Increment column is helpful to find out the Total points of the teams when we have equal points.

Steps:

  • First of all, we will type the following formula in cell E5.
=C5*D5

This simply multiplies Points with Increment so that we get the Total.

Calculating Total to create a league table in excel

  • After that, press ENTER.

Then, you can see the result in cell E5.

  • At this point, we will drag down the formula with the Fill Handle tool.

Therefore, you can see the complete Total column.

Here, you can easily notice that no teams have equal points. Now, we can rank the teams according to their Total.

  • After that, we will type 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.
  • Afterward, press ENTER.

Then, you can see the result in cell D5.

  • Moreover, we will drag down the formula with the Fill Handle tool.

Therefore, you can see the complete Rank column.

Read More: Create a Table in Excel Based on Cell Value (4 Easy Methods)


How to Create League Table with Auto Sorting in Excel

Here, we will show you how you can create an Excel league table with auto sorting.

To do so, we will use the SORT function. Here, we have 2 data tables. The first Data set is unsorted. Next, we will show you how you can sort this data table with auto sorting.

Here, one thing must be remembered that the SORT function is only available on Excel 365 or higher versions of Excel.

Steps:

  • First, we will type the following formula in cell B17.
=SORT(B5:C11,2,-1)

Formula Breakdown

  • SORT(B5:C11,2,-1) →The SORT function sorts out the number in a range of cells.
  • B5:C11 → is the table_array
  • 2 → is the sort_index.
  • -1 → indicates ascending order.
  • Furthermore, as soon as we press ENTER, the dataset gets sorted.

Hence, you can see the sorted league table.

Read More: How to Create a Table with Existing Data in Excel


Practice Section

You can download the above Excel file to practice the explained method.


Conclusion

Here, we tried to show you 4 methods to create a league table in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo