Skip main navigation

Why connect data sources?

What are some reasons to consider connecting your data sources, and how can you gain more meaningful insights from them? Learn more in this article.

When your source data is a complete data set and contains all of the detail you need to summarise your data by the required collections – you won’t have to connect data sources. But when your data is relational (stored in separate, related tables like a database), you’ll want to bring these separate sources together to provide meaningful insights.

Table 1: Sales TableTable 1: Sales Table

For example, if you’re importing a list of products sold from a database, it’s likely that each sale record in the database will only list the item number of the product, the ID number of the salesperson and the specifics of the sale (date, time, amount), but it probably won’t list the product information nor the salesperson information.

Table 2: Products TableTable 2: Products Table

In a separate (but related) table, all product information will be recorded, with each products item number, manufacturer, make, model, product description, category, etc.

Table 3: Employees TableTable 3: Employees Table

And in another (separate but related) table, all employees will be listed with their ID number, department, region, and other relevant information.

In order to summarise the sales by category, make, model, department or region, the tables will need to be joined together.

In the old days, prior to the inclusion of PowerPivot data modelling in Excel, this could only have been achieved by manually updating the data source with the additional information; either by adding the extra columns and entering the information, or by creating VLookup functions to look up and return the required information from external workbooks before creating the PivotTable. This would require regular maintenance: updating the source before refreshing the PivotTable, and it would only be possible with data is stored in Excel.


With the recent addition of new data tools, Excel makes it super easy to connect a PivotTable to multiple sources and create relationships between fields in the separate tables – negating the need to update the original data source with additional information. So instead of manually editing the source data, we can simply add multiple sources to the data model. As long as the seperate sources have a common value (e.g. Employee ID or Product ID), we can build our PivotTable from multiple related tables and create relationships to match the records.

All tablesClick to expand

This article is from the free online

Mastering Excel Pivot Tables

Created by
FutureLearn - Learning For Life

Reach your personal and professional goals

Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.

Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.

Start Learning now