The power of the data model happens here. Click that icon to reveal the fields available in the table. Each table has a greater than sign (>) to the left of the table name.
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.
0 kommentar(er)
