HomeНаука и техникаRelated VideosMore From: Tuts+ Computer Skills

Advanced PivotTables: Combining Data from Multiple Sheets

3250 ratings | 709783 views
See companion tutorial and sample files on Tuts+: https://computers.tutsplus.com/tutorials/advanced-pivottables-combining-data-from-multiple-sheets--cms-21190. By Bob Flisser.
Html code for embedding videos on your blog
Text Comments (115)
frankenfishdom (3 days ago)
Is there any way to do this on a Mac?
frankenfishdom (3 days ago)
Is there any way to do this on a Mac?
Alth Cecil (6 days ago)
Why do people say 'two times' instead of 'twice'??
i.e. Civil (11 days ago)
Can I get relate a column to a row? I'm having a spreadsheet in which I need to extract data to match the data from the other
Deepika shakya (11 days ago)
Owsome leaning
lyndonkimthomas (12 days ago)
Pyomomyiroeod Atzi(^^)🈯🇦🇩🇦🇷..*me 8'68:"me 9-+-)59 Omrodoneueis DESKNEID NIMRIS MOMROSOM📣🇦🇷🎫🏄🗞️🎫
jair serrano (12 days ago)
Nice, thanks :)
surendra kumar (15 days ago)
Hi, I facing problem many time this error. @we couldn't get data form the data model. Here's the error message we got:
surendra kumar (15 days ago)
I'm using excle version 2016
gravittii (25 days ago)
Thank You!
Umair Siddiq (1 month ago)
My data has the same tables but order numbers are not the same. They're always unique values. I have two sheets with annual performance and need to combine the two sheets into one pivot to track product level performance. Any idea how to do that?
N Project (1 month ago)
https://www.pivot.one/app/invite_login?inviteCode=dixqtp
Dan Freeman (1 month ago)
Hoping this older post may still be monitored. Thank you for this simply explained video! I've created a pivot using data from two worksheets within the same workbook. Both have the same heading names for Part Numbers and Part Quantities. The quantities from one sheet show correctly by item. The quantities from the second sheet show the summed quantity of all Part Quantities in the Part Quantity column. Any advice on how to return the correct quantities by item? I tried this in MS BI too and received the same results. Thanks in advance!
Babu Tanguturi (1 month ago)
Thanks for the Video
Jimmy Miller (2 months ago)
the Relationships don't work for me, says data has duplicates, any thoughts, I've tried to select multiple collums to match up, no luck.
Oyeyemi Rafiu Owolabi (19 days ago)
Ok, pls can you described how your data looks like may be i can help. Because, at times it is the "Append" that works not "Merge"
Jimmy Miller (20 days ago)
Querobine dsouza nothing
Querobine dsouza (20 days ago)
I faced the same, by any chance did u find a solution or work around. thanks
LiamDinh (2 months ago)
Great tutorial. Thank you very much. I follow the provided link to get the sample file to practice but couldn't file the zip file. Is there any other way I can get it ?
rekstrom100 (3 months ago)
Im not sure why I am getting this error.says, "Both select columns contain duplicate values. At least one of the columns selected must contain only unique values to create relationships between the tables."
William Culver (3 months ago)
Nice Vid Thanks a lot. @ 7:24 you say there's nothing visibly different but actually the table icons change over on the right, they gain a 'filled in' header bar rather than empty :)
Tony and Sandy Hartigan (4 months ago)
This was one of the best tutorial videos I have seen. A very relaxed but comprehensive style in delivery.
Cathy Richey (4 months ago)
Thank you so much! This was quite helpful!
Ghyes Kamal (5 months ago)
Excellent
ramkumar jayabalan (5 months ago)
very useful for me and big thanks for you.
sunpure oranges (5 months ago)
Thank you very much sir, Really great..sir now I was able to understand how to create a data. Thanks a lot
Kishore Tholana (6 months ago)
simple and very informative
Penny Cox (6 months ago)
You're a legend. What a great video, well explained. :-D
Crimson Sun (7 months ago)
I was just watching the video and I found it extremely helpful.Unfortunately the only thing I fail to understand is why do we begin creating the connections always by selecting the "payment info" tab as first?I tried beginning with "customer info "(created a pivot with it) and then related to "order info" and "payment info"but it did not work properly.I guess it is a data tables relationship principle I'm failing to understand completely?
santosh kumar (7 months ago)
Thanks for the video.. can any one let me know how to do single pivot for different sheets but the data in different sheets are same..
Avishek Bose (8 months ago)
The Order Number has really earned good Karma :)
Thangath TamilAzhagie (8 months ago)
Add this data to data model- i couldnt find the option. What should i do?
alex abraham (8 months ago)
Very well explained!
Asif Khan (9 months ago)
thanks a lot for you detail video. i need some help.
Monster Stream (9 months ago)
Good video. Thank you
Arun Solomon (9 months ago)
Super
DaModernDaVinci (9 months ago)
anybody else notice that when Bob finished creating the relationships between the tables the table icon on the PT fields pane darkened up a little..? 6:57
Christoph Meirich (9 months ago)
very helpfull ... thx a lot!!!!
Zhao Zhang (10 months ago)
Hi Bob, Thank you for the great video! The tutorial is clear and thorough. However, it just occurred to me that it might be easier use VLOOKUP to merge 3 sheets together and the convert it into pivot table. Plus, could we combine the 3 sheets by simply copy+paste? Cheers! merry xmas to you
yimcheol kang (11 months ago)
It's awsome!! One question, if we do not make relationship, what is going to happen?
Azzeddine Chergui (11 months ago)
Thank you a lot !
Jammale Lamarti (11 months ago)
Thanks for this video ! Appreciated. It is useful.
Khayrul Amin (1 year ago)
Nice
Kassandra Yuen (1 year ago)
super duper ~
HTC Desire (1 year ago)
Every week we prepare reports using pivote table,can any one let me know how set up short cut so that every week we prepare this report automatically by pasting raw data.
Rahul Surajith (1 year ago)
its pretty easy. When you make the table, try to make it a dynamic one using the offset formula in the refers to box.. Once you do this, every time you copy stuff , just got to pivot table and refresh it
Mohammed Ziaullah (1 year ago)
Thanks, very good info after "PIVOT TABLE"
Helge Haugland (1 year ago)
Fantastic
jjbean321 (1 year ago)
Awesome, Thank You!
Riverboat (1 year ago)
Excellent video!
uday sai (1 year ago)
Am unable to find this opeion on Excel 2007 version. How can we get it ?
Keely Soto (1 year ago)
Awesome!
narawan sucharit (1 year ago)
Thank you. it help a lot
Mike H (1 year ago)
i have 200 worksheet, how can i combine without going into individual sheets
Oyeyemi Rafiu Owolabi (19 days ago)
You may load data from workbook, i guessed.
pitchfork (1 year ago)
good video but 10pct of the video is renaming the tabs and tables, could have been done beforehand
accounts accounts (1 year ago)
add this data to data model is not available. pls help me, i can check it that;
epaminondas neto (1 year ago)
Extreme helpful; step to step exposition is fine too. Tks a lot!
Raja Chauhan (1 year ago)
great info Bob, keep up the good work... all the best.....
Globalization_ (1 year ago)
thanks mate great video
Vishan Khandelwal (1 year ago)
Extremely helpful :-) Thanks
S MY (1 year ago)
Extremely helpful. Thanks alot
E X I L E D Hobbit (1 year ago)
Very good, but dissapointed since I could not use it with my data. (I have to summarize multiple years data which each have their own tab and each year's tab has data by region on it.... so what I'll call the control# is what I consider the primary key, but so for each control # there are multiple regions reporting results for that control#. and how would I relate the tables, relate 2012 to 2013, then 2012 to 2014 or what?
Hussain Akhtar (3 months ago)
Share your data then it may be understood properly and solution provided.. it works perfectly with different data I have tried
Tyler Brogdon (1 year ago)
While this is useful, it also disconnects your data from external connections, is there any way to keep this from happening?
malsaso (3 months ago)
Tyler Brogdon hi yes there is, instead use merge data in power query more powerful
Davo Devigne (1 year ago)
Yep, pretty damn goooood!
Eva Tsang (1 year ago)
for example, what if your oranges were more expensive and a extra charge of 2.55% (only the oranges) how am I able to show that on the same table?
poopsie pie (1 year ago)
thanks
Casey Leftwich (1 year ago)
This was extremely helpful. If we don't have an order number but only month and year, can we relate that instead?
Awais Ansari (1 year ago)
Good work
Giorgia Lopez (1 year ago)
Hello Bob! Thanks so much for this tutorial, it´s great help. I downloaded the files and created the tables with no issues. Only, when I create the pivot table and check the Data model option, I get this error: We couldn´t load the Data Model. This may be because the Data Model in this workbook is damaged. Any advice on how to solve this? Many thanks in advance!
Alex Rosén (1 year ago)
Very good. Thank you!
Rizwan Tahir (1 year ago)
very good Thumss up
SteelerY360Nation (1 year ago)
I just found your channel, and went to your site to view tutorials by using the free download workbooks! - Thank You!
Rajendra Bangar (1 year ago)
Good vedio but vedio quality is very poor.
Mohan Pant (1 year ago)
Excellent Video
Kris Maly (2 years ago)
I enjoyed watching this video and recommend others to watch Host is an awesome teacher. Explains clearly and crisply. Thanks for educating the community and appreciate your volunteership. Keep posting some more videos. Thanks a bunch
karan shakye (2 years ago)
Good
Angelo Sammarco (2 years ago)
Not bad! The main point of concern here is that he is using Excel 2013, not Excel 2010, so you may not be able to do exactly what he does, but, again, the overall video is not bad.
gopal DAS (2 years ago)
Angelo Sammarco
Mishelle Fisher (2 years ago)
Awesome tutorial. I really appreciate how you broke it down into easy to follow steps.
Wilson Santiago (2 years ago)
I noticed that there is a lot of Access in these power query and power pivot courses ????
ENEA MYFTARI (2 years ago)
Hello Sir! I have been looking for this kind of video for long. In fact, I am using excel 2010 and Data Model is missing. How can I deal with that?
Michael Lim (1 year ago)
ENEA MYFTARI Buy excel 2013?
Mani Ramachandran (2 years ago)
Very useful and explained in simple language
Bishoy Kailini (2 years ago)
Thank you so much
Leslie Serrano (2 years ago)
best tutorial, thanks for share ...
Alina Pinca (2 years ago)
Great tutorial, thank you!
J Munoz805 (2 years ago)
Great tutorial, thank you, I wanted to see if it is possible to create a pivot just like you did with worksheets that pull the data from a Microsoft query? and if so will I be able to refresh my data on my pivot table anytime the data is refreshed on the worksheets?
TheADIDAMAC (2 years ago)
Excellent tutorial thank you. I have a specific question a little different from your scenario. I have multiple sheets (lets say different accounts). Each have the exact same headers. However, the rows aren't linked (all unique transactions). I want a pivot table that will get data from multiple sheets but give me the flexibility of playing with combinations of headers whichever way I want. Creating a pivot table through the wizard and multiple ranges handicaps me as it puts all headers under column field and the first column on each sheet as the rows field. Is there a way around it? I am using Excel 2007
Brandon Brown (2 years ago)
most legit Excel tutorial I've seen in a long time. Now I don't hate Pivot tables as much coming from a strictly database background.
Dan Eduardo Alvarado (2 years ago)
Great JOn.. Please make more tutorial videos, you have helped countless sous.. Thanks a million!
Mansoorsadat (2 years ago)
Bob Flisser!!! Awesome job man... Thank you... Today I learned something new :)
crispmint (2 years ago)
thank you! that is very helpful!
jazzista1967 (2 years ago)
great... finally I was able to understand how to create a data model if you don't have the  powerpivot feature.. Thanks a lot
Malcolm Gay (2 years ago)
Thanks. Very informative and concise coaching.
Lincoln Kayondo (2 years ago)
GOD BLESS U
Connor Macorin (2 years ago)
When you press "add this data to data model," what exactly does that achieve? Couldn't you still make the relationships with the other two tables without pressing that button?
Vanessa Riley (2 years ago)
what should i do when i go to insert the pivot table and "add this data to the Data Model" is not "select-able"?
Derrick Ludwig (2 years ago)
+Vanessa Riley : Are you view the excel workbook in compatibility mode? If so, save it as a normal excel workbook and try again.
Indrayan Bhattacharya (2 years ago)
Thanks. Good tutorial.
Bassem Helou (3 years ago)
Dear , thank you for your tutorial , it was helpful i didnt know that 2013 does this , the issue that when creating the same pivots does it need to be as the same criteria ? i know having a unique value is the common thing between all sheets , but will it show for you ? i will try it , thanks
Aggelos Papaemmanouil (3 years ago)
Hallo admin, that is a great tutorial. Really interesting and helping, because I had a task in work and wanted to combine multiple data from different sheets in one pivot table, and the idea of having 6 pivot tables in 6 different sheets was not appealing. I wanted a pivot table that summarizes all the data from all sheets. I have a question though: As I said, I have 6 sheets with a set of data (that is: interest income, interest expense, income from commission, balance of deposits, balance of loans and balance of Letters of Guarantee). All of these have the customer ID in common. Due to the fact that in each sheet there is a column that refers to different periods of time (2012, 2013, 2014, 30/06/2015, 31/12/2015), customer_ID is DUPLICATED. So, does this new excel feature Data Model create false tables? Any suggestions?
Bassem Helou (3 years ago)
+Aggelos Papaemmanouil also when the date is in the columns field you can group it by year . just right click on the date in the pivot and group it .
Bassem Helou (3 years ago)
+Aggelos Papaemmanouil hello , i was wondering the same issue and one more thing i write in my comments below , for your years : split the dates : data then text to columns then select what is the separator for your number formats , then you have created years in number format in one column , the relation will be now the duplicated ID will show in multiple places , what to do is to put the year in the filter row in the pivot table or in the tab row it will show it for you horizontally , but it will be big file to print or preview . i wish i was helpful
induonyoutube (3 years ago)
Amazing! Thank you for posting this video.
S MY (3 years ago)
Salute you!
Adarsh Maurya (3 years ago)
THANKS FOR SHARING THIS EXEL FILE THIS IS VERY EASY TO UNDERSTAND AS SHOWN IN THIS VEDIO.
Mahmud Ankur (3 years ago)
how can i get this xl file please?
Srikanth C (9 months ago)
Thanks for improve our skills
Tarun Ahuja (1 year ago)
really very nice videos...I really appreciate your way of teaching ....
Rick Darcy (3 years ago)
+WILL J OK, I found the download attachment box on the right side of the this link.   http://computers.tutsplus.com/tutorials/advanced-pivottables-combining-data-from-multiple-sheets--cms-21190    thx
Rick Darcy (3 years ago)
+WILL J Page not available. I'm looking for the worksheet or zip file, please.  Thanks!
WILL J (3 years ago)
+Mahmud Ankur https://computers.tutsplus.com/tutori....

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.