Advanced Pivot Table Techniques: Combine Data from Multiple Sheets in Excel

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.

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

🚩Let’s connect on social:

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

[naito]

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

[nadeemmalik]

Leila you are simply the best on YouTube as your lessons are very captivating and easy to understand.

[ericm]

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!

[TheImmortals]

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

[shishupalsingh]

I love your teaching and coaching style. Thanks a lot.

[neofjcn]

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

[ret]

Marvelous video. Clear and easy to follow example. Thank you for sharing.

[ghasemhossein]

خانم قرانی خیلی ممنون بابت آموزش های مفید شما

[mattschoular]

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

[jiajiaphotography]

Awesome as always 👍
I sure will come back to this tutorial many times when I need to use this function in the future

[prasannawarnasuriya]

thank you for the excellent explanation. Now I understand the Data Model

[askulasaatwikthc]

Thank you for enlightening us in Excel,

[harigokul]

Thank you! I've actually looking to merge two different data types. Now I found one.

[Waydisturbed]

And yet another uploaded video that I will be sharing with my team. Leila Gharani IS #MsExcel!

[HenricAndersen]

You are just fantastic! It's soooo good and you explained it very good. Thank you very much!

[LearnExcelVBA]

Hey Leila, thanks for sharing this. I am sure this is going to be useful to lot of people.

[eliseumessias]

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!

[brijeshshah]

Superb !! Thanks a lot for the knowledge shared !!

[arnoldwilso]

Thank you for enlightening me. Enoy all

[andreademiliano]

Hi Leila!
I have a couple of concerns I would like to share, maybe you or someone else might help me.
1) Is it possible to create a relationship between tables located in different excel file or are they have to be in the same one?
2) Is it possibile to connect two tables without setting them as real “excel tables”? I used to work with very big databases and I noticed that, when I apply table formatting, file’s size increases a lot!

Thank your for sharing this powerful function and for all the job you do! I have been believing to be already a good excel user, but it’s incredible how many new tricks I’m learning watching your tutorials! You’re the best