digest
Short Summary
Main recommendation: Use Excel's Relationships feature (Data Tools > Relationships, or Power Pivot if available) instead of VLOOKUP helper columns when you need to pull lookup data into a pivot table source, especially on large one-to-many data sets. The relationship approach avoids tens of thousands of formulas, produces a much smaller file, and works in Excel 2013, Excel 2016, and Office 365.
Key Points
- The video explains that a VLOOKUP helper column on a 73,000-row sales table adds many formulas (done twice in the example), making the file heavy; relationships replace those lookups.
- A relationship works like VLOOKUP but between tables: the lookup table's first column must hold a unique list (the primary key), and the sales table holds the repeating foreign keys in a one-to-many structure.
- Power Pivot's Data Model combines three parts: a columnar in-memory database for big data, relationships between tables, and DAX formulas (DAX is previewed for later videos).
- Power Pivot supports millions of rows, multiple tables in the pivot field list, and DAX calculations designed for big data.
Details Worth Noting
- Concrete instructions:
- Convert each source range to an Excel table: select the range, press Control-T, press Enter, then under Table Tools > Design > Properties, give it a name (example: "Product Lookup Table").
- Go to Data > Data Tools > Relationships > New.
- In "Table," select the fact/sales table (the many side, foreign key). In "Related Table," select the lookup table (the one side, primary key).
- Pick the matching key column on each side (for example, Product on the sales side and the Product primary key on the lookup side), then click OK.
- Repeat with New for the second lookup (for example, Country Code linking Sales to Country). The Table dropdown will now show Data Model tables as well as worksheet tables.
- Insert a PivotTable (shortcut Alt-N-V). Choose the Data Model, then drag fields from the related tables into Rows and Values (Country and Product into Rows, a Revenue number field into Values).
- To see the Power Pivot tab, go to File > Options > Add-ins > Manage > COM Add-ins > Go, check the Power Pivot add-in, and click OK. Manage Data Model then opens the Power Pivot window, where Diagram View shows the one-to-many relationships.
- Dragging a number field into Values creates what looks like a standard calculation but is actually an implicit DAX measure; the speaker says this is fine for casual relationship use but not for full data models.
- In Office 365 all table icons look the same; hover over a table to see whether it is a Model table or a worksheet table, or right-click and choose "Show in Active" to move tables into the Data Model.
- The speaker notes that in earlier versions, only Data Model table icons showed a dark line across the top while other Excel tables did not, helping distinguish them; in Office 365 all icons look the same so you must hover to check.
Bottom Line
Relationships are a cleaner, lighter alternative to large VLOOKUP helper columns for building pivot tables from multiple related tables, and they lay the groundwork for the fuller Power Pivot Data Model covered in later videos.
