Instead of adding VLOOKUP columns to bring region or category into a sales table, connect the tables with a relationship. A PivotTable can then use fields from all of them at once, with no extra formulas.
Table of Contents
Video
The problem
Sales_Data has the city of each sale, but not its region or country. Those are in another table, Place. A manager wants total sales in South India.
The usual fix, VLOOKUP columns for region and country, means 100 extra formulas for 50 rows, and more for every new question. A relationship needs none.
What you need
- Both ranges must be Excel Tables (Ctrl + T). Give them clear names on the Table Design tab.
- A column with matching values in each, here Location in Sales_Data and City in Place.
- In the lookup table (Place), each city must appear only once.
Step 1: Create the relationship
- Data → Relationships → New.
- Table: Sales_Data, Column (Foreign): Location.
- Related Table: Place, Related Column (Primary): City. Click OK.
Step 2: Build the PivotTable
Insert → PivotTable → From Data Model (in older versions, tick Use this workbook's Data Model). Both tables appear in the field list.
Drag Region and Country (from Place) to Rows and Amount (from Sales_Data) to Values. South India: ₹7,40,260.
Add more tables
For units sold by product category, relate Sales_Data[Item] to Product[Product] the same way. One PivotTable can now use fields from all three tables.
To see all relationships as a diagram, open Data → Manage Data Model (Power Pivot) → Diagram View.
Things to know
- If the PivotTable shows the same total on every row, the relationship is missing or the fields come from the wrong tables.
- Excel can't relate tables when the lookup column has duplicates or blanks.
- For a single extra column, an XLOOKUP is still simpler. Relationships pay off with several lookup tables or large data.
- Excel for Mac supports the Data Model in Microsoft 365, but not all Power Pivot features.