Excel Connection Between Two Excel Workbooks Download The PowerPivot

In Excel 2010 you can download the PowerPivot Add-in free from Microsoft and use PowerPivot to create PivotTable reports based on multiple tables.

PowerPivot is extremely powerful and if you use data frequently I highly recommend you investigate it further.

PowerPivot is also available with some versions of Excel 2013.

Excel 2013s Data Model allows you to create relationships between tables and lets you report on multiple tables with a PivotTable.

Previously, PivotTables could only report on a single table.

It is not necessary for the fields (columns) to have the same name.

The Data Model feature lets you create a basic relational database structure within Excel.

Both might contain a State column, but in both tables those fields will contain duplicates, so you cant use the State field to create a relationship.

This means you could create a PivotTable report based on data from both tables.

Relationships provide a way to extract data from multiple tables to complete your report.

Using the Order ID to create a relationship between the Sales and Order tables lets you extract the name from the Order table and use it in your Sales report.

We want to report on regional sales where each state or territory is allocated to a region.

We have a separate table which lists the states and territories and their respective regions see Figure 1.

I highly recommend using the Format as Table feature as it tells Excel to treat the table as a database table.

The companion video includes more details about Format As Table.

The bottom dialog in Figure 4 has the selections necessary to create the relationship between the two tables.

This example shows that the names of the columns can be different.

One of the columns must contain unique entries, if not, an error message will be displayed.

Now you can create a PivotTable report based on the two tables.

In the Existing Connections dialog, click the Tables tab and then the Tables in Workbook Data Model option, then click the Open button and OK see Figure 6.

The PivotTable Fields list has both tables listed see Figure 7.

Clicking the icon to the left of the table name will display all the fields (columns) from the table.

Excel For Mac Connection Between Two Excel Workbooks