Use multiple tables to create a PivotTable

You can absolutely use multiple tables to create a PivotTable in Excel — and it’s especially powerful when your data is related but stored in separate tables. This is done using the Data Model in Excel, which lets you create relationships between tables (similar to how databases work).

✅ How to Create a PivotTable from Multiple Tables

🧱 Step 1: Format Your Data as Tables

1. Select each dataset and press Ctrl + T to format it as a Table.
2. Give each table a meaningful name:
   • Go to the Table Design tab → rename in the Table Name box (e.g., Sales, Products, Employees).

🔄 Step 2: Create a Relationship Using the Data Model

1. Go to the Insert tab → click PivotTable.
2. In the dialog:
   • Choose “Use this workbook’s Data Model”.
   • Check “Add this data to the Data Model” (if prompted).
3. Select “From External Data Source” → then choose the tables.

🔗 Step 3: Define Relationships Between Tables

1. Go to the Data tab → click Relationships.
2. Click New and define relationships using key fields (e.g., ProductID in both Sales and Products tables).

   Example:

   • Table: Sales
   • Column: ProductID
   • Related Table: Products
   • Related Column: ProductID
3. Repeat for any other related tables.

📊 Step 4: Build the PivotTable

1. Now go back to your PivotTable.
2. In the PivotTable Fields pane, you’ll see all tables listed.
3. Drag fields from different tables — Excel will use the Data Model to connect them automatically.

🎯 Example Use Case

Suppose you have:

• Sales table: OrderID, ProductID, Quantity
• Products table: ProductID, ProductName, Category
• Employees table: EmployeeID, Name, Region

You can build a PivotTable that summarizes sales by product category and region, even though those fields live in different tables!