Excel project

World Cup Shirt Orders: Advanced

The same order sheet, but with the messier real-world data the basic project glossed over: a courier code that only makes sense from its first letter, a country code that might be 2 or 3 letters, a fee stored as text, a real two-axis distance table, and a tiered highlight rule.

Projects are multi-step builds, not single exercises: each one chains several real skills together on one dataset, the way an actual workbook gets built, not graded one isolated formula at a time. Steps unlock in order, so finishing one is what opens the next.

What this project covers

Step 1 of 5: Standardize a messy courier code

Customers type their courier however they like ("UP", "up", "UPS", it varies), but every version starts with the same letter. Standardize it: if the code starts with "C" it's CHRONOPOST, "D" is DHL, "R" is ROYAL AIR MAIL, "U" is UPS, anything else is TNT.

Step 2 of 5: Look up a country from either code format

The country reference table has both a 2-letter and a 3-letter code column, and this order's raw code could be either. Try matching it against the 3-letter column first; if that comes back empty, fall back to the 2-letter column.

Step 3 of 5: Clean up a fee stored as currency text

The team's sponsor fee came out of the source system as text ("$0.75", dollar sign included) instead of a real number, so nothing downstream can do math with it yet. Strip the "$" and turn it into a usable number.

Step 4 of 5: Look up a distance from a two-axis table

Shipping distance isn't a single lookup. It depends on BOTH the customer's area and the favorite team's area, crossed against each other in a distance table, the same way the real exam's Distance sheet worked. Find the distance between this order's two areas.

Step 5 of 5: Flag the tournament's top scorers

The real exam colors a player's row by their goal tally: none, a couple, or a lot. Build the top tier here: highlight every player with more than 2 goals.

The dataset, the exact approach, and the answer for each step are part of the unlocked project itself, not shown here.

More projects

Projects are part of the full unlock

Projects are locked until you unlock the full site: multi-step builds chaining pivot tables, charts, conditional formatting, lookups, named ranges, and more on one real dataset. Open this one to see how to unlock the rest.

Open this project →