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.

The task: orders have a customer ID; countries live in another sheet. Bring the country across.

=XLOOKUP( what_to_find , where_to_look , what_to_return , if_not_found )

Concretely:

=XLOOKUP(A2, Customers!$A$2:$A$500, Customers!$C$2:$C$500, "NOT FOUND")
         │    │                       │                      │
         │    │                       │                      └ instead of #N/A
         │    │                       └ column to bring back (country)
         │    └ column to search (customer IDs) — LOCKED with $
         └ the ID on this row — relative, so it moves down

Drag it down. Every order now has a country.

→ Reading a lookup someone else wrote? Drop it into the Formula Explainer tool to see exactly what each argument does.

Four things that decide whether this works:

1. Lock the lookup ranges with $. Unlocked, row 3 searches one row lower and quietly returns wrong answers. This is the most common real-world spreadsheet error and it doesn't announce itself.
2. Always set the fourth argument. "NOT FOUND" is infinitely better than #N/A — it tells you how many IDs are missing rather than scattering errors.
3. Types must match. An ID stored as text won't match the same ID stored as a number. If a lookup fails on values you can see are identical, this is why. TRIM and check the alignment — text left-aligns, numbers right-align by default. That alignment is your type indicator.
4. #N/A is information. It means "not found", which is often the real finding: your customer list is incomplete.

Older Excel — INDEX/MATCH:

Copy

=INDEX(Customers!$C$2:$C$500, MATCH(A2, Customers!$A$2:$A$500, 0))

Same job. MATCH finds the row number, INDEX fetches from it. The 0 means exact match and is not optional — omit it and you get approximate matching, which returns confident nonsense on unsorted data.

On VLOOKUP: you'll see it everywhere. It can only look rightward, breaks when a column is inserted, and defaults to approximate matching. Learn to read it, don't write new ones.

✅ Check: every row has a country or an explicit "NOT FOUND", and you know how many didn't match.

You could answer "sales by country by month" with a wall of SUMIFS. A pivot does it in about fifteen seconds and rearranges on demand.

1. Click inside your data → Insert → PivotTable (Excel) or Insert → Pivot table (Sheets)
2. You get four drop zones. This is the whole concept:

   ROWS      what to group down the side      → Country
   COLUMNS   what to group across the top     → Month
   VALUES    what to calculate                → Sum of Amount
   FILTERS   what to restrict the whole thing → Year

3. Drag fields in. The table builds itself.

The mental model that makes pivots click: you are answering "what per what". Sales per country. Orders per month. Average score per category. The first "what" goes in Values, the second in Rows.

Three things worth knowing immediately:

• Values defaults to Sum for numbers and Count for text. Click the field to change it to Average, Max, Count. Half of all pivot confusion is a field summarising the wrong way.
• "Show values as → % of column total" turns counts into percentages without a single formula. Buried in a menu and enormously useful.
• Pivots don't auto-refresh. Change the source data and you must Refresh (right-click → Refresh). This catches everyone at least once. Tables help — new rows land inside the source range.

✅ Check: a pivot answering a real question about your data, and you've changed one field from Sum to Average and watched it re-compute.

If it moved: look for "PivotTable", "Pivot table" or occasionally "Summarise with pivot". The four drop zones are universal.

The point isn't decoration. It's that you shouldn't have to scan 400 rows to notice a problem.

Select your range → Conditional formatting.

Three rules that earn their place:

1. Threshold — "highlight if over budget"

Format cells greater than → 500 → red fill

2. Colour scale — the shape of a column at a glance

Colour scale, green→red. On a numeric column you now see distribution without a chart.

3. Formula-based — the one worth learning properly

The others are presets; this one is general. It highlights a whole row based on any condition:

Select A2:E400 → New rule → "Use a formula"
Formula:  =$D2="OVERDUE"

Note the mixed reference: $D locks the column so every cell in the row tests column D, while 2 stays relative so each row tests its own. Get that wrong and the highlighting is nonsense — it's the same $ logic from TL-01, and this is where it pays off.

Useful formula rules:

