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

Advanced PivotTables: Combining Data from Multiple Sheets

4445 ratings | 842787 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 (140)
Dipti Sharma (3 days ago)
Thanks a lot , Well explained.
Love Blues Everyday (4 days ago)
Excellent Bob!!’ Thanks.
Andrei Marusic (6 days ago)
Thanks a lot. It worked for me.
NANDHUhu D (6 days ago)
Beautifully explained
Ntihin Thilakan (7 days ago)
hi enjoyed your video and i need help from your side.I want to copy data from one sheet to another sheet.I need when i paste it should go to the exact matching serial number.can you help with that
milind (26 days ago)
Nice information
Asaduzzaman Lashkar (1 month ago)
What shall i do if there is repeating og data among the data sheets??
Pulkit Pant (1 month ago)
Thanks a lot
The Entertainer (1 month ago)
Nice tutorial
L VR (1 month ago)
This only works with tables with unique headers. What if you're trying to combine large tables with the same headers and make one Pivot Table???
nonameghost (20 hours ago)
If I am not wrong, assuming that all your tables are with the same headers, you can referring to this link below: https://www.youtube.com/watch?v=JiAYngRcN0s to combine all your worksheets/workbooks. After combining, you probably could do pivot table after that, though, get rid of headers from other worksheets/workbooks by filtering it off.
Xan Kru (1 month ago)
great learning feature, now I just have to see if I can combine sales with targets...
bchabot1 (1 month ago)
When I try to create the relationships, it is telling me that duplicate values exist in the columns. is there a workaround for this because the data set that I am using is for students attending a class and will contain duplicates.
Danyos777 (2 months ago)
Great video! The presenter explains the technique well explained, concise, and informative. Thank you!
Jorge Velez (2 months ago)
Is the file Pivot Consolidate.xls free or you have to pay for download it?
Jos v Gorkum (2 months ago)
I consider myself pretty intelligent but when it comes to tutorial video's, I always end up feeling like a complete dumb**s. Yours was 100% clear. Big thanks from Holland!
rakesh kumar (3 months ago)
Nice video
Thanks. Good job
Bodega Importers (4 months ago)
Can you assist with a specific issue? I would like to work with you by phone re errors on my vlookup and pivot charts please
EVI NOVITA (4 months ago)
Very helpful d(^_^)b
frankenfishdom (5 months ago)
Is there any way to do this on a Mac?
frankenfishdom (5 months ago)
Is there any way to do this on a Mac?
Alth Cecil (5 months ago)
Why do people say 'two times' instead of 'twice'??
i.e. Civil (5 months 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 (5 months ago)
Owsome leaning
lyndonkimthomas (5 months ago)
Pyomomyiroeod Atzi(^^)🈯🇦🇩🇦🇷..*me 8'68:"me 9-+-)59 Omrodoneueis DESKNEID NIMRIS MOMROSOM📣🇦🇷🎫🏄🗞️🎫
jair serrano (5 months ago)
Nice, thanks :)
surendra kumar (5 months 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 (5 months ago)
I'm using excle version 2016
gravittii (6 months ago)
Thank You!
Umair Siddiq (6 months 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?
James Webster (2 months ago)
i'm also trying to do this
noy movie (6 months ago)
Dan Freeman (6 months 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!
L VR (1 month ago)
This ONLY works for tables with unique headers, this will NOT work if you have multiple headers that are the same. I have the same problem.
Babu Tanguturi (6 months ago)
Thanks for the Video
Jimmy Miller (7 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.
Pam Dooley (1 month ago)
I am having the same problem and the data does not have any duplicates. At first, I thought it might be blank rows but I have doubled checked and that isn't the case. Has anyone experience it and found the solution?
Ally Low (1 month ago)
The unique field/column that you are creating the relationships for (eg: order#) MUST NOT have duplicates. You either have to select a column that is unique in values (meaning it is unique and not repetitive eg: GeographyID, ClassCode, Serial#, Sales#, Receipt#), or you will have to select a column that is closest to being a unique value and have the duplicates removed from the data ribbon. Then you can start creating your data relationships by tying up the unique columns between each table.
Oyeyemi Rafiu Owolabi (5 months 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 (5 months ago)
Querobine dsouza nothing
Querobine dsouza (5 months ago)
I faced the same, by any chance did u find a solution or work around. thanks
LiamDinh (7 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 ?
rachael_badass212 (8 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 (9 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 (9 months ago)
This was one of the best tutorial videos I have seen. A very relaxed but comprehensive style in delivery.
Cathy Richey (9 months ago)
Thank you so much! This was quite helpful!
Ghyes Kamal (10 months ago)
ramkumar jayabalan (11 months ago)
very useful for me and big thanks for you.
sunpure oranges (11 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 (11 months ago)
simple and very informative
Penny Cox (1 year ago)
You're a legend. What a great video, well explained. :-D
Crimson Sun (1 year 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 (1 year 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 (1 year ago)
The Order Number has really earned good Karma :)
Thangath TamilAzhagie (1 year ago)
Add this data to data model- i couldnt find the option. What should i do?
alex abraham (1 year ago)
Very well explained!
Asif Khan (1 year ago)
thanks a lot for you detail video. i need some help.
Monster Stream (1 year ago)
Good video. Thank you
Arun Solomon (1 year ago)
DaModernDaVinci (1 year 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 (1 year ago)
very helpfull ... thx a lot!!!!
Zhao Zhang (1 year 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 (1 year ago)
It's awsome!! One question, if we do not make relationship, what is going to happen?
Azzeddine Chergui (1 year ago)
Thank you a lot !
Jammale Lamarti (1 year ago)
Thanks for this video ! Appreciated. It is useful.
Khayrul Amin (1 year ago)
Kassandra (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)
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)
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 (5 months ago)
You may load data from workbook, i guessed.
plo (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?
L VR (1 month ago)
This ONLY works for tables with unique headers, this will NOT work if you have multiple headers that are the same.
Hussain Akhtar (8 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 (9 months ago)
Tyler Brogdon hi yes there is, instead use merge data in power query more powerful
Davo Devigne (2 years ago)
Yep, pretty damn goooood!
Eva Tsang (2 years 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 (2 years ago)
Casey Leftwich (2 years 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 (2 years ago)
Good work
Giorgia Lopez (2 years 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 (2 years ago)
Very good. Thank you!
Rizwan Tahir (2 years ago)
very good Thumss up
SteelerY360Nation (2 years ago)
I just found your channel, and went to your site to view tutorials by using the free download workbooks! - Thank You!
Rajendra Bangar (2 years ago)
Good vedio but vedio quality is very poor.
Mohan Pant (2 years 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)
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

Would you like to comment?

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