World Cup 2018 Excel Template

Last week I was helping a friover build a World Cup pool in Excel. It was fun debating the pool’s scoring rules. I also taught hyên ổn a few Excel tricks along the way.

Bạn đang xem: World cup 2018 excel template

Excel Files

There are 2 files. Cleaning FIFA country list and World-Cup-Template-2018v2.

Data Cleanup (Flash Fill)


I found a country danh mục with FIFA rank. I used Flash Fill as it appeared lớn be a simple pattern (remove sầu 1st three letters). Further down ‘IRNIR Iran’ was converted to lớn ‘IR Iran’. That’s not right! Learn more from J-Walk!


Any Normal Person….

With such a small danh mục & only 1 exception any normal person would manually correct the entry. I guess I’m not normal. I enjoy these puzzles. And what if the các mục had been much longer? A formula can solve it quicker than manually fixing entries.

Xem thêm: Hướng Dẫn Sửa Lỗi Ghost Xong Không Vào Được Win, Không Bootwin Sau Khi Ghost

What’s The Pattern?

Flash Fill made the first mistake. I made the second mistake.

Find The Last Upper Case Letter

I thought that this would work. I obviously didn’t scan the data. “CODCongo DR” breaks the pattern.

Find The First Lower Case Letter và Go Bachồng One Space

This worked! In a nutshell, look for all lower case letters inside each text. We want the position of the 1st lower case letter. See my Excel file (above) for a full explanation of this array:


World Cup Pool Scoring System

The original idea was that everyone would select 2 teams from each group. However, everyone would piông chồng mostly the same teams. Where’s the fun in this? I recommended the following:

You get 1500 points lớn select 2 teams from each groupPoints are based on FIFA rating for each country

This way there’s more strategy in selecting teams. See my World Cup Template above.

Data Validation Trick

Manually creating 8 different data validation lists seemed too tedious. Unlocking the column reference allows us khổng lồ create only 1 data validation list and then copy / paste it for all other groups