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.

Method 1 – Using the RANK Function

Steps:

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

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.

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.

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

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.

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)

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)

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.

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)

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.