How to link multiple Excel files into one Pivot table
One of the powerful features of Excel is the ability to create Pivot tables from multiple data sources using relationships. Relationships are connections between two tables that have at least one column in common. To create relationships in Excel, you need to use the PowerPivot window, which is an add-in that enhances the data analysis capabilities of Excel. Here are the steps to create relationships in Excel using the PowerPivot window:

1. Enable the PowerPivot add-in if you haven’t done so already. Go to File – Options – Add-Ins and select COM Add-ins from the Manage drop-down list. Click Go and check the box next to Microsoft PowerPivot for Excel. Click OK and you should see a new PowerPivot tab in the ribbon.
2. Click on the PowerPivot tab and select Manage Data Model. This will open the PowerPivot window, where you can import, manage and analyse your data.
3. In the PowerPivot window, click on Home – Get External Data and choose your data source. You can import data from various sources, such as Excel files, databases, web pages, etc. Follow the instructions on the screen to select the tables or queries you want to import. You can also rename, filter and format your data as needed.
4. Repeat step 3 for each data source you want to use in your Pivot table. Make sure that each table has a unique name and that there is at least one column that can be used to link the tables together.
5. To create a relationship between two tables, click on Diagram View in the bottom right corner of the PowerPivot window. This will show you a graphical representation of your tables and their columns.
6. Drag and drop the column that you want to use as a link from one table to another. A line will appear between the two tables, indicating that a relationship has been created. You can also right-click on a column and select Create Relationship to manually define the relationship parameters.
7. Repeat step 6 for each pair of tables you want to relate. You can create multiple relationships between the same tables or between different tables, as long as they make sense logically and do not create circular references or ambiguity.
8. To create a Pivot table from your related tables, go back to Data View in the PowerPivot window and click on PivotTable in the Home tab. Choose where you want to place your Pivot table and click OK.
9. In the Pivot table fields pane, drag and drop the fields you want to use from any of the related tables. You can also use calculated fields or measures to perform calculations on your data.
10. Format and customize your Pivot table as desired. You can change the layout, design, filters, slicers, etc.

You have successfully created a Pivot table from multiple data sources using relationships in Excel with the PowerPivot window.
Join this channel to get access to perks:

If you want to support me, check this out:

Online training courses:

Word course:

Excel Introduction Course for basic users:
Excel Intermediate Course:

Excel Advanced Course:

Microsoft Project Master Class:

Microsoft Visio Essentials:

Access for beginners course:

Access Master Class:

Classroom based courses can be arranged at

Courses are available through Microsoft Teams, Online and Face to face in the UK

Udemy Courses

Training Guide Books

Excel for New Managers

Microsoft Project Overview

Microsoft Visio Basic

Very cheap E_books are here:
Excel For New Managers
Microsoft Project Overview
Microsoft Visio Basics
Microsoft Access Introduction
A PowerPoint Overview

Leave a Reply

You must be logged in to post a comment.


“Steve’s background in IT training clearly provided him with great organisational and managerial skills which he put to good use at Second Chance Enterprises. His ability to see the long term strategy as well as manage the day to day management is one of his biggest strengths.”

Chriss Graham
Project Coordinator at Daisy Group

“I studied with Steve when we completed our CiPD CTP together. During the course I found Steve to be very personable and also very professional.  Steve was first on my list of suppliers. Steve was very competitively priced and very accommodating.”

Simon Snowdon
European Training Manager at HP Enterprise Services

“The course was really thorough and Steve was very knowledgeable and explained things very well. I’ve not used Microsoft Project before but now I feel very confident that I’ll be able to use it for what I need to do in my job”.

Craig Forster
Estimator, John F Hunt Regeneration Ltd

“Steve delivered a number of Microsoft Office Training Sessions to a range of employees across Newcastle College. The service provided by Steve is second to none. I have no hesitations in recommending him to other employers.”

Christine Jackman
Learning Manager(North) - Winner of Training Programme of the Year NECCA