EASILY Combine Multiple Excel Sheets Into One With This Trick

Рет қаралды 194,380

Күн бұрын

Automatically combine multiple excel sheets into one using Power Query.
🔥Take our in-demand Power BI for Business Analytics course: www.careerprinciples.com/cour...
🆓 DOWNLOAD the Free Excel file for this video: careerprinciples.myflodesk.co...
In Excel you often need to combine multiple excel sheets into one. For example, you might have monthly sales tabs that you need to merge into one large sheet, or multiple excel files that you need to consolidate into a master file. Yet 90% of Excel users don't know how to do it. That's why in this video I'm going to show you a simple, automated solution to this problem using Power Query that will save you hours of tedius work. You might think of copy pasting as a solution, but this doesn't update if the original dataset changes. Same thing goes for the vstack formula. Instead, using Power Query, we'll be able to do this in no time.
LEARN:
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
🔥Power BI for Business Analytics: www.careerprinciples.com/cour...
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
🚀 All our courses: www.careerprinciples.com/courses
SOCIALS:
📸 Instagram - careerprinc...
🤳 TikTok - / career_principles
🧑‍💻 LinkedIn - / careerprinciples
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - Using Copy & Paste
1:15 - Using the VSTACK formula
2:30 - Combining Multiple Excel Sheets
6:58 - Combining Multiple Excel Files
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.

Пікірлер: 104

@KenjiExplains 4 ай бұрын

🔥Take our in-demand Power BI for Business Analytics course: www.careerprinciples.com/courses/power-bi-for-business-analytics

@mohammedkhaledhossain7374 4 ай бұрын

Kenji Can you do a video, showing how to extract figures coming from outlook excel attachments to your excell spreadsheet. For example everyday I recieve an excel attatchment on outlook, and from that attatchment I copy and paste the figures onto my speadsheet. Can this be automated. Plz can you show if this is possible

@aracelidreitzler7220 2 ай бұрын

Omg! I love how you explain! Step by step. Thank you❤

@noyondas6925 3 ай бұрын

You make complex things easy for us with best presentation skills. You're great !

@durgaverma3563 2 ай бұрын

I love it when you say "Alright!, Back to the Video😄" after talking about your Course. Been binge watching you for the last two days and learned a lot of things in a short time :) really thankyouu sm ^^

@fofangfizz 2 ай бұрын

This video was super easy to follow. EXTREMELY helpful! Thank you!!!

@user-ef9tl7vw8i 2 ай бұрын

Finally a clear explanation! Thank you very very much!!!

@yohannaagbanaji8012 4 ай бұрын

Great job Kenji. Keep it up.

@akinsojiakindele2391 4 ай бұрын

Great job Kenji.Thank you

@finalmale 3 ай бұрын

Your teaching style and method is awesome 👍

@nickspinnler3929 6 күн бұрын

Thanks kenji you’re really a life saver. I’m a beginner IB (investment banker not irritable bowel LOLZ) ant I am learning a lot. Even though chat GPT can literally do all this hahahaha (job will be obsolete LOLZ). but my firm doesn’t let me use it LOLZ, so thanks Kenji 🎉

@TheTechnicalAccountant 4 ай бұрын

Awesome vid as always! Thanks Kenji!

@jtmh31 9 күн бұрын

The "From Folder" query is simply the best. I use it all the time where I need to combine data from multiple files, or I want to change the data from similar files for reporting.

@user-rj8bw7vf3q 3 ай бұрын

You by far have the best tutorial videos. Thank you for providing so many helpful videos.

@KenjiExplains 3 ай бұрын

Thank you !

@camlex6310 4 ай бұрын

This is really helpful!! Thanks

@villarantejhunnar8761 2 ай бұрын

OMG new subs here sir. thank you for this tutorial. it save me alot of time! more power sir! love from ph!

@agboolasherifdeen7760 4 ай бұрын

Thanks for this awesome video.

@muddasar3060 20 күн бұрын

Very informative tht really help me solve my recent requirements

