The first thing youвЂ™ll notice in the PivotTable Fields pane is a list of table names instead of a list of field names. The data model contains pointers to the three tables and defines the relationships between those tables. WhatвЂ™s a data model? The answer is that by creating relationships, you unknowingly created a data model that lives in the workbook. When you start from a blank cell after defining relationships, the Create PivotTable dialog will default to вЂњUse This WorkbookвЂ™s Data Model.вЂќ This sentence always seems cryptic to me. But for our purposes, you need to insert a blank worksheet in your workbook or simply start from a blank cell on Sheet1 and go to Insert, PivotTable. This next step is counterintuitive because most people start a pivot table by selecting the data that they want to appear in the pivot table. This one should specify that the Data table has a CustID column thatвЂ™s related to the Account Number column in the Customers table.ДЄfter creating both relationships, theyвЂ™ll be listed in the Manage Relationships dialog. ItвЂ™s related to the Products table using the column called Product.Д¬lick NewвЂ¦ again and define a second relationship. In the Create Relationship dialog, specify the Data table has a column called ProdID. On the right side of the Manage Relationships dialog, click NewвЂ¦ to create the first relationship. If youвЂ™re using Excel 2016 or newer, youвЂ™ll see a Relationships icon in the Data Tools section of the Data tab of the ribbon.Д¬lick the Relationships icon to open the Manage Relationships dialog. For this example, call the three data sets вЂњData,вЂќ вЂњProducts,вЂќ and вЂњCustomers.вЂќ A Table Design tab appears in the ribbon, and the Table Name can be edited in a box on the left side of the ribbon. You can easily change the name of each table before you build the relationships: Select a cell in the table. By default, these three tables will be called Table1, Table2, and Table3. Excel will ask you to verify that your data has a header row. On each of the three worksheets, select the individual data set and press Ctrl+T.

Behind the scenes, it will make a data set eligible for use in the Relationships dialog. But those words, вЂњFormat as Table,вЂќ undersell how much happens when you make a worksheet into a table. The Format as Table icon on the Home tab (or Ctrl+T) sounds like itвЂ™s made for quickly formatting a worksheet. Say that you have a large invoice register on Sheet1 with fields like вЂњProduct IDвЂќ and вЂњCustomer Number.вЂќ If the data on Sheet2 is a product database and the data on Sheet3 is a customer list, then you can easily build a pivot table from data from all three worksheets without doing a bunch of VLOOKUP formulas to get the data back onto Sheet1. It was built into Excel 2013, but the relationship-building tools that help make it easy to do first arrived in Excel 2016. The ability to link data from two worksheets debuted as an add-in in Excel 2010.

Excel 2013 table tool design tab