How to Make a League Table in Excel That Ranks Itself

A league table in Excel that ranks itself needs two parts: a list of match results, and a summary table that counts and sorts everything from that list. Type a new score and the points, goal difference and positions all update. This guide builds one for a small four-team football league, step by step, but the same method works for five-a-side, netball, office quiz leagues or a sales contest.

Step 1: Enter the results, one row per match#

Start with a results list where each row is one match. Put it in A1:E7:

The Winner column is a formula, so you only type the teams and scores. In E2, fill down:

=IF(C2>D2,A2,IF(D2>C2,B2,"Draw"))

If the home side scored more, it returns the home team. If the away side scored more, the away team. Otherwise "Draw". This one helper column makes the counting much simpler, because COUNTIFS can't compare two columns with each other directly.

Step 2: Set up the league table#

Put the summary table to the right, starting in G1, with one row per team and these headers:

P is played, W won, D drawn, L lost, GF goals for, GA goals against, GD goal difference and Pts points. Type the team names in G2:G5: Lions, Tigers, Bears, Wolves. Every other cell is a formula that you write once in row 2 and fill down. The $ signs lock the results ranges so they don't move as you fill.

Step 3: Count played, won, drawn and lost with COUNTIFS#

A team plays some games at home and some away, so most counts add two parts.

Played (H2):

=COUNTIF($A$2:$A$7,G2)+COUNTIF($B$2:$B$7,G2)

Won (I2):

=COUNTIF($E$2:$E$7,G2)

Drawn (J2):

=COUNTIFS($A$2:$A$7,G2,$E$2:$E$7,"Draw")+COUNTIFS($B$2:$B$7,G2,$E$2:$E$7,"Draw")

Lost (K2): =H2-I2-J2. Every game is a win, a draw or a loss, so subtraction is safer than another count.

COUNTIFS counts rows where every condition is true, here "the team was at home" and "the result was a draw". The COUNTIFS and SUMIFS guide explains multi-condition counts in more depth, and the COUNTIFS function guide covers the syntax.

Step 4: Add goals and points with SUMIFS#

Goals use the same home-plus-away idea with SUMIFS, which adds the numbers in rows that match.

Goals for (L2): home goals when at home, plus away goals when away.

=SUMIFS($C$2:$C$7,$A$2:$A$7,G2)+SUMIFS($D$2:$D$7,$B$2:$B$7,G2)

Goals against (M2): the other column each time.

=SUMIFS($D$2:$D$7,$A$2:$A$7,G2)+SUMIFS($C$2:$C$7,$B$2:$B$7,G2)

Goal difference (N2): =L2-M2

Points (O2): =3*I2+J2, three for a win and one for a draw.

Fill row 2 down to row 5 and you get the full table, still in the order you typed the teams:

Two quick checks catch most mistakes: total goals for must equal total goals against (13 each here), and total wins must equal total losses (4 each). The SUMIFS function guide has more examples.

Step 5: Rank the table automatically with SORTBY#

SORTBY returns the table sorted by one or more columns, and it re-sorts every time a result changes. In an empty area, such as G8, type:

=SORTBY(G2:O5,O2:O5,-1,N2:N5,-1,L2:L5,-1)

This sorts by points (highest first), then goal difference, then goals for. The -1 means descending. The result spills into nine columns and four rows: Lions 7 points, Wolves 5, Tigers 3, Bears 1. Copy the headers above it and you have a live league table.

You can also use SORT with column numbers: =SORT(G2:O5,{9,8,6},{-1,-1,-1}) sorts by the 9th, 8th and 6th columns of the range. SORT and SORTBY need Excel 2021 or Microsoft 365. The SORT function guide and the comparison of SORT vs SORTBY explain the difference. In older Excel, select the table and use Data > Sort with three levels, as shown in sorting by multiple columns, but you'll need to sort again after each result.

How to keep the table up to date#

The table only stays automatic if new results land inside the ranges the formulas use. Two simple habits make that safe:

• Format the results as an Excel table. Click inside the results and press Ctrl+T (Mac: ⌘T). Use structured references such as Results[Home] in the formulas, and every new row is picked up on its own.
• Or make the ranges longer than you need. $A$2:$A$200 leaves room for a full season. Empty rows don't change any count or total.

Enter fixtures before they're played with blank scores, and the Winner formula would call them a draw. Wrap it to leave unplayed games empty: =IF(C2="","",IF(C2>D2,A2,IF(D2>C2,B2,"Draw"))). Use C2="" rather than ISBLANK so the test still works if the scores are formulas.

Add a position column that breaks ties#

A position number is useful when you want the table to stay in its original order, or for older Excel. In P2, fill down:

=COUNTIF($O$2:$O$5,">"&O2)+COUNTIFS($O$2:$O$5,O2,$N$2:$N$5,">"&N2)+1

The first part counts teams with more points. The second counts teams with the same points but a better goal difference. Add 1 and you have the position: Lions 1, Wolves 2, Tigers 3, Bears 4. Plain RANK.EQ would give tied teams the same position, as the RANK function guide explains.

Tie-break rules differ between competitions. Some use head-to-head results before goal difference, so check your league's rules and change the sort order to match. To show only the top teams, such as a top 3 for promotion, see how to build a top N list.

Practise it#

Try the exercise on this page: it calculates one team's points straight from the results, with no helper table. Then practise COUNTIFS and SUMIFS in a live spreadsheet and learn to sort tables with the SORT function, where every formula you type is checked instantly.