I’ve selected the Existing Worksheet in cell Sheet1, Cell G10, but you can put your Pivot Table wherever you like.Ĭlick OK to close the Create PivotTable dialog box. Select a location to create the PivotTable. For this example, we will make the PivotTable on the same worksheet as the data. The Create PivotTable window opens. The most important thing is the Use this workbook’s Data Model option is selected. Create the PivotTableĮverything is in place, so we are now ready to create the PivotTable.Ĭlick Insert > PivotTable from the ribbon. Once all the relationships are created, click Close. Next, let’s create the relationship between the SalesData and ProductData tables using the same process as above. That’s it, simple right! We have just combined two tables without any formulas.
The columns do not need to share a common header name for this technique to work. However, it can be helpful to remember how the tables are related. Related Column (Primary): This is the column we want to pair with the Column (Foreign) we selected above. If this were a VLOOKUP, it would be the first column in the table_array argument. The word Primary is database terminology again it tells us the column must contain unique values.Related table: This is the table containing the categories we want to analyze the transactional data by (the lookup table).Column (Foreign): This is the name of the column from the transactional values table which we want to lookup from. If you’re in the VLOOKUP mindset, then this would be the column containing the lookup_value argument. The word Foreign is database terminology to indicate that this column can have duplicate values.Table: This is the table containing the transactional values that we want to analyze (the fact table).I don’t think the descriptions in this dialog box are particularly clear, so I’ll try to give you a bit of a steer. The Create Relationship dialog box opens. This is where we define the relationships that exist. The Manage Relationships dialog box opens. Click New. With our three tables created, it’s now time to start creating the relationships. Click Data > Relationships. Repeat the steps above for the other datasets to create tables called SalesRepData and ProductData. With any cell in the table selected, click Table Design and enter a new name into the Table Name box. I’ve chosen SalesData (spaces and most special characters are not permitted within table names). Next, we need to give our Table a meaningful name. Click any cell in the table, then click Table Design and choose another format from those available. TOP TIP: You don’t need to stick with that format. The data changes to a striped format. This is a visual indicator that an Excel table has been created. The Create Table dialog box opens. Check the range includes all the data, and ensure my data has headers is ticked. Then click OK
Select any cell within the first block of data and click Insert > Table (or press Ctrl + T). We will not use a single formula! Create tablesįirst, we need to turn our data into Excel tables. This puts our data into a container so Excel knows it’s in a structured format that can be used to create relationships. To achieve this, we will create relationships to combine PivotTables. This requires information from all three data tables: Our goal is to create a PivotTable showing product sales by branch.
The sales data contains the transaction information, which is often referred to as a fact table. The sales rep data and product data include the categorization to analyze the transactions these are often referred to as lookup tables. These data sets could be on separate worksheets, but for ease of demonstration, they are included on one. In our example file, we have three sections of data: Refresh a PivotTable from Multiple Tables.
0 kommentar(er)
