How to Create a PivotTable from Multiple Tables in Excel?

Sharing is caring!

Creating a PivotTable from multiple tables is one of the most efficient ways to analyze and summarize data from different sources in Excel. Instead of manually merging datasets or performing repetitive calculations, you can use Excel’s built-in tools to consolidate, relate, and analyze data effectively.

In this guide, we will provide a detailed step-by-step explanation of how to create a PivotTable from multiple tables, ensuring your data analysis is both dynamic and accurate.

Why Use Multiple Tables in a PivotTable?

Using multiple tables in a PivotTable allows you to:

• Analyze related datasets without merging them manually.
• Maintain data integrity by keeping original tables intact.
• Create dynamic relationships between datasets for better insights.

Step-by-Step Guide to Create a PivotTable from Multiple Tables

1. Prepare Your Data

Before creating a PivotTable, ensure your data is clean and organized. Follow these steps:

1. Format Each Dataset as an Excel Table:
   • Select the dataset.
   • Press Ctrl + T or go to Insert > Table.
   • Check the box for “My table has headers.”
   • Name each table descriptively using the Table Design tab (e.g., “SalesData” or “ProductInfo”).
2. Ensure that each table has at least one column that can act as a common field (e.g., Product ID in both SalesData and ProductInfo).
3. Avoid blank rows or columns within your tables, as they can interfere with relationships and analysis.

2. Combine Tables Using Relationships

Excel’s Data Model allows you to connect multiple tables through relationships based on shared fields. This eliminates the need for manual merging while enabling seamless analysis.

Step 2.1: Add Tables to the Data Model

To add your tables to the Data Model:

1. Click anywhere inside the first table.
2. Go to Insert > PivotTable.
3. In the dialog box, check the option for Add this data to the Data Model.
4. Repeat this process for all other tables.

Step 2.2: Define Relationships Between Tables

Once all tables are added to the Data Model:

1. Go to the Data tab and click on Relationships.
2. In the Relationships window, click on New.
3. Define relationships by selecting:
   • The primary table (e.g., SalesData).
   • The related table (e.g., ProductInfo).
   • The common field (e.g., Product ID).
4. Repeat this process for all related tables.

For example:

• Link “SalesData” and “ProductInfo” using Product ID.
• Link “SalesData” and “RegionData” using Region ID.

By defining relationships between these tables, you can analyze sales by product name or region without manually combining datasets.

3. Create the PivotTable

Once relationships are established, follow these steps to create your PivotTable:

1. Go to Insert > PivotTable.
2. In the dialog box:
   • Select “Use this workbook’s Data Model” as the source.
   • Choose where you want to place the PivotTable (new worksheet or existing worksheet).
3. Click OK.

4. Add Fields to Your PivotTable

In the PivotTable Field List:

1. Expand each table to view its fields.
2. Drag fields into appropriate areas:
   • Rows: Categories like products, customers, or regions.
   • Columns: Grouping variables such as years or quarters.
   • Values: Numeric data like sales or quantities (use aggregation functions like Sum or Average).
   • Filters: Criteria for filtering data dynamically.

For example:

• Drag “Product Name” from ProductInfo into Rows.
• Drag “Value” from SalesData into Values.
• Drag “Region” from RegionData into Filters.

Your PivotTable will now display consolidated insights based on multiple tables.

Alternative Method: Combine Tables Using Power Query

If your tables have identical column structures but reside on different sheets or files, Power Query is an excellent tool for appending them into one table before creating a PivotTable.

1. Go to the Data tab and click on Get Data > From Other Sources > From Table/Range.
2. Load each table into Power Query Editor by repeating this step for all datasets.
3. Use the Append Queries option under the Home tab in Power Query Editor:
   • Select two queries (tables) and combine them into one query.
4. Repeat until all tables are appended into one consolidated query.
5. Close and load the appended query as a new table in Excel.
6. Use this new combined table as the source for your PivotTable.

Example Use Case

If you have monthly sales data stored in separate sheets (e.g., JanuarySales, FebruarySales), Power Query allows you to append these sheets into one comprehensive dataset called “AllSales.” You can then create a PivotTable from this single dataset.

Comparison of Methods to Create a PivotTable from Multiple Tables

Here’s a quick comparison of using Relationships vs Power Query:

Both methods are powerful but cater to different scenarios depending on how your data is structured.

Additional Tips for Optimizing Your PivotTable

To make your PivotTables more effective and user-friendly:

1. Use Slicers for Better Filtering

Slicers provide an interactive way to filter data visually:

• Go to the PivotTable Analyze tab and select Insert Slicer.
• Choose fields like Date, Region, or Product Category for filtering.

2. Refresh Data Automatically

If your source data updates frequently:

• Right-click on your PivotTable and select Refresh.
• Alternatively, set up automatic refresh by linking external sources via Power Query.

3. Add Calculated Columns

For advanced analysis, add calculated columns directly within Excel Tables or Power Query Editor:

• Example: Add a Profit column by subtracting cost from sales (Profit = Sales – Cost).

4. Apply Conditional Formatting

Highlight key trends or outliers directly within your PivotTable:

• Select cells in Values area > Home tab > Conditional Formatting > Choose formatting rule (e.g., Top/Bottom Rules).

Common Challenges When Working with Multiple Tables

While creating a PivotTable from multiple tables is straightforward with practice, here are some common challenges users face along with solutions:

Final Thoughts

Creating a PivotTable from multiple tables in Excel is an essential skill for anyone working with complex datasets across various sources. By leveraging tools like Relationships and Power Query, you can consolidate data efficiently without manual merging while maintaining flexibility for updates and analysis.

Whether you’re analyzing sales trends across regions or tracking inventory performance by product categories, following these steps will help you unlock deeper insights quickly and accurately.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.