=$D2="OVERDUE"                    flag by status
=$B2>$H$1                         over a threshold held in a cell
=COUNTIF($A$2:$A$400,$A2)>1       highlight duplicates
=$C2=""                           missing data

That last one is a genuine quality check: make missing values visible instead of hoping you'll notice them.

✅ Check: one formula-based rule highlighting entire rows correctly, tested by editing a value and watching the highlight move.

Analysis sheets are wrong more often than people expect, and they're wrong silently.

Four checks, every time:

1. Does the total match? Sum your raw amount column. Sum the pivot's grand total. They must be identical. If not, your source range is wrong — usually a blank row.
2. Count your lookups. =COUNTIF(E2:E400,"NOT FOUND"). Zero unmatched is suspicious in real data; a lot unmatched means a type mismatch.
3. Spot-check three rows by hand. Pick three at random and verify manually. This catches the errors that pass every automated check.
4. Change one input and watch it flow. If the pivot doesn't move, you forgot to refresh.

✅ Check: raw total equals pivot total, and you know your unmatched count.

Six common mistakes

1. Unlocked lookup ranges. Works in row 2, wrong from row 3 down, no error shown. The most expensive mistake in this lesson.
2. No fourth argument on XLOOKUP. #N/A scattered everywhere instead of a countable flag.
3. Text vs number mismatch. Identical-looking IDs that don't match. Check the alignment.
4. Forgetting to refresh the pivot, then presenting last week's numbers.
5. Formatting inside the data range — colouring source rows by hand instead of by rule. It stops being true the moment anything sorts.
6. Building the analysis before fixing the shape. Merged cells and blank rows will beat you every time. Step 1 exists for a reason.

Exercise (60 min, verifiable output)

Use two real datasets that share a key. Options: your transactions + a category-to-budget table · a club's members + attendance · a music export + genres.

1. Shape both to step 1's rules. Format both as tables.
2. XLOOKUP a field from the second into the first. Lock the ranges. Set the not-found value.
3. Count your unmatched rows. If any, diagnose: genuinely missing, or a type mismatch?
4. Build a pivot answering a real "what per what" question.
5. Change one field from Sum to Average. Add "% of column total".
6. Add two conditional formatting rules: one threshold, one formula-based row highlight.
7. Run all four sanity checks from step 5. Record the raw total and pivot total side by side.
8. Add three new rows to the source. Refresh. Confirm everything updates.

✅ Finish check: a joined dataset with a known unmatched count · a working pivot · a formula-based row highlight · matching raw and pivot totals written down · proof that adding rows flows through.

Cheatsheet

SHAPE FIRST — nothing works otherwise
  one row = one observation · one column = one thing
  no merges · no blank rows · no totals inside the data
  format as a Table → ranges grow, refs become readable

XLOOKUP
  =XLOOKUP(A2, $range$, $return$, "NOT FOUND")
   relative  locked   locked      always set this
  old Excel: =INDEX($ret$, MATCH(A2, $find$, 0))    ← the 0 is required
  lookup failing on identical-looking values?
     → text vs number. check alignment: text left, numbers right

PIVOT — "what per what"
  ROWS group down · COLUMNS group across
  VALUES calculate · FILTERS restrict
  defaults to Sum (numbers) / Count (text) — change it deliberately
  "Show values as → % of column total"
  DOESN'T AUTO-REFRESH

CONDITIONAL FORMATTING
  formula-based is the general one:
     =$D2="OVERDUE"        $ locks the column, row stays relative
     =COUNTIF($A$2:$A$400,$A2)>1     duplicates
     =$C2=""                          missing data

BEFORE YOU TRUST IT
  raw total == pivot total? · how many NOT FOUND?
  spot-check 3 rows by hand · change an input, watch it flow

Next lesson: TL-03 — Cleaning Messy Data (L2) Related: TL-01 Spreadsheets From Zero · TL-04 Dynamic Arrays and Dashboards · AI-09 Working with Data · PS-04 Presenting Data Path: Spreadsheet Fluent — 2/5