PowerPivot is used to for connecting and manipulating data, particularly from external sources. It allows you to connect tables of data that have common fields (just like a relational database) and is just one of the features you will learn about in our Excel Advanced Data Analysis course.
To create related tables in PowerPivot:
- Create a relationship between each table through the Design tab > Create Relationship or from the Home tab select Diagram View. The tables now display as boxes on the screen, displaying fields.
- Identify the fields that are common between two tables. e.g. Freight Code.
- Drag and drop the Freight Code on the Freight Company field in the Orders table. These two fields contain the same data.
- Repeat this step to connect the other tables to orders, dragging and dropping the common field.
- Now you can create PivotTables and PivotCharts from the data by selecting Home tab > PivotTable.