Create a PivotTable in Excel using multiple worksheets by Chris Menard
Вставка
- Опубліковано 28 жов 2024
- 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 - chrismenardtra...
○ Screenshots - Snagit - chrismenardtra...
○ UA-cam keyword search - TubeBuddy - www.tubebuddy....
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!
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!
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.
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.
This is the best I'm always looking for information on the fly and this was quick and to the point
Thank you.
Excellent, very useful while extracting data from database tables.
Thank you.
Amazing tips. Solved my issue!
Thank you.
Thanks so much for posting this Chris! I'm really starting to see why Excel superusers fancy tables so much :)
Thank you.
Perfect. I have been looking for something like this for months.
Glad to help.
Thank you so very much for our online training session!
You're welcome
This is perfect - saved a lot of effort for me. Thanks.
You're welcome!
Thanks for this. Nice easy steps.
Thanks, Pat.
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 :) :)
I love the nice summary towards the end of the video. Great job!
Thank you! I appreciate the feedback. Have a wonderful weekend.
Wow.. you are such a nice teacher
Thank you.
Thanks Chris. Just what I need.
Glad to help
quick and too the point! thank you!
You're welcome!
Super clear and helpful, thank you!
You're welcome!
So excited! Tks so much!
This was awesome. Thanks!
Glad you enjoyed it!
Im going to try this today, was looking for similar solution.
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
Thanks, brief and detailed. Very helpful
really useful - you saved me hours of work! Amazing!
Thank you! I appreciate the feedback.
Love It! thank You!
Fantastic tutorial ✌👍
Thank you!
Cool... Tqvm Chris
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!
Professional, thank you!
Thank you! I appreciate the positive feedback.
Thank you very much!! (y)
You're welcome!
Thank you
You're welcome
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
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-
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?
Awesome. Can i add 6 tables?ofcourse with relations to the maintable intact
I cannot check the box "Add this data to the data model". Why?
hi nice tutorial, btw how bout excel 2010??
I don't have excel 2010, but it should work.
i want to consolidate salary sheets 12 months in one sheets is this format is working ?
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.
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.
when doing this pivot for multiple tabs, the calculated field options doesn't work anymore, please help
Thanks
You're welcome.
The checkbox while creating the pivot table is disabled for me. What do i do?
how to do this on a mac?
Unwritten rule of excel don’t use a Mac
Hi Mr Chris Menard
can i do it in MS office 365
thanks
I was in Office 365 when I created this video.
I didn't know Excel could create relational stuff like this.
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
how can i get the data sheet Chris? I would like to practice please
Any help? I do not have the bottom box.
How can I join your course?
add this data to this data model option hide in excel please guide me what can do
i didnt get active all in my piviot table
what i have to do
Can you teach me how to mail merge multiples worksheets on one form
why is the option unavailable for me ?
Hi thanks for your email. Can I download these sheet so I can practice? Thank you once again.
Please let me know regarding your training sessions
can you please explain this trick would work in ms excel 2010???
I don't believe this is possible in Excel 2010.
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!
Does not show Analyze multiple tables in Office 2010
It should say Using 2 worksheets
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?
Hi Sir may I ask what version of excel is this? Thank you
I use Excel with office 365 subscription.
mmmk