digest
Short Summary
Main recommendation: Instead of using a VLOOKUP helper column to combine two tables, convert both tables to Excel tables and add them to the Excel 2013 Data Model, then create a relationship so both tables appear in the PivotTable Field List. The video notes this works without the Power Pivot add-in, though the presenter says he would probably still use VLOOKUP, calling this trick unlikely to be used often but potentially useful to some viewers.
Key Points
- The goal demonstrated is a report of Region by Revenue, where Region sits in a separate lookup table keyed by Sales Rep.
- Excel 2013 includes the xVelocity columnar engine, so the Data Model and relationships are accessible from the PivotTable dialog without the Power Pivot ribbon tab.
- Without Power Pivot, calculated columns and calculated fields are not available, so the trick is limited to building relationships and pulling fields from two tables.
- Without a relationship, summing Net Revenue across both tables gives an incorrect total, which is why the relationship is required.
- The same relationship can be built in the Power Pivot Diagram View by dragging the Sales Rep column from the transaction table onto the Sales Rep column in the lookup table.
Details Worth Noting
- Concrete instructions:
- Select the first data range and press Ctrl+T to convert it to an Excel table, then press Enter to confirm.
- Press Alt, J, T, A to jump to the table name box and name the lookup table "D_SalesRep" (the presenter's name for it), then press Enter.
- Convert the second table to an Excel table as well so both appear in the field list.
- Select a cell in the transaction table, press Alt+V to open Insert PivotTable, choose a new worksheet, and check "Add this data to the Data Model."
- In the PivotTable Field List, switch to "All" to see both tables; the table with a black line has been added to the Data Model.
- When Net Revenue summed incorrectly, click "Create" in the "Create Relationship" prompt, set the transaction table as the first table with Sales Rep as the foreign key column, then set the lookup table (Sales Rep) as the related lookup table.
- In Power Pivot (if installed), add the table with Alt, B, Y or the Add to Data Model button, then drag Sales Rep onto Sales Rep in Diagram View to create the relationship.
- The presenter notes that Excel 2013 without the Power Pivot ribbon does not show the "Related Lookup Table" fields in the relationship dialog, while Power Pivot does.
- To enable Power Pivot, go to Options, Add-Ins, Manage COM Add-ins, click Go, and select it.
- Only one Data Model can exist per workbook.
Bottom Line
Excel 2013's PivotTable Field List can relate two tables through the Data Model, letting you report across them without a VLOOKUP helper column, though calculated columns and fields still require the Power Pivot add-in.
