- Creating pivot tables in excel 2013 how to#
- Creating pivot tables in excel 2013 plus#
- Creating pivot tables in excel 2013 download#
If you don’t see all the tables in the PivotTable Fields view, then change the selection from Active to All. To achieve this, put the fields into the following sections: We can now start dragging fields from each table to form a single view.Īs I said earlier, the goal is to show product sales by branch. Notice that the PivotTable Fields window includes all three tables. I’ve selected the Existing Worksheet in cell G10, but you can put your Pivot Table wherever you like. For this example, we will make the PivotTable on the same worksheet as the data. Select a location where the PivotTable should be created. The most important thing is that the Use this workbook’s Data Model option is selected. Once all the relationships are created, click Close.Įverything is in place, so we’re now ready to create our PivotTable.Ĭlick Insert > PivotTable from the ribbon. Now let’s create the relationship between the SalesData and ProductData tables using the same process as above. However, it can be helpful to remember how the tables are related.
![creating pivot tables in excel 2013 creating pivot tables in excel 2013](https://cdn4syt-solveyourtech.netdna-ssl.com/wp-content/uploads/2013/03/excel-2013-pivot-table-5.jpg)
The columns do not need to share a common header for this technique to work. The word Primary is database terminology again it tells us that the column must contain unique values. If this were a VLOOKUP, it would be the first column in the table_array argument. Related Column (Primary): This is the column we want to pair with the Column (Foreign) we selected above.Related table: This is the table containing the categories we want to analyze the transactional data by (the lookup table).The word Foreign is database terminology to indicate that this column can have duplicate values. If you’re in the VLOOKUP mindset, then this would be the column containing the lookup_value argument. Column (Foreign): This is the name of the column from the transactional values table which we want to lookup from.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. This is where we define the relationships that exist. Now the Create Relationship dialog box opens. The Manage Relationships dialog box opens. With our three tables created, it’s now time to start creating the relationships.
![creating pivot tables in excel 2013 creating pivot tables in excel 2013](https://www.dummies.com/wp-content/uploads/353155.image1.jpg)
Repeat the steps above for the other datasets to create tables called SalesRepData and ProductData. I’ve chosen SalesData (spaces and most special characters are not permitted within table names). Next, with any cell in the table selected, click Table Design > Table Name and give the table a meaningful name. Click any cell in the table, then click Table Design and choose another format from those available. You don’t need to stick with that format. This is a visual indicator that an Excel table has been created. The data will change to a striped format. Check the range encompasses all the data, and ensure my data has headers is ticked. Select on any cell in the first block of data and click Insert > Table (or press Ctrl + T). This puts our data into a container so that Excel knows that it’s in a structured format that can be used to create relationships. To achieve this, we are going to create relationships and will not use a single formula!įirst, we need to turn our data into Excel tables. Our goal is to create a PivotTable showing product sales by branch this requires information from all three data tables. The sales rep data and product data include the categorization to analyze the transactions by these are often referred to as lookup tables. The sales data contains the transaction information, which is often referred to as a fact table. These data sets could be on separate worksheets, but for ease of demonstration, I have included them on one. In our example file, we have three sections of data:
Creating pivot tables in excel 2013 plus#
Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.ĭownload the file: 0040 Combining multiple tables in a PivotTable.zip
Creating pivot tables in excel 2013 download#
I recommend you download the example file for this post.
![creating pivot tables in excel 2013 creating pivot tables in excel 2013](https://i.ytimg.com/vi/l0a0dCgFA5g/maxresdefault.jpg)
![creating pivot tables in excel 2013 creating pivot tables in excel 2013](https://www.journalofaccountancy.com/content/jofa-home/issues/2020/aug/smart-excel-functions-for-productivity/_jcr_content/contentSectionArticlePage/article/articleparsys/image.img.jpg)
The ability to create relationships has been around since Excel 2013, yet many users don’t even know this feature exists. For this post, we are focusing on creating relationships.
Creating pivot tables in excel 2013 how to#
Check out my Power Query series to understand how to do this. We don’t need to copy and paste data into another worksheet either as we can now use Power Query to import the data. Instead, we can build relationships that combine multiple tables and automatically create the lookups for us. But we don’t need to do all those VLOOKUPs anymore. After that, the data is ready, we can create a PivotTable, and the analysis can start. When most people use PivotTables, they copy the source data into a worksheet, then carry out a lot of VLOOKUPs to get the categorization columns into the data set.