Suppose you have two tables of data in Excel that are related. As an example, you might have sales Orders and Products. You might have Customers and their Orders. You want to create a pivot table but you also want the two tables connected together. First we need to connect them together with the relationship.

In both examples mentioned, we have a one-to-many relationship where One Customer can have many Orders and one Order can include many Products.

Here are our two tables, which I have named tblOrders and tblProducts. tblOrders is in a sheet called Orders and tblProducts is in a sheet called Products. These tables are purposefully simple and small.

To Add a new relationship, go to Excel Data, Data Tools, Relationships and click the Relationships icon and click Add in the dialog box.

You need to select the first table and a column, and the second table and the related column.

It will seem that nothing happened after you create your relationship, but something did happen. Click on the Power Pivot Window green icon in the Data Tools group of the Data tab (menu). That icon is shown on the left of this text. New to Power Pivot? You can have a look at a YouTube introductory video on Power Pivot called How to use Power Pivot in Excel | Full Tutorial. This is a good business example because Kevin Stratvert uses three related tables: Customers, Orders, and Cookie Types (products).

Below is a screenshot of the relationship we created, using the Diagram View. The important part is linking the two tables. They will be linked to a particular column in each table. The column names do not have to be the same in a linked pair, but the data types and the data’s meaning do need to be the same. In our example, we have a one-to-many relationship where the Products table is the “one” side, and the Orders is the “many” side. We will show all of the Orders and only those Products that match.

In the Excel workbook, click the Insert tab. Click the down arrow under Pivot Table (on the left). Click From Data Model.

Below is our pivot table and pivot chart.

You can learn more about this in the YouTube video called Create a Pivot Table from Multiple Sheets in Excel | Comprehensive Tutorial! by Leila Gharani. Go to time 6:45.

In SQL (Structured Query Language), this would represent a LEFT JOIN or in some databases, it is called a LEFT OUTER JOIN. Have a look at an example called SQL LEFT JOIN Example at w3schools.com where they take the example of Customers and their orders. A customer may have zero, one, or many Orders. We have a one-to-many relationship between Customers and Orders.