@owkpriyanka4269 Ай бұрын

very helpful, thanks!

@1wz2mny1 3 ай бұрын

Thank you for your videos, the problem I am having is that I want to pull a daily csv into a folder, but it has the same name every time, would you suggest renaming when I pull it into the folder? Or is there a better way?

@user-rm4cg2zn6x 2 ай бұрын

Good Afternoon Sir, Excellent, Excellent, Excellent.

@muddasar3060 20 күн бұрын

Great effort... Thumbs up

@GeardoMijausseMaguiavasteven 2 ай бұрын

Good tip. I liked that

@hosseinaghamohamadi3887 4 ай бұрын

it was great thanks

@oreoluwababatola8551 Ай бұрын

Thank you so much

@nihayatsabah3063 3 ай бұрын

Very useful Thank 🌷👏👏🌷🌷

@samjonk5012 2 ай бұрын

Hi kenji, Thank you for this clear explanation! My ex husband was a BI consultant and I have watched many times, but it seemed always very complecated. Now I actually need the knowledge. You are working in this example with sheets and files, my source is on the web. I have tried to merge them, but it didn't work out the way I wanted. Would this also work for 2 files from the web? Thank you again and hope to hear from you. Kind regards, Sam

@abdulhamidalhaddadi2255 Ай бұрын

Thank you dear

@laurensc1302 3 ай бұрын

These days almost all files in a professional setting are stored on shared (Sharepoint)drives. This makes it significantly harder to do this. It took me a while to figure out. It would be amazing if these type of videos touch on that or show that. But thanks a lot for the video many will benefit from this!

@JohnYoga 3 ай бұрын

This is exactly what I was going to touch on. I do not have the ongoing monthly updates all neatly in one file location as an Excel workbook. I have monthly updates, that come in as separate month files in a Sharepoint location. I need to stack each of these every month.

@JJ_TheGreat 2 ай бұрын

@JohnYoga Just curious whether you use OneDrive? Maybe you can link to the files through that (?)

@JohnYoga 2 ай бұрын

@@JJ_TheGreat thanks for responding. They are on different Sharepoint locations.

@la_gatalina 3 ай бұрын

Hi Kenji, for work, I have used the "Combining Multiple Excel Files" method you presented. However, the styling of entries gets lost (half of an entry is bold, the other half is in regular font). How can I maintain the integrity of the data?

@jamesbuckley9091 4 ай бұрын

Hi Kenji, can I combine only a certain tab within multiple excel workbooks using this method?

@trstreet61 2 ай бұрын

Thanks!

@abdulhamidalhaddadi2255 Ай бұрын

Thank you

@gbadamosirilwan8628 Ай бұрын

Thanks a lot @kenjiExplains. I have being able to get my work done within minutes. I really grateful. Please can you also make a tutorial for us on how to consolidate multiple excel workbook with multiple sheets into one. Thanks a lot.

@Nerijus.. 4 күн бұрын

@kenjiexplains can you able to make Monthly Financial Model Actual vs Budget, as well year to date, full year analysis

@orleanralia3297 Ай бұрын

thank you

@antopulickal7107 4 ай бұрын

Very helpful, Thank you. Could you please upload a video on how to convert pdf file and multiple pdf files to excel? If you could use bank statement pdfs it would be really helpful...🙏🙏

@fermion4945 2 ай бұрын

Hi kenji ,can I use append query to do the same ?

@arjunmahajan2537 3 ай бұрын

Bro you are awesome

@cm00000 4 ай бұрын

Im having issues in power query where if some columns i need to combine from multiple sheets are formula-based, not values. Power query sometimes import those columns and return entries as null if i dont open the excels first to recalculate and save. Is there a workaround so i wont have to open reclaulate and resave excel?

@faroufakmdps Ай бұрын

good job man

@RaviKumar-gr5lo 27 күн бұрын

It's great. Only problem I have is 12 months data streching upto 3lakh rows. May I know What is the maximum rows power query handles?

@JJ_TheGreat 2 ай бұрын

2:29 Yes, Power Query is the best!... Now, another way I thought of was Copy -> Paste Special -> Paste Link. Does this have the same problem as the other methods? Thanks.

@sandilembhele2515 4 ай бұрын

Very well explained. Thanks, Kenji.

@kwekubonful8620 4 ай бұрын

Hi Kenji, to merge the excel sheets into one, should all the sheets have the same number columns? And should the columns be arranged in the same order?

@worldofdata 4 ай бұрын

Order is not required and if there are more column, power query will add unique columns and show relevant data in each col. For instance, if one sheet contains sales person and other dont, the merged data will show sales person col with null of the sheet where there is no column and show data for sheet where data is available. hope it answers your question

@montebont 2 ай бұрын

@@worldofdatawhich kind of makes it a complete mess...doesn't it?

@HemantVerma-yu8rn 29 күн бұрын

Can be done this for google sheets as well

@JakeSteen00 3 ай бұрын

Is there a way to exclude hidden tabs, when consolidating ‘all’ tabs into one summary tab?

@bhuvaneswarivenkatesan3946 3 ай бұрын

Hi Kenji, thanks for ur useful video. I have a doubt . If the total number of rows exceeded 1048576, whether power query will split the data to the next sheet? Can u tell how to do that please.

@montebont 2 ай бұрын

You should not have such a large number of rows in Excel...Best use a proper high volume database...

@AkashKumar-if7yr 3 ай бұрын

Can you create a video on "How to create a Stock Research Report on PowerPoint" and convert into pdf or word

@muhammaduhamzayalwa1150 Ай бұрын

I wish you the best kanji

@reddot2342 2 ай бұрын

hi ... how if i want to merged some data consist of > 1mio row each ?

@olukunleayobamipetrosagboo6164 3 ай бұрын

Hello Kenji, can u recommend acceptable professional exam on Power Bi

@KenjiExplains 3 ай бұрын

Hey honestly not familiar with any professional exams sorry!

@dishamangalorkar5867 7 күн бұрын

Can we create multiple excel files from one excel sheet / data?

@AbdulRahman-yp6oy 4 ай бұрын

Sir please make youtube video on Power BI

@willzinner8813 4 ай бұрын

can you make a tutorial just on power query as well?

@KenjiExplains 4 ай бұрын

I already have that here: kzfaq.info/get/bejne/d9Jgqcx3qdeynmg.html

@NguyenQuang-ky5ux 4 ай бұрын

If I buy an access bundle course, can I activate this power bi course in video?

@KenjiExplains 4 ай бұрын

hey there, please send us your course queries at info@careerprinciples.com :)

@NguyenQuang-ky5ux 4 ай бұрын

Is it all the courses I can access?

@NguyenQuang-ky5ux 4 ай бұрын

What I mean by asking is All access annual subscription, it is all the course?

@CANishchy 4 ай бұрын

Comment before watching.its a learning dose 😌

@KenjiExplains 4 ай бұрын

Love it!

@swarajchillal891 4 ай бұрын

What if the columns in each month differ in numbers? However, the column names do match and we want only specific columns with the same name to be kept in our data. How to do it?

@worldofdata 4 ай бұрын

we can use power query Colum name function to filter only relevant columns

@SuperArnie 4 ай бұрын

In PowerQuery you can click on the header of any columns you want to exclude from the resulting table and click on “‘Remove Columns”. PowerQuery has a lot more to offer in regard to cleaning and consolidating data. Try it! Look at the ribbon and the context menus for columns, lines and other objects, click or right click respectively and play around while watching the table showing for the effect of what you’re doing. This hands-on approach will reveal many of the basic functionality.

@muhammadkamranali3586 22 күн бұрын

Hi, my name is Kamran and I am based in Dubai. I am watching your videos and impressed with your excel skills. I was wondering if you can help me with one formula to automatically calculates cost of shares sold on First-in-First-out basis. I regularly trade in stock market so volume of transaction is quite large. Hence, it is not possible to manually check cost of each share. Hence, I am looking to automate my file, however, I am not able to arrive at cost of shares sold. I took help from AI tools, watched youtube videos etc but remained unsuccessful. Problem comes if I make a split purchase and a split sale i.e. first buy quantity of any share on different dates and then sell the quantity in small chunks on later dates. I would be grateful if you have or you can suggest any solution for this. KR

@AbrahamPanicker_AB 17 күн бұрын

I can help but need to know exact requirements

@muhammadkamranali3586 16 күн бұрын

@@AbrahamPanicker_AB Sincere thanks for your reply. I am looking for calculation either in Excel or Query that can calculate cost of shares sold on earliest purchase basis. For example i purchase 500 shares on 1st Apr and 500 shares on 2nd April. Now: if i sell all 1000 shares on 3rd April, formula should add the cost of both earlier purchase. If i sell 500 on 3rd and 500 on 4th, formula should first pick cost from 1st Apr and then 2nd Apr. But If i sell only 700 shares on 3rd Apr, formula should first pick cost of 500 shares from first purchase, then proportion cost of 200 shares from 2nd purchase. The remaining 300 unsold appear as closing quantity with proportionate cost of 200 shares from the 2nd purchase.

@leonardoo6150 Ай бұрын

What if I get new data every month but it's just new financials, so I want the new data to be mapped to a new column to the right every subsequent month

@praveenan4717 4 ай бұрын

❤❤

@nicolette3436 3 ай бұрын

The combine and load did not work for me

@margerymartin3313 2 ай бұрын

Why is it when I close and load, only 537 rows appear instead of the full data set? Can anybody help please? :(

4 ай бұрын

Kenji is the real MVP

@tramynguyen2246 3 ай бұрын

Why my query only loaded two sheets. I have 8 sheets total 😢

@smr406 4 ай бұрын

❤

@KenjiExplains 4 ай бұрын

Cheers!

@colewest2259 4 ай бұрын

You should make a video to teach us how to use a macro to record the date and time when you last saved your excel workbook.

@jsimmons834 Ай бұрын

So not a trick with Excel, but rather incorporating Power BI. Made it simple tho.

@providencewilliams8064 4 ай бұрын

But in other to achieve this, all three sheets must have equal number of columns and same labels.

@Horn_of_Afrika 2 ай бұрын

Yes, otherwise u have to use the merge function

@distilledgogilba 3 ай бұрын

Test

@sssjjjj5475 4 ай бұрын

Power query is far easier I think and will work on an infinite amount of sheets

@jon99long 4 ай бұрын

Nah. I think he should use power query instead.

@laudnunoo1915 4 ай бұрын

😂 when you don’t watch the full video…

@preciousheart9522 2 ай бұрын

Will you teach me 😢

@montebont 2 ай бұрын

Excellent example of very poor table design / data structuring. Put ALL data on a single sheet like you would do in a proper well designed database and use queries / filters to extract subsets... It's as easy as that.

@ikigaipyri 4 ай бұрын

🎉😂😢😮😅

@KenjiExplains 4 ай бұрын

hahaha lots of emojis XD

@amh8989 3 ай бұрын

Dear Mr.Kenji, I appreciate you due to creating such an informative content. You are really a great teacher. Unfortunately, I can't download the excel files of your tutorials recently. Despite facing the message " Thanks for downloading (might take a few seconds)" right after writting down my email address, I do not recieve any file

@KenjiExplains 3 ай бұрын

Please check your downloads folder in your computer/browser

@finalmale 3 ай бұрын

Your teaching style and method is awesome 👍

@KenjiExplains 2 ай бұрын

Glad you think so!

@dinog.8606 3 ай бұрын

Would it be possible to extract the Power Query in a different format so one can create a Pivot Table and summarize all those monthly reports and avoid using Power BI?

@JJ_TheGreat 2 ай бұрын

I am not exactly sure what you mean, but you could Close & Load -> "Close & Load To..." -> PivotTable, instead of a regular table.