Make Pivot Tables from more than one source data table with this simple trick

#datamodel #Excelrelationships #twotablesonepivot
We can all create pivot reports from single tables or ranges of data. What if you need to create a pivot from data that is in two or more tables? You can use "Data Model" feature of Excel to connect multiple tables and create pivots from them. This technique opens up doors for advanced data analysis with ease.
For example workbook and resources visit:
chandoo.org/wp/advanced-pivot...

[@antoniodefalco6179]

You're an excellent teacher, your videos are so clear and usefull. Thank you for sharing your knowledge here on the Tube!

[@chandoo_]

You're very welcome!

[@prosenjitbappi1207]

@@chandoo_ You're an excellent teacher, your videos are so clear and usefull. Thank you for sharing your knowledge here on the Tube!

[@mariafriend644]

I’ve been looking for this, yours is the best simple explanation. Thank you.

[@technuts]

Super! All the tricks with respect to PIVOT tables in one place. Thank you Chandoo!

[@wayneedmondson1065]

Great tip Chandoo! Thanks for sharing :)) Thumbs up!!

[@TheDsgrant79]

Game changer! I never knew what the Data model could do. Thanks

[@chandoo_]

IT IS!!!

[@paulmurali6843]

Love your Videos brother. May God bless you. I pray for you and your family.

[@GZbautista]

Wow! I love this chandoo. this is exactly what I am looking for..

[@juliusbiliran]

Really really great video! Super helpful! Thanks!!!!!❤

[@danteencinas7020]

Thank you Chandoo!

[@AdeKaryadi]

nice tutorial. good job. thanks

[@alyan9247]

The excellent way of teaching ❤️ From Pakistan

[@bunkoti]

Great video

[@chrise4196]

Very clear, thank you

[@chandoo_]

You are welcome my friend.

[@mohammadalbizri2013]

Thanksful, how it's nice if you linked the worksheet for implementation !

[@dilipsuman2085]

You are awesome 👌

[@bskrmusic3932]

Thanks a lot Anna , Great Job.

[@chandoo_]

So nice of you

[@ashfaqhussain5328]

Thanks. Very helpful. Can you make a video on accounts payable balance? one table contains invoice amount, second table has payments. Determine the balance payable using this technique. Thanks

[@GATORAID1993]

brilliant

[@jude6387]

This is great thank you. Is there a video to show how I keep the same pivots but add new data each month?

[@chandoo_]

If you update your source data and click on refresh from data ribbon, the pivots will automatically update.

[@jude6387]

@@chandoo_ That will save so much time. I am thinking I can also add further tables with relationships as well. I appreciate your reply. Many thanks Chandoo and love the videos.

[@jude6387]

Ps is there a place where I could ask another excel question that I have a problem with?

[@anv.4614]

thanks

[@owolabiquadri6154]

Hello chandoo. Love your videos. It has been my go to videos. "Use this workbook model doesn't appear in mine when I'm creating another pivot table after having linked the tables in the first pivot. What could be wrong?

[@chandoo_]

Thanks for the love Owolabi. The wording changed a bit in Excel upgrades. After making the relationships, try this: 1) Select a blank cell where you want the pivot. 2) Go to Insert > Pivot Table > From Data Model This should show the pivot layout with your tables (switch to all tables to see everything)

[@ibrarahmad795]

Hi Sir, Silly question, if you have old version, how it will work?

[@surabhis645]

Thankyou sir. ..for the help One problem, when I tried to create a relation between the table, error occured i.e Both selected columns contain duplicate values. At least one of the columns selected must contain only unique values to create a relationship between the tables. Can you help here

[@chandoo_]

Hi Surabhi... You need at least one table with unique values to make relationships.

[@surabhis645]

@@chandoo_ thankyou But in your case, u r able to build relation between customer ID In my data, there are ids in two data table but no success.

[@chandoo_]

Yes, but the customer table had only one row per ID. Make sure one of the tables has unique IDs.

[@abdikanimohamed4297]

I have made an attempt to create something but I'm experiencing difficulty, as the slicer is not functioning as intended with my tables. I have carefully inspected the data model and the established relationship, but I have not been able to resolve the issue.

[@jigmy27]

Is this possible in excel 2019 as well? When I try to follow the steps and insert Pivot Table, I do not find the Data Model option.

[@chandoo_]

It should be. Excel 2019 has the data model option. See this page to learn how to trigger it. chandoo.org/wp/introduction-to-excel-2013-data-model-relationships/

[@mistazed1170]

I have a question. What if you use vlookup to bring back text fields

[@chandoo_]

You can do it. But we are talking about "avoiding" lookups altogether.

[@shwetapali9620]

When i am adding another table in the same sheet it is not taking relationships which we have made earlier between the sheets.

[@dqfashionfactory6470]

I'm getting error but as you said I did same step but the error is you have selected cell contained duplicate value

[@RaushanKumar-rh3sn]

I have an issue when I create a relationship b/w two columns then the ok button not highlighted why

[@stilgottheblues]

I think there was an update on Excel. I did not have to connect. It was done by excel automatically.

[@vikramjeetsaraon3066]

Hey Chandoo, I am making pivot table report from using an external link of azure database and it’s not letting me select “add to data model” hence I cannot combine two any other tables with it. What to do in this case….??

[@chandoo_]

hmm.. Are you using the Power Query to load this Azure data? If so, you can use the load behaviour options of PQ to send data to data model. Whether a table is in the model or not, it will be available for "adding relationships".

[@vikramjeetsaraon3066]

@@chandoo_ thanks for the reply. By going through PQ. After creating the connection it’s asking me for “table import wizard” to write a MDX statement for a table, but I need access to all the table.😕

[@chandoo_]

Just use the MDX for full table. I haven't written MDX in a LOOOONG while, so can't help with that.

[@mdtanweer4649]

When I am creating second pivot table , "Use this workbook's data model " option is not available .

[@kappatita4559]

I'm having this same problem and can't figure out why the check box is not showing up :(

[@saahil0203]

I cant see table design option in my excel to change name which will show up in Pivot table range and because of that i couldnt see data from all 3 files

[@chandoo_]

Hmm.. the design tab only activates when you select any cell inside the table.

[@saahil0203]

@@chandoo_ you're too good thank you.

[@saahil0203]

One more help, if I have multiple lookup values which formula I should use because Vlookup or Xlookup won't work if I need multiple result. Please suggest.

[@novitargg7200]

in my excel i dont have option to check list 'add this data to the data model' what will i do? fyi i use windows 10

[@chandoo_]

Hi Novita... This option is available in Professional version of Excel 365 / 2019 / 2016 / 2013. Check your version of Excel and if possible upgrade.

[@novitargg7200]

@@chandoo_ Thankyou sir, your video is very usefull and clear for me👍

[@kaifahmed316]

Sir I didn't get the excel file

[@rameshkunda6775]

I am not getting the Option 'Table Name' how do i get , Could you please help me? ....... 3:29🤔

[@shoaibrehman9988]

Hi Sir, No doubt your's videos very informative. But sometimes you are so quick to perform next steps even some steps are very important or key of your video. I know you assume we already knows basic. But here in this video there are few more things for pivot relationships i would like to say your's steps very quick. Yes bc of your experience. But we are audiences. In nutshell you are awesome but you need to adopt moderate speed or pauses during important steps. I hope you take it positive. Thanks we need more video.

[@chandoo_]

Hi Ali... Thanks for your suggestions. This video is part of "Advanced Pivot Table tricks", so there is no point in going thru basics in this one. Please watch the Pivot Table beginner guide to get the concepts on relationships first. chandoo.org/wp/excel-pivot-tables-tutorial/

[@prakashgothankar4792]

This is insane

[@Loralanthalas]

It sucks the slice function disappears when you do this.

[@chandoo_]

What are you talking about? You can still slice the data with a slicer.

[@krishnaprasad8311]

Can u teach in Hindi

[@weslerseni3923]

Bad audio

[@koushikmaity3954]

Not understandable because of fast explanations. Request you to go slow for better understanding.

[@chandoo_]

Noted. you can also adjust video speed in KZbin. That might help.