Anyone who works with Excel is familiar with the situation where you have various tables and want to use the data out of all of them. Most Excel users think that a PivotTable is always based on a single table. But in Excel it is really easy to create a PivotTable using data from more than one table. In this blog post I will describe the various steps you need to take.
A PivotTable based on two tables: a practical example
You will always need at least two tables, which is why we will use two in this example:
- Customers (with surname, first name, gender and postcode) (There are 82 customers)
- Municipalities (with the name of the municipality and the postcode) (I downloaded these from the De Post website: http://www.bpost.be/site/nl/verzenden/adressering/zoek-een-postcode
We want to know the number of residents per gender in each municipality.
Normally most people would add an extra column, ‘Municipality’, to the customers table so they can then use the VLOOKUP function to find the data in the other table. At first glance you might say that is not so much work, but imagine that you want to add not 1 column to the customers table but 10, each with a VLOOKUP. Not only does that take a lot of time, but it also impairs the performance of Excel.
Furthermore, in our example, you don’t even need to use a VLOOKUP function because Excel will always search in the 1st column of the municipalities table. In our example, the first column contains the municipalities instead of the postcodes. This means you would also need to change the position of all these columns.
There is a much simpler way:
- Click in the postcodes table.
- Create a PivotTable via Insert – PivotTable
- In the pop-up check the option Add this data to the Data Model and click OK.
- In the PivotTable we drag the Code field to the Rows
- On the right-hand side, you will see the PivotTable Field list. At the moment, only the table used in the PivotTable (Municipalities) is shown, but at the top you have an option All which allows you to display all the tables in the workbook. Click All and drag the Surname field from the Customers table to Values.
- As a result, you will see the same number of people for every municipality. This is normal. After all, Excel does not know how the customers table can be linked to the postcodes table. You will also see this from the error message in the Fields List.
- You can try clicking on Auto Detect…. Sometimes Excel will find the link itself. (For instance, if both tables have a column with the same name, which is not the case in our example; we have postcodes in customers and code in municipalities) You will see this message. Close this window by clicking Close.
-
Instead of clicking Auto Detect… we are going to use the other option: CREATE…
You can compare this screen with what you do in the VLOOKUP function. Read the screenshot above as: “In the Customers table, find the value in the Postcode column in the Code field of the Municipalities table“. Since you can choose the columns yourself, it makes no difference whether the Code field is the first or second column in the table. Click OK to confirm.The PivotTable is updated and you will see the result you want.
- If you drag the Gender field from the Customers table to the Columns section, we will get the PivotTable we initially wanted, without using functions.Excel PivotTables
The great thing about this is that there is no need to limit yourself to just two tables. As long as the relationships are correct, you can use multiple tables in a single PivotTable.
Power Pivot: the power behind Excel
For your information: The reason you can build a PivotTable based on multiple tables is because Excel builds a data model in the background. If you want to view this data model, you will need to enable the Power Pivot add-in. This not only allows you to create a PivotTable based on multiple tables, but you can also define custom sort orders, overrule totals, create calculations that are not possible in an ordinary PivotTable, continue calculations using the result of a PivotTable calculation etc. Power Pivot is really powerful, but not simple.
Would you like to get more from your PivotTables? Then sign up for one of these training courses:
