Use the Consolidate Feature to Combine Data
Do you want to combine tables together? Lets say you have sales data that span multiple columns but there rows and rows of values. Also there are multiple worksheets (or tabs) that have the same format. The only difference is that one worksheet if for salesperson A and the other worksheet is for salesperson B. Now with two sales people the act of combining this together would be a simple copy/paste. However if you have more sales people (like 10, 15, or more!), that would be a lot of copying and pasting. Instead of doing that, just use the consolidate feature in Excel to do the heavy lifting. See how it's done with this video.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~
Пікірлер: 104
impressive teaching and easy to learn. thanks alot
Great video hawmie, enjoyed it alot, Keep the content coming!!:)
Good tutorial thumbs up!! Thank you
it really helped me a lot :)
Nice video and teaching
I love how you explain that in details man you the best
@DougHExcel
4 жыл бұрын
Hi Stepy Voice, thanks for the kind words!
Great video. Clear, simple and to the point!!
@DougHExcel
5 жыл бұрын
Hi Al Jack, glad you liked it, thanks for commenting!
A very expressive way tq a lot.
Thanks for the effort
Thank you sir, it was good learning video for learning consolidate tool :)
Really very nice video and easy to use
Very crisp and clear presentation.Thank you.
@DougHExcel
7 жыл бұрын
Hi anand meundi, glad you liked it, thanks for commenting!
Thanks alott.. may God blesss u for ur effort and please do more education charity..
this video is wonderful, it has truly help me in my office work.
@DougHExcel
3 жыл бұрын
Hi Edwin Ekine, thanks for the comment!
Excellent teaching..thank you so much
@DougHExcel
6 жыл бұрын
Hi jozlin mary, thanks for the comment!
Muito Obrigado.
Thanks a lot,got what i was looking for.
@DougHExcel
6 жыл бұрын
Thanks Boniface Ebineng, glad it helped!
Thank you so much. Very useful
@DougHExcel
5 жыл бұрын
You're Welcome!
impressive teaching and easy to learn. thanks a lot. KEEP IT UP. Buddy.
@DougHExcel
7 жыл бұрын
Hi Manish Panchal, thanks for the comment!
Detailed explanation !!!
@DougHExcel
6 жыл бұрын
Thanks MS Excel Simplified!
Very helpful, thank you.
@DougHExcel
6 жыл бұрын
Hi teh bear, glad you liked it, thanks for commenting!
thanks a lot....! it is really working if u have any video related excel pls update it.....
good one. Got what I was looking for.
@DougHExcel
6 жыл бұрын
Hi Manoj Kumar, glad you liked it, thanks for commenting!
Thanks, well explained.
@DougHExcel
6 жыл бұрын
You're Welcome!
Very good! Thanks
@DougHExcel
6 жыл бұрын
Hi Mohamed Barry...you're welcome, glad you liked!
excellent and clean clear presentation
@DougHExcel
7 жыл бұрын
Thanks for the comment. Glad you liked!
very very teachig! great!
@DougHExcel
6 жыл бұрын
Thanks Maria João Vieira!
Very nice. You explain very well
@DougHExcel
3 жыл бұрын
Hi Kimmie Dao, thanks for the comment!
Thank you!
@DougHExcel
6 жыл бұрын
You're Welcome!
Amazing you help us to easy to learn
@DougHExcel
5 жыл бұрын
Hi A7mad Maher, thanks for the comment!
cheers dude!
@DougHExcel
5 жыл бұрын
Thanks xdisgreat!
Thanks mate.
@DougHExcel
6 жыл бұрын
You're Welcome!
It is really helpful. could you please create a video showing VLOOKUP function with multiple application?
Thank you
@DougHExcel
4 жыл бұрын
You're Welcome!
nice
Hi, It cleared my doubts for totaling the multiple sheets. Very explanatory, Thank You for your nice works. S PASUNGILI, DINDIGUL, SAP INFOTECH.
@DougHExcel
6 жыл бұрын
Hi Pasungili Subbanasari...you're welcome, glad you liked!
Thanku so much sir
@DougHExcel
5 жыл бұрын
You're Welcome!
Great teaching video, you made this topic easy to follow along!
@DougHExcel
3 жыл бұрын
Hi daveg655, thanks for the comment!
Hi Doug.. just one question will this also able to take care of rows. if few number of rows are different in data sets.
@DougHExcel
7 жыл бұрын
not sure what different data sets mean but if the columns fields don't match up across the sheets you might get some unintended results. Another option is to use Power Query. kzread.info/dash/bejne/eaCBqKSHgbi3gZs.html kzread.info/dash/bejne/gHyC0NCuf6mem7g.html
Super :)
@DougHExcel
7 жыл бұрын
Thanks!
Hi Doug! I would like to know if it works in case if the column names are different?
@DougHExcel
7 жыл бұрын
The final output will be in different columns
thank you vary much
@DougHExcel
7 жыл бұрын
you're welcome!
@user-qo4gn5iz3j
7 жыл бұрын
thank you i will watch your all lessons
very important video
@DougHExcel
3 жыл бұрын
Hi Ranush Malshika, thanks for the comment!
I have a column with 65,707 values. I made changes in 15,045 rows from "z" numbers to actual numbers corresponding to values. How do I replace the "z" numbers with the changes without affecting the "z" numbers that have no corresponding actual values?The data is in two workbooks. Thank you for any help you can offer.
Hi Doug H, do you know how to do this in Numbers? It does not have the tools like Excel
@DougHExcel
7 жыл бұрын
gosh...don't know the Mac equivalent of this... sorry :-(
Hi Thanks for your video.I have data which contains medical, institutions, furniture shops, doctors and much more.And the data is in laws and it is a single sheet.I am unable to separate by its category. Can you help me how to do this.Please please.
@DougHExcel
7 жыл бұрын
Sorry I don't consult, but if you present this question to folks on the mrexcel.com forum you may get some help. HTH
Hi, great video! Question: I have multiple tabs in a single excel workbook that I am trying to consolidate and make sense of. I was able to consolidate using your video which is great. BUT - is there a way to add more labels to the left hand column and then use it as a reverse drill down? For example - left most column would be Manager then to the right of that supervisor - and then Employee and then position and then all of the number data? I am hoping to consolidate the data and then be able to click on a manager which would then show only his/her supervisors and then if you click on a Supervisor his/her employees. All with the data going from macro to specific person.
@DougHExcel
3 жыл бұрын
Hi Lance Brissette, thanks for the comment!
Suggest me? I have a data in main Excel sheet , I want to separate those data according to region wise. Important think is when a enter the New data in main Excel sheet, It should be automatically separated according to region wise, Plz help me by suggesting Video.
sir I have a little problems in merge and center. when we lost merge and center how to get it back in work sheet. i.e the entire work sheet could not function it . merge and center is unable to click
@DougHExcel
3 жыл бұрын
El-em-El Taitom, that is very interesting...thanks for letting me know.
Exelent
@DougHExcel
7 жыл бұрын
Thx!
You forgot to add Asia (2) in your second example, but I loved the tutorial, thank you so much!
@DougHExcel
7 жыл бұрын
:-P Good catch...i had clicked OK with first clicking Add...but I hope most people got the gist of the steps!
@minterno1
7 жыл бұрын
I'm starting to handle more stacked datasets at work now and this really helped me pull everything together before running my pivots. Good stuff!
Hi Doug, Tried this feature based on your problem. In fact I tried both by creating a table for each of the similar data sheets and also selecting the cells as you have indicated in your video. However, the results do not appear. It displays with only a couple of headers and the data does not look presentable. Both sheets are exactly similar - rows and columns. Not sure what is the mistake here. Appreciate your thoughts. Thanks so much.
@DougHExcel
6 жыл бұрын
Check if there are any empty rows in your range. For an alternative way of doing this you can also use Power Query (if this is something that will be a recurring task). See this vid at kzread.info/dash/bejne/gHyC0NCuf6mem7g.html
@alphabeta01
6 жыл бұрын
Thanx Doug for your prompt response. I am using a Mac and did not find find the correct update for power query as you mentioned. Alternatively, is there a function I could use to obtain similar results. I am trying with Fn getpivotdata also. Appreciate your thoughts. Thanks
Doug, I did exactly what you taught......I keep getting an error message "Data not consolidated". What am I doing wrong?
@AUNTLORI56
8 жыл бұрын
+Doug H Doug, thanks so much. I figured out what happened. It didn't like the header that I choose to include (they were all different). Changed that and it worked perfectly!
GUD
@DougHExcel
6 жыл бұрын
Thanks Satyavir Singh!
I have a spreadsheet for 2018 customers with a column for "comments". How do I transfer those comments to a fresh spreadsheet for 2019. The 2018 sheet has about 4,000 rows and the 2019 sheet has about 7,000 rows so I can't just copy from 2018 and paste into 2019. I need the comments in 2019 to reflect for those fields from 2018. Thanks in advance.
@DougHExcel
4 жыл бұрын
With the columns similar an added column that is Year with the values like 2018 and 2019 could be an option. Also Power Query is a nice alternative to the consolidated feature .... see kzread.infosearch?view_as=subscriber&query=power+query+combine
I have followed your instructions and others who have the same instructions. But each time, I have entire missing columns of data from my 2nd, 3rd, and 4th columns. The header is there, but no data. And the data in the final column which is "years" is screwing up the years. All my years are 1900's and 2000's, it starts showing 3000's and 4000's years.
@DougHExcel
3 жыл бұрын
Sorry, don’t understand the comment/ question...
নাইস ভিডিও
@DougHExcel
2 жыл бұрын
ধন্যবাদ
البدرى
nice video but you forgot Africa
@DougHExcel
6 жыл бұрын
yes i did....so i'll try not to neglect it in the future as it IS one of the largest continents!