Make Pivot Tables from more than one source data table with this simple trick
ฝัง
- เผยแพร่เมื่อ 1 ต.ค. 2024
- #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...
You're an excellent teacher, your videos are so clear and usefull. Thank you for sharing your knowledge here on the Tube!
You're very welcome!
@@chandoo_ You're an excellent teacher, your videos are so clear and usefull. Thank you for sharing your knowledge here on the Tube!
I think there was an update on Excel. I did not have to connect. It was done by excel automatically.
Great tip Chandoo! Thanks for sharing :)) Thumbs up!!
I am not getting the Option 'Table Name' how do i get , Could you please help me? ....... 3:29🤔
I’ve been looking for this, yours is the best simple explanation. Thank you.
I have an issue when I create a relationship b/w two columns then the ok button not highlighted why
When i am adding another table in the same sheet it is not taking relationships which we have made earlier between the sheets.
Sir I didn't get the excel file
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
Super! All the tricks with respect to PIVOT tables in one place. Thank you Chandoo!
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.
You are awesome 👌
The excellent way of teaching ❤️ From Pakistan
I'm getting error but as you said I did same step but the error is you have selected cell contained duplicate value
nice tutorial. good job. thanks
It sucks the slice function disappears when you do this.
What are you talking about? You can still slice the data with a slicer.
Love your Videos brother. May God bless you. I pray for you and your family.
Hi Sir,
Silly question, if you have old version, how it will work?
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
Hi Surabhi... You need at least one table with unique values to make relationships.
@@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.
Yes, but the customer table had only one row per ID. Make sure one of the tables has unique IDs.
Really really great video! Super helpful! Thanks!!!!!❤
Thanksful, how it's nice if you linked the worksheet for implementation !
This is insane
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?
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)
Wow! I love this chandoo. this is exactly what I am looking for..
thanks
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
Hi Novita... This option is available in Professional version of Excel 365 / 2019 / 2016 / 2013. Check your version of Excel and if possible upgrade.
@@chandoo_ Thankyou sir, your video is very usefull and clear for me👍
When I am creating second pivot table , "Use this workbook's data model " option is not available .
I'm having this same problem and can't figure out why the check box is not showing up :(
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.
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/
I have a question. What if you use vlookup to bring back text fields
You can do it. But we are talking about "avoiding" lookups altogether.
This is great thank you. Is there a video to show how I keep the same pivots but add new data each month?
If you update your source data and click on refresh from data ribbon, the pivots will automatically update.
@@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.
Ps is there a place where I could ask another excel question that I have a problem with?
Can u teach in Hindi
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….??
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".
@@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.😕
Just use the MDX for full table. I haven't written MDX in a LOOOONG while, so can't help with that.
brilliant
Thank you Chandoo!
Great video
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
Hmm.. the design tab only activates when you select any cell inside the table.
@@chandoo_ you're too good thank you.
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.
Game changer! I never knew what the Data model could do. Thanks
IT IS!!!
Thanks a lot Anna , Great Job.
So nice of you
Very clear, thank you
You are welcome my friend.
Not understandable because of fast explanations.
Request you to go slow for better understanding.
Noted. you can also adjust video speed in TH-cam. That might help.
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.
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/
Bad audio