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

You know what's funny? Me trying to do this all morning* without access to power pivot Thanks a lot, [my stupid company]!

*I'm trying to re-create a report I made in PBI just in Excel 2016. Why? You know why. Ended up merging queries but I didn't feel great about it..

How do I select two or more columns per table? Or do I need to make that key column?

I am addicted to watch your all videos. You are such a brilliant and you don't know how much you are helping me.

You just rocked my Excel world.

Thanks for simple and clear explanation

Duplicate values how should we fix it

Outstanding work as always. I now hope to find a video from Leila whenever I have anything challenging to do in excel.

Exactly what I need to do today. Thank you so much! ❤

Hi Leila, I recently tried out the data model, but I am facing the issue where the pivot table does not allow me to summarise the values by product or sum as it says the data is in text. I have changed the data in the source Excel table to numbers by using the Text to Columns method, refreshing the modals and the pivot table. But still it says data is text. I have also tried changing the data in Power Pivot table to Number by formatting, but this is not possible as I get an error warning.

Pls advice is there is a way around this thanks?

Wonderful presentation mam. I will follow these steps and try to do accounts using excel. Currently I am doing it manually in excel

what is left to join your course. You already taught all the advanced things 😀

hi leila how to do if there is a duplicate ?

Hello, in this same scenario, I am running into blank rows. how do I remove them? the pivot table options isn't working.

I will be waiting for your assistance on the issue please

Well Hello Leilla. This relationship tab is not activated in my Excell Version. Can you tell me how to Activate it?

Thanks!

Thank you, your courses are the best source for selflearning.

This was an excellent tutorial and exactly what I needed. You elevated my excel skills to then new highs. Thank you so much!

Your tutorials are very informative. Need your assistance in which would be the best way to takle this situation. I want to create a bill of material if you select a type of product as a duct sensor location on F3 will answer on F5. The description of the answer of F5 will be on I7. the answer to F5 will also be able to be displayed on F13. The answer to I7 will be also displayed on I14. Would like to know how to go about this or which classes would best help me with this issue. Greatly appreciate your assistance.

Please share with me your email Address

Brilliant presentation!

There is a problem. If there are tables in different sheets, and you want to connect them, it gives inconsistent results in the pivot table. Example: there are 5 sheets each with yearly sales pro region. If you connect them with region, pivot table returns inconsistent results for regions and years. First table sales per region is ok when you include the next, it combines all regions and returns it as a one region sales.

Explanation top class.
Highly professional

Thanks for this video. I tried with my own data and when creating relationship, there's a pop up that my data are duplicates. Which data isn't allowed duplication, is data in Column (Foreign) or Relationship (Primary)?

I am doing this but not working. The pivot works if only using data from one table, but when pulling from another one is broken. I need to add the revenue for my sales orders on one table (multiple sales order# duplicates since I have the revenue per line item) and then add the total freight pero order (not line) coming from another table. Instead of giving me the freight for that specific sales order, its giving me the sum of all the freight, and is putting that value for every row in the column.

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

Hi Leila, I am a Qlik Engineer but always wanted to learn excel to do things faster like Quality Checks. Your videos are of great help. Really appreciate your time making these informative videos. I am trying to excel in Excel by watching & practicing.

Hello madam I liked your videos , I am computer teacher and wants to create report for students performance in various test in a year. Please make one video on it.

Could you please explain how to create one to one relationship in power pivot

Not getting 'Use this Window's Data Model' option when I am trying to insert a Pivot Table... Created the Realationship between the Worksheets and its visible on the Diagram view as well... but this option of 'Use this Window's Data Model' is not coming and it is asking me to insert the Table Range as in the case of normal Pivot tables.... PLease help

Another amazing video! Thank you!😊

Grateful for your sharing.

I am really thankful to you because this will be help me a lot.

Thank you 😊 💓

Hi...when i am creating a relationship between two tables it says at least one column must have unique values...i have checked and they both have unique values so dont understand why i am getting the error...could someone advise? I am not a pro

Excellent. Thanks

When creating pivot table from a model, you can directly do it from the model window. What you suggested is also one alternative but if you do not refresh the base window, insert pivot window may not suggest "add from data model" option.

Thank you for this video😊

Thanks for explaining this nicely. One question; why I'm getting "The relationship can't be created because each column contains duplicate values. Select at least one column that contains only unique value" error while trying to create a connection between 2 tables? 😵‍💫😵‍💫

I seriously cannot for the life of me create relationships. I always get the duplicate value error. I’m trying to connect my finances. 2022 with 2021. So I cannot remove duplicate categories. Or reoccurring charges. So idk how to do this then.

Excellent video, thanks for knowledge sharing.. this was new thing to me..

how about same data in multiple sheet same header and Same as due to large file

your tutorials are really fascinating. Thanks a lot🙏

Thanks for this... But if we create pivot this way, is there a way in which we can control all those data with single slicers

I have sales tables i have added to the model. Each table has the same metrics (sales results for a month.. for example traffic, units, cost...) I am looking to be able to say, pull in June traffic and july traffic for instance.. When i set a connection (store id for this example), i pull traffic from one table. when i pull in traffic from the second table, it is giving me the grand total of traffic rather than the traffic by store id.. what am i doing wrong? Would it be best to set the data up differently (ie. traffic table, units table). so on and so forth? I am asked to continually look at MoM, YoY and looking to have a repository of data loaded so its simple pivots rather than wasting time sourcing

Is there a way to combine fields from different table, e.g see what type of article each customer had ordered or these kind of info? Or is only possible to have aggregate measures like Total Sales per customers so measures like Sum, Count etc?

Grateful and amazing way for learnining so could you please share excel sheet you work on for further application and practice

Good

When I click close and load. It's showing me two option and I can't find pivot table option. Can you please give a solution?

Thanks looks soo easy!!

Thanks Laila, Excellent post, your video are awsome and helpful. time saving!!!!!!! wishing you the best