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. After that, the data is ready, we can create a PivotTable, and the analysis can start. But we don’t need to do all those VLOOKUPs anymore. Instead, we can build relationships that combine multiple tables and automatically create the lookups for us.
We don’t need to copy and paste data into another worksheet either as we can now use Power Query to import the data. Check out my Power Query series to understand how to do this. For this post, we are focusing on creating relationships.
The ability to create relationships has been around since Excel 2013, yet many users don’t even know this feature exists.
Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples. This is the best way to learn. You’ll be able to see the solutions in action, plus the file will be useful for future reference. The support files are available for FREE to newsletter subscribers.
Click below to subscribe and gain access to the subscriber area. You will also receive:
- My favorite tips and tricks direct to your inbox
- Exclusive content (which is only available to subscribers)
- FREE tools and downloads
If you’re already a subscriber, click here to log-in to the subscriber downloads area.
The filename for this post is 0040 Combining tables in a PivotTable.zip
In our example file, we have three sections of data:
- Sales data
- Sales rep data
- Product data
These data sets could be on separate worksheets, but for ease of demonstration, I have included them on one.
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 by; these are often referred to as lookup tables.
Our goal is to create a PivotTable showing product sales by branch; this requires information from all three data tables. To achieve this, we are going to create relationships and will not use a single formula!
First, we need to turn our data into Excel tables. 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.
Select on any cell in the first block of data and click Insert > Table (or press Ctrl + T).
The Create Table dialog box opens. Check the range encompasses all the data, and ensure my data has headers is ticked. Then click OK
The data will change to a striped format. This is a visual indicator that an Excel table has been created.
You don’t need to stick with that format. Click any cell in the table, then click Table Design and choose another format from those available.
Next, with any cell in the table selected, click Table Design > Table Name and give the table a meaningful name. I’ve chosen SalesData (spaces and most special characters are not permitted within table names).
Repeat the steps above for the other datasets to create tables called SalesRepData and ProductData.
With our three tables created, it’s now time to start creating the relationships. Click Data > Relationships.
The Manage Relationships dialog box opens. Click New.
Now the Create Relationship dialog box opens. This is where we define the relationships that exist.
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.
- Table: This is the table containing the transactional values that we want to analyze (the fact 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.
- Related table: This is the table containing the categories we want to analyze the transactional data by (the lookup table).
- 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 that the column must contain unique values.
The columns do not need to share a common header for this technique to work. However, it can be helpful to remember how the tables are related.
In our example, we use the following:
- Table: SalesData
- Column (Foreign): Sales Rep ID
- Related Table: SalesRepData
- Related Column (Primary): Sales Rep ID
Click OK to create the relationship.
Now let’s create the relationship between the SalesData and ProductData tables using the same process as above.
- Table: SalesData
- Column (Foreign): Product ID
- Related Table: ProductData
- Related Column (Primary): Product ID
Once all the relationships are created, click Close.
Create the PivotTable
Everything is in place, so we’re now ready to create our PivotTable.
Click Insert > PivotTable from the ribbon.
The Create PivotTable window opens. The most important thing is that the Use this workbook’s Data Model option is selected.
Select a location where the PivotTable should be created. For this example, we will make the PivotTable on the same worksheet as the data. I’ve selected the Existing Worksheet in cell G10, but you can put your Pivot Table wherever you like.
The PivotTable is created. Notice that the PivotTable Fields window includes all three tables. We can now start dragging fields from each table to form a single view.
As I said earlier, the goal is to show product sales by branch. To achieve this, put the fields into the following sections:
- Columns: SalesRepData > Branch
- Rows: ProductData > Product
- Values: SalesData > Sum of Value
If you don’t see all the tables in the PivotTable Fields view, then change the selection from Active to All.
This creates the following PivotTable:
There you have it. We’ve created a PivotTable from multiple tables without any formulas 🙂
Download the 100 Excel Macros ebook
- Contains 100 Excel VBA macros
- Learn VBA by following along with the example codes
- Apply to your macros, automate Excel, save time.
Download the ebook today!
Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.
Duplicate values in lookup tables
If we have duplicate values in our lookup tables (or related tables as they are called in the Relationships window), the relationships won’t work. When we use VLOOKUP, Excel always returns the first item it finds, even if it’s not the answer we want). But when we use relationships, if there is more than one, Excel doesn’t know which item to use; therefore, to ensure the integrity of our data, we must have unique values in our lookup tables.
Creating a relationship with duplicate values
When creating the relationship, if we have duplicate values in our lookup table, we will get the following error:
We must go back and fix our source table before we create the relationship.
Adding duplicate values into a table?
If we accidentally add duplicate values into our lookup tables, we will get an error when refreshing the PivotTable.
We must go back and fix the data in the lookup table and then refresh again.
You probably didn’t realize, but you’ve just used the Power Pivot engine in Excel. We’ve not opened or installed the Power Pivot add-in; all we’ve used is the standard Excel interface. However, if we were to open the Power Pivot tool, we would see that our data and relationships exist in there.
In this post, we’ve created a PivotTable from separate tables without formulas, something which was not possible before Excel 2013.
If you understand how these relationships work, maybe it’s time to investigate Power Pivot a bit further. Then you can gain even more automation benefits and create even more advanced reports.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: