Create a PivotTable in Excel using multiple worksheets by Chris Menard
Vložit
- čas přidán 24. 05. 2017
- You can create a PivotTable in Excel using multiple worksheets. The key is to turn the ranges into Tables. The trick to doing this is the tables are related. Example: you may have one table that has all your customers and their customer numbers. Another table contains orders for those customers but must include the customer number. We tie the customer number from the Customer Table to the customer number of the Orders table.
#pivottables #exceltutorial #excelworksheets
Chris Menard is a Microsoft Office Master Instructor. He is also a public speaker on technology products. His clients include:
* The University of Georiga Executive MBA students.
* Mizuno USA
* Gwinnett Medical Center
* The Georgia Society of CPAs.
And make sure you subscribe to my channel!
- EQUIPMENT USED --------------------------------
○ My camera - amzn.to/3vdgF5E
○ Microphone - amzn.to/3gphDXh
○ Camera tripod - amzn.to/3veN6Rg
○ Studio lights - amzn.to/3vaxyy5
○ Dual monitor mount stand - amzn.to/3vbZSjJ
○ Web camera - amzn.to/2Tg75Sn
○ Shock mount - amzn.to/3g96FGj
○ Boom Arm - amzn.to/3g8cNi6
- SOFTWARE USED --------------------------------
○ Screen recording - Camtasia - chrismenardtraining.com/camtasia
○ Screenshots - Snagit - chrismenardtraining.com/snagit
○ CZcams keyword search - TubeBuddy - www.tubebuddy.com/chrismenard
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week! - Jak na to + styl
After having been working in Excel for 15 years, this is the first time I am looking at Relationships functionality of Excel. Thank you for uploading this.
You are welcome!
Thanks, brief and detailed. Very helpful
Perfect. I have been looking for something like this for months.
Glad to help.
Thanks so much for posting this Chris! I'm really starting to see why Excel superusers fancy tables so much :)
Thank you.
I love the nice summary towards the end of the video. Great job!
Thank you! I appreciate the feedback. Have a wonderful weekend.
You are asome! You have no idea how much you just helped me! Thank you sooo much!
Bryan, I'm glad I could help. Thank you for the positive feedback.
really useful - you saved me hours of work! Amazing!
Thank you! I appreciate the feedback.
Thanks Chris. Just what I need.
Glad to help
Thank you!! I looked up three other videos that made it so much more difficult.
You're welcome! I'm glad mine was the easiest.
This is perfect - saved a lot of effort for me. Thanks.
You're welcome!
This is the best I'm always looking for information on the fly and this was quick and to the point
Thank you.
quick and too the point! thank you!
You're welcome!
So excited! Tks so much!
Thank you so very much for our online training session!
You're welcome
Excellent, very useful while extracting data from database tables.
Thank you.
Amazing tips. Solved my issue!
Thank you.
Thanks Chris. That was GREAT!!!
I appreciate the positive comment. Thank you.
@@ChrisMenardTraining You're Welcome. I'm starting to follow all your videos now. Always Learning :) :)
Super clear and helpful, thank you!
You're welcome!
Cool... Tqvm Chris
Welcome.
Professional, thank you!
Thank you! I appreciate the positive feedback.
Wow.. you are such a nice teacher
Thank you.
This was awesome. Thanks!
Glad you enjoyed it!
Love It! thank You!
Thanks for this. Nice easy steps.
Thanks, Pat.
Perfect video. Not too much blah blah blah.. short and to the point. I hate Videos that 90% is totally unrelated jargon.
Love the comment. I try not to do to much blah blah blah
Very simplified technique
Thank you.
Im going to try this today, was looking for similar solution.
Fantastic tutorial ✌👍
Thank you!
Thank you very much!! (y)
You're welcome!
Thank you a lot Chris!
Does anybody know how I can make a table without losing my initial format? I don't want it to have that Excell-made bluish design.
so cool! thanks
Glad you liked it!
Hi Chris, I have two tables, one with demand and one with supply, unique filed is Demand number. Against every demand there is multiple supply (and the supply has different status). I want to make a pivot with demand and supply against each demand along with the status of supply. Can you please suggest a way to do this.
I tried the method you suggested; however I am not able get the desired view.
Thank you
You're welcome
Thanks
You're welcome.
I didn't know Excel could create relational stuff like this.
i didnt get active all in my piviot table
what i have to do
Awesome. Can i add 6 tables?ofcourse with relations to the maintable intact
i want to consolidate salary sheets 12 months in one sheets is this format is working ?
I cannot check the box "Add this data to the data model". Why?
Hello. My current problem is: two tables- too large to combine into a single sheet. Can I use a similar method to have a single pivot pull data from both tables? They are the same headers… just too long for excel
Hi-
My two tables have identical column headers and the information is identical values. I'm using two worksheets because I ran out of room on the first one. However, when I try and est. the relationship I get the message '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.'
When selecting, I selected the company name which was the first column for both and the same on throughout which is why they're not unique.
Any ideas?
how can i get the data sheet Chris? I would like to practice please
The checkbox while creating the pivot table is disabled for me. What do i do?
I have multiple sheets with the same data types and I want a pivot table to consolidate the data. What change do I need to make to avoid having multiple sums? There is too much data to combine into one sheet so that isn't an option.
I have the same issue Tom. Did you came up with a solution?
Not yet
Hi thanks for your email. Can I download these sheet so I can practice? Thank you once again.
how to do this on a mac?
Unwritten rule of excel don’t use a Mac
add this data to this data model option hide in excel please guide me what can do
This only works for unique values. What if you have datasets with fields with non unique values?
for link two tables there should be a link for both the tables then only we can link
when doing this pivot for multiple tabs, the calculated field options doesn't work anymore, please help
If we have no primary key so what we have to do. for example I have same data in two sheet and both sheet header is same and month are also same , In this situation I select month for primary key but there are show error:- " both selected value are duplicate"
In this condition what i have to choose for primary key.
Pls suggest..
I am having the item wise sales table having multiple invoice no and in sales ledger having invoice no in such a conditions how can make a pivot table
Can you add more data to an existing table after creating the pivot table?
Yes. Add data to your tables, and just refresh the PivotTable.
can you please explain this trick would work in ms excel 2010???
I don't believe this is possible in Excel 2010.
Any help? I do not have the bottom box.
why is the option unavailable for me ?
How can I join your course?
Can you teach me how to mail merge multiples worksheets on one form
Please let me know regarding your training sessions
How come I don't see the option to "Add multiple sheets to the Data Model"? I was able to create the two tables but can't seem to see both tables when creating the Pivot?
Hello. Can you tell me what version of Excel you are using and if it is PC or Mac? I haven't tested this in a Mac for Excel.
im on a PC, Excel 2010 (and I have 2007)
@@kmhoebel Hey Karie. I went and checked and this will only work in Excel 2013, Excel 2016, and Excel 2019.
Booooooo thank you so much!
hi nice tutorial, btw how bout excel 2010??
I don't have excel 2010, but it should work.
Hi Mr Chris Menard
can i do it in MS office 365
thanks
I was in Office 365 when I created this video.
Does not show Analyze multiple tables in Office 2010
It should say Using 2 worksheets
mmmk
Hi Sir may I ask what version of excel is this? Thank you
I use Excel with office 365 subscription.
Not enough explanation, what are you trying to do? What is the outcome? What is the prerequisites?? what data can be used? What can not be ujsed?