Spreadsheets, Level Two: XLOOKUP, Pivot Tables, Conditional Formatting

⚠️ Version note: XLOOKUP exists in Microsoft 365, Excel 2021+ and Google Sheets. On older Excel you'll need INDEX/MATCH — covered in step 3. Menu paths move between versions; each step says what to look for. Last checked: 24 July 2026.

The wall everyone hits

You can build a sheet. You can write SUMIF. And then a real task arrives:

Here's a list of 400 orders with customer IDs. Here's a separate list of customers with their countries. How much did we sell in Germany?

Nothing in TL-01 does that, because the answer lives across two tables, and joining tables is where spreadsheets stop being a calculator and start being a database you can see.

This lesson is three tools. Each solves one problem that has no reasonable manual workaround.

What you'll have at the end

• Two real datasets joined, with the lookup working correctly
• A pivot table that answers a question you'd otherwise compute by hand
• Conditional formatting that makes exceptions visible without you looking for them
• A sheet that stays correct when the data grows

---

Almost every "the formula won't work" problem is a data-shape problem. Before anything else:

One row = one observation. One column = one thing. One cell = one value.

That last one causes real damage: a total row inside your range gets included in the next total, silently doubling it.

Then make it a Table. Select your data → look for Format as Table (Excel) or Format → Convert to table (Sheets).

Worth thirty seconds because:

• Ranges grow automatically — new rows are included in every formula pointing at the table
• You get filter buttons free
• You can refer to Orders[Amount] instead of B2:B400, which is readable and doesn't break

✅ Check: your data is one header row, no merges, no blanks, no totals inside, and formatted as a table.

---