Advanced Pivot Table Techniques: Combine Data from Multiple Sheets in Excel
Vložit
- čas přidán 28. 06. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Struggling to create PivotTables using data from different tables? This video is your solution! We dive into a practical, easy-to-understand guide on using Excel's Data Model for efficient reporting.
⬇️ Download the workbook here: pages.xelplus.com/merge-multi...
Creating a Pivot Table in Excel based on multiple tables or multiple sheets can be tricky. Often Lookup functions like VLOOKUP are used to consolidate the information in a new table. This can significantly increase the size of the spreadsheet and make it slow.
Key Highlights:
▪️ Crafting a PivotTable with Customer Sales & Quantity Sold by Article
▪️ Using Excel's Data Model to Simplify Data Merging
▪️ Easy Steps to Create Relationships between Tables
▪️ Creating Visually Clear and Informative Reports
▪️ Tips on Hiding Unnecessary Data Fields for Clarity
In this video I'll show you a better alternative to combine data in a pivot table. We will keep the source tables as they are but we'll build a report based on relationships in PowerPivot. Based on this data model in Excel we can setup PivotTables and access the information from all tables.
00:00 Create a PivotTable with from Multiple Tables
01:47 Using the Excel Data Model to Merge Data from Different Tables
02:47 Create Relationships between Different Excel Tables
05:10 Diagram View in Excel PowerPivot
06:00 Create a Pivot Table from the Excel Data Model
09:56 Join Leila's Ultimate Power Query Course
Complete Power Query Playlist: • Excel Power Query (Get...
Power Query complete course: www.xelplus.com/course/excel-...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
Grab the file I used in the video from here 👉 pages.xelplus.com/merge-multiple-tables-file
Hi. Thanks for your awesome and helpful tutorials ! Can you please confirm that I'm not mad, and Data Model is not available on a MAC version of Excel ???
Cheers
i've found your channel has been 3 days, and however we say in my country, "i've been looking for copper, but i've found gold". T h a n k y o u!
Welcome aboard!
You are a life saver!!! Previously, I enrolled to ur VBA course and saved my life too. Once again, this save my life again! Thank you very much for being such a great teacher!!!
Wow thanks again! You are empowering so many people in the roles they already occupied. How powerful, thanks Leila! Anyone can lead and anyone can make the difference!
Your knowledge of Spreadsheet is stunning,Ms Gharani. I not only like but love your videos. They have been extremely helpful to me. Since I am learning programming, I wish you could come up with some such videos in days to come. All the best !
Awesome Data Model video, Teammate!!
Appreciate it, teammate!
Love your tutorials! You've really helped me step up my game at work.
Excellent tutorial Leila. Thank you very much for sharing your knowledge!
The data model I'm using at work to monitor OPEX is built exactly on that type of pivot table. Now I understand much better what my colleague hasn't explained me. Thank you so much Leila :D
Glad it helped!
Awesome as always 👍
I sure will come back to this tutorial many times when I need to use this function in the future
I am addicted to watch your all videos. You are such a brilliant and you don't know how much you are helping me.
Leila! I'm just gonna call you the Rock Star maker. You're helping us all to be Rocks Stars at this. Thanks so much for the knowledge transfer!🙌🏾
You rock! 😊
This was an excellent tutorial and exactly what I needed. You elevated my excel skills to then new highs. Thank you so much!
I’ve been working for 6 yrs now in a finance company dealing with data and Excel on a daily basis and I thought I ‘knew’ Excel. Wow your channel is amazing and I am now even more motivated. Anyways may I ask for a tutorial about Slicer tables in Excel, this is something I have been always struggling to understand. I would really appreciate that! Thanks in advance :) Keep up inspiring us Leila :)
Glad I could inspire you. I have a video on Pivot Tables on the channel in case you want to check it out.
Thanks for your work, I have been watching your videos for weeks to be better at my work. I send you a big hug from CDMX
Following your channel and watching your videos have upgraded my Excel knowledge to new heights. Thank you for enlightening us and keep bringing new concepts like this.
You're very welcome! Thank you for watching.
Thank you! I've actually looking to merge two different data types. Now I found one.
Wow thanks again! You are empowering so many people in the roles they already occupied.
Thank you Leila. This video helps me a lot in my works on originazing assets components. Highly appreciating.
Like to see many to many relation + nested tables hierarchy
Respectively and best regards
Awesome as always! Making these things very easy to understand.
Thank you, your courses are the best source for selflearning.
Thank you for enlightening us in Excel,
Another fantastic time saving video! Thanks!
Thanks Leila you are brilliant. I just came across your video while preparing for an interview where I will need to have knowledge of Power Query, Power Pivot and Power BI.
Fantastic! Super helpful, thanks Leila
Outstanding work as always. I now hope to find a video from Leila whenever I have anything challenging to do in excel.
Marvelous video. Clear and easy to follow example. Thank you for sharing.
You just rocked my Excel world.
Excellent as always. I'm trying to create a pivot from data generated by other two pivot tables from different files. May you explain the way to do it?
Another amazing video! Thank you!😊
Thanks for simple and clear explanation
I like relationship. Thanks Leila!
Great trick. Thank you for making it so easy!
Exactly what I need to do today. Thank you so much! ❤
Wonderful, Leila - no more helper columns! Thank you!
Wonderful Gino!
Whenever i find u uploaded a video
Step1: Click on the youtube video.
Step 2: Like it first
Step3: Then watch it..
Thank you Leila. Lots of Love...:)
Thank you for your support!
Excellent, thank you Leila. I thought I knew data models and just discovered from this there is still a lot to learn.
Glad it was helpful, Mike!
Leila you are simply the best on CZcams as your lessons are very captivating and easy to understand.
Wow, thank you!
Wow. Very explicit video tutorial, thanks for sharing this Leila. You're simply amazing
You’re welcome Nono 😊
Thanks. I am just a beginner . These give a better of how a pivot table is created.
I love your teaching and coaching style. Thanks a lot.
Happy to hear that!
This is just perfect ... simple and easy explanation ... thanks Leila
Glad you like it!
The data model was a wonderful addition to Excel, no more lookup formulas!
Thanks. I have been doing this in MS Access. This is much more practical. 👍
You really explain your topics very clearly! Excellent! Your advance lessons have convinced me to upgrade to office 365. I want to start using this technique but somehow I can't seem to find from the data tools the establish "relations" icon or command from my data tab. I also can't see the command for getting external data from folder or from existing workbook. I'm using office 365 for Mac. Can you help me please? Tried chatting on online chat support but the respondent said this feature is NOT available to Office 365 for Mac. Is this true?
Hi Leila. Excellent! Thanks for the reminder of how to make connections in Power Pivot. Picked up a few good tips and tricks from your presentation. Always a pleasure to watch your videos :)) Thanks for sharing and Thumbs up!!
Always a pleasure seeing your comments, Wayne :)
You are just fantastic! It's soooo good and you explained it very good. Thank you very much!
Nice and Crisp video Leila! As usual, I learnt a trick or two here as well! Take a bow Lady! You really are "Excel Queen"! 😊👍
Thank you! 😃
Really nice and clearly explained how to use the data model with nice music on the background.
Thanks nice job and looking forward for many such videos
Explanation top class.
Highly professional
I definitely struggled with this for a while, so I appreciate this tutorial. Thanks Leila :)
Thanks agian Leila! you are the best!
nice music in the video. i watch atleast one video of leila everyday
Great video! really useful and easy to understand.
Thank you for sharing!
Thank you for your wonderful work. It helps a lot. I obviously still have much to learn about excel :)
Brilliant presentation!
Thanks for sharing your knowledge.
I have enjoyed all of your great videos. I have learned much on so many topics from you. I have not found a video from you covering Microsoft Project. I think this software product is of the same quality as Excel. It seems like this product would be natural for you to present, especially with your background in project management. I hope you add this to your products to teach. Thank you for all your tremendous videos.
Thanks Laila, Excellent post, your video are awsome and helpful. time saving!!!!!!! wishing you the best
Great video ... simple and effective
Hey Leila, thanks for sharing this. I am sure this is going to be useful to lot of people.
I hope so!
As always, you are amazing.
I come to your video to refresh my excel skills. Thank you for clear explanation. Please keep up your good work.
Thank you, I will
I was waiting for this :) Excellent!!
Glad you liked it!
Thanks looks soo easy!!
your tutorials are really fascinating. Thanks a lot🙏
Glad you like them!
Grateful for your sharing.
This is perfect~ Thank you
Excellent. Thanks
awesome!! you're soooo amazing!
Thanks a lot Leila
❤️❤️ Enlightening
Awesome tutorial.... thanks
خانم قرانی خیلی ممنون بابت آموزش های مفید شما
Thank you for this video😊
Really nice to see these videos teaching Excel.I also have some tutorials on youtube and I hope you'll find them useful! Much love and keep on learning ❤️
Nice explanation very informative. Thanks
very good
i'm following you from egypt 🇪🇬
thank you for the excellent explanation. Now I understand the Data Model
Great to hear!
This is amazing, you're saving me hours and hours in the future from having to index-match!!
Happy to help :)
Excellent Trick as always. I wonder how come some people dislike this video! No matter how good a video is, there are always some stupid haters!!
Thank you for enlightening me. Enoy all
Very slick!
excellent video. thanks a lot
Great lesson. thank You Leila.
My pleasure!
Wow! What a wonderful video. Thank you so much. This is very helpful!
Glad it was helpful! Thank you for watching.
It's funtastic video.. love it ❤️👍
Thanks Leila...Another great time saver in the PowerPivot Data Model is the ability to set the data type (ie Sales Field - Number format as Currency) in the Data Model on the source field therefore eliminating the need to fix the number formatting in every subsequent Pivot Table. Thanks for sharing...
Thank you 😀
Thanks Leila, I'll apply this at work
Our pleasure, Felipe.
You are just great!
Ultra Amazing!!!
thank you its greaaaaaat
My favourite teacher
Excellent video, thanks for knowledge sharing.. this was new thing to me..
As always Leila does the best
Thank you 😊
I am really thankful to you because this will be help me a lot.
Thank you 😊 💓
My pleasure 😊
Another great video; thanks for sharing 👍👍
Thanks for watching!
Wow thanks so much