Join multiple tables in a PivotTable

★ Want to automate Excel? Check out our training academy ★

★ Download the example file:★

★ About this video ★
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.

In this video, I will show you how :-)

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel #PivotTables #PowerPivot

[dennishackett]

Great easy to follow and understand Are their any major additional features over the 3 years this was made for relationships? If so, would you be willing to do a part two that shows the newest features added to relationships?

[mariamelaniereyes]

Thank you Mark, your video uploads are gifts of knowledge.

[wayneedmondson]

Hi Mark. Great tutorial! My first pass at it was to load the three tables into Power Query and then merge twice to a new combined query using Left Outer joins on Sales Data with SalesRepID and ProductID.. then close and load to a Pivot Table Report. Now I see this was much more work than just setting up the relationships through the relationship manager or directly with Power Pivot and Diagram View. Thanks for the great tips and inspiration to learn and practice a new method :)) Thumbs up!!

[tinkytinkz]

Thanks Mark! First time I've learned primary/foreign

[sallyho]

I got a message that says duplicate values. I didn’t know how to fix it. Can you explain more on the meaning of duplicate values.

[jeffedelstein]

is there a workaround for a mac? Powerpivot oand data models are not available on mac.

[nsanch]

Thank you Mark for the great video. That is neat relationships can be made without even going into Power Pivot. I wonder if there is any faster way to get the 3 tables into the model; Or do you have to go through the steps of making tables then forming relationships. Not that it's too much work.

[godsonsoans]

Hello Mark, I have one question.
I have created 2 tables on the Excel sheet (I have labelled them as week 1 & week 2 table respectively). both the tables have same headings but the data inside the table is different. I'm trying to filter or use a Pivot table where i can filter one table at a time. For eg: If i have created 10 Tables in a worksheet all with the same headings which would be labelled Week 1, week 2, week and so on till 10. how can i filter to show only one of the tables on the excel sheet ? Please help.

[lilyiann]

Hi! Thanks for a great video! Is it possible to do a many-to-many join using this method? For example, one table is sales of pastries (columns: date, item, quantity). The second table is list of ingredients (columns: item, ingredient). I'd like to left outer join these two tables on item, to get a view of how many ingredients are used based on sales

[mirrrvelll]

Good but not every version of Excel has Power Pivot...but definitely great thing.