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.
How to Create a League Table in Excel: 4 Easy Methods
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.
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)
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: How to Make a Table Bigger in ExcelÂ
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)+E5This simply multiplies D5 by 3 and then adds the result with E5.
- 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
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 Decision Table in ExcelÂ
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)
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) 
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.
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*D5This simply multiplies Points with Increment so that we get the Total.
- 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)
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: How to Make a Conversion Table in Excel
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.
Practice Section
You can download the above Excel file to practice the explained method.
Download Practice Workbook
You can download the Excel file and practice while you are reading this article.
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.
Related Articles
- How to Create Table from Another Table in Excel
- How to Create Table from Another Table with Criteria in Excel
- How to Mirror Table on Another Sheet in ExcelÂ
- How to Create Table from Multiple Sheets in Excel
- How to Create a Lookup Table in Excel
- How to Make 3D Table in Excel
<< Go Back to Make a Table | Excel Table | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
