Y

Excel · Beginner · 5 h

Pivot tables and lookups

Summarise half a million rows with a pivot table, and join sheets with XLOOKUP.

Why employers care: In a job description, "advanced Excel" almost always means pivot tables, lookups and clean formulas. It is tested in assessments at services firms and in operations analyst roles.

In the roadmaps for: Data Analyst, Data Scientist, Data Engineer

Lessons, in order

  1. Pivot tables on half a million sales linesSummarise a real online retailer's year by country and month in minutes, and spot the outliers a pivot table hides.3 min
  2. Excel formulas: relative vs absolute cell referencesWrite formulas that copy down half a million rows correctly, lock the cells that must not move, and read every Excel error value at a glance.12 min
  3. XLOOKUP and the cleaning formulas you use every weekJoin two sheets safely, and fix text, dates and codes with TRIM, TEXT, IFERROR and friends.3 min
  4. Excel Tables, sort and filter for clean dataTurn a raw export into an Excel Table, sort and filter half a million rows safely, and get totals that respect the filter.11 min

Next shelf: Power Query for repeatable cleaning