Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Combining multiple tables in a PivotTable

0040 Combine tables in PivotTable Featured Image

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 example file for this post.  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.

Download Icon
Download the file: 0040 Combining multiple tables in a PivotTable.zip

Watch the video


Watch the video on YouTube.

The scenario

In our example file, we have three sections of data:

  • Sales data
  • Sales rep data
  • Product data

Sections of data for Pivot Table

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!

PivotTable with relationshiops

Create tables

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).

Insert - Table from Ribbon

The Create Table dialog box opens.  Check the range encompasses all the data, and ensure my data has headers is ticked.  Then click OK

Create table dialog box

The data will change to a striped format.  This is a visual indicator that an Excel table has been created.

Data converted to Table

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.

Select Table Format

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).

Table Design - Name Table

Repeat the steps above for the other datasets to create tables called SalesRepData and ProductData.

Creating relationships

With our three tables created, it’s now time to start creating the relationships.  Click Data > Relationships.

Data - Relationships

The Manage Relationships dialog box opens.  Click New.

Manage Relationships - New

Now the Create Relationship dialog box opens.  This is where we define the relationships that exist.

Create Relationship Window

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

Create Relationship - Populated for Example

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

Create Relationship for 2nd Table

Once all the relationships are created, click Close.

Relationships 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.

Create PivotTables

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.

Click OK.

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.

PivotTable Fields

This creates the following PivotTable:

PivotTable with relationshiops

There you have it.  We’ve created a PivotTable from multiple tables without any formulas 🙂



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:

Duplicate values when creating relationships

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.

Error message when duplicating values

We must go back and fix the data in the lookup table and then refresh again.

Power Pivot

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.

PowerPivot - Data Model

Conclusion

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.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Don’t forget:

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:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. 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.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

6 thoughts on “Combining multiple tables in a PivotTable

    • Excel Off The Grid says:

      Thank you. The introduction of Excel 2013 really was a big step forward (though I’m not sure we realized at the time).

  1. Av says:

    Hi. When I try to create the Pivot table, I don’t have the option for “Use this workbook’s Data Model “.
    Why would that be? (I am using Excel 2013).

    Thank you!

    • Excel Off The Grid says:

      The Data Model functionality was first introduced in Excel 2013, so it should be there in one form or another. But may go by different names then. I don’t have a copy of Excel 2013 any longer to check. Try looking for some older blog posts or YouTube videos to find the equivalent options. But the principles should be the same.

  2. Dave says:

    Thanks very much for the article – one of the more clear presentations of this concept.
    Is it possible to combine multiple “facts” tables? I have been keeping transaction data in a table-per-sheet per account. All the table headings are identical, but the accounts are on separate tabs (sheets).

Leave a Reply

Your email address will not be published.