Charting Team Lineups & Formations (Excel)

We have all seen the team lineups and formations graphics on TV for soccer (European football) games. It got me wondering if I could create a chart instead of a static image that can be easily scaled for any teams, any formations just by updating the underlying tables. It turns out, I can. It takes quite a few steps but it’s not very difficult. In this post, I’ll give an overview of how it’s done.

It starts with structuring the underlying tables; one for each team. The tables should have at minimum, X and Y coordinate values because I’ll be using a Scatter plot. Ideally, we also want the names and jersey numbers of the players for both teams to make it more informative. And as a final touch, I’ll add a soccer field image as the chart’s background, and add team names and logos.

I’ll start with the actual game information from the leg 1 of UEFA 2024 Semifinal game between Dortmund and PSG.

My tables look like this:

Each team is using different formations. I also added a Player column that just uses shorthand to indicate what position each player is playing along with the full position name string. and as one team will be facing the other, team1 will be on one side of the field, and the other team will on the other. So, to show the formation in a portrait mode, one team’s goalie will be at the top and it’s forwards will be at the lowest tier toward the center of the field, while the other team’s goalie will be at the very bottom and its forwards will be near the top facing the center of the field. The final formations should look something like this for example:

Then I’ll add a scatter plot for each using the X-values, Y-values for each table. The two columns determine the position of each player for each team. For greater flexibility, I decided to create each team’s chart separately, that way, if we want to show each team one at a time, we can. If we decided to show both teams on one chart, we can just combine them to create one larger chart.

After I got a soccer field image from my AI agent, I halved it to create two parts of the pitch. Then I obtained each team’s logo and their jersey colors that they wore for that exact game. The first chart for Dortmund looks like this based on Team 1 Table:

I added the title with a formula from the Table title cell. Then the data points got their values from Name column, and their jersey numbers as textboxes with formula from PlayerNumber column.

The PSG chart looks like this based on Team 2 Table:

Finally, we can combine them and group them together to show a single larger chart as this:

There are quite a few clicks and key presses involved to get here but that’s all there’s to it in general and once they’re built, it’s much quicker to change them as needed for other teams. I hope you found it interesting. Happy charting!

Related Blogs:

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top