Easiest way to COMBINE Multiple Excel Files into ONE (Append data from Folder)

In this detailed tutorial, you'll learn how to consolidate data from multiple files using Get & Transform (Power Query) in Microsoft Excel. This process is invaluable when you need to gather and analyze data spread across various files. Perfect for those who regularly handle data in Excel, our video guides you through each step, ensuring you can efficiently combine information without the hassle.

This is the easiest way to combine multiple Excel files from a folder into one Workbook and create a table or a a pivot table report based on the consolidated data.

🔗 What You'll Learn:
▪️ Setting Up Power Query: Learn to connect Power Query to a folder containing multiple Excel files.
▪️ Filtering and Cleaning Data: Discover how to filter out unnecessary files and clean up your data for accurate analysis.
▪️ Combining Data: Understand how to merge data from different files into one comprehensive dataset.
▪️ Creating PivotTables or Excel Tables: See how to transform your consolidated data into a PivotTable or a standard Excel table for further analysis.

00:00 Consolidate Data from Multiple Excel Files
01:28 Get & Transform Data From Multiple Files in a Folder
02:37 Merge and Append Data in Power Query
05:55 Clean Data in Power Query
07:10 Load Data as PivotTable to Excel
09:21 Load Data as Table to Excel
09:53 Wrap Up

Many of us send out templates to colleagues and are later faced with the challenge to somehow consolidate multiple Excel files. To append the data into a single Excel workbook can be quite a troublesome and lengthy process.

Sure, a solution for this has always been Excel VBA. But learning VBA takes time and it can be too difficult for a lot Excel users.

In this tutorial, I'll show you a much easier approach! We'll use Excel Get and Transform aka Power Query to combine data from multiple Excel workbooks. We will directly connect to a folder that contains the files and use the feature Combine Files in Power Query. Any time you drop an Excel file in that folder, the data will be appended and consolidated with the rest. You can then create one Excel table for the appended data or create a pivot table from the multiple Excel files.

The best part about using Power Query to merge data from Excel files is that you don't need to write any code! You just need to click a few buttons to append the data from the files.

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[wolfstream]

I love the way you explain and turn complex matters into easy ones. You have a very good syntax, perfect diction and a very comfortable voice tone. Excellent in all levels. Thank you!

[syairol]

I use to append by individual files. Its time consuming. This is a life saving solution. Append by folder. Thanks Leila. You're truly a excel gem

[DebbieKinsinger]

You helped me overcome the fear and resistance I've had to learning how to use Excel effectively. I didn't even know that Power Query existed, and suddenly a world a pivot tables, grouping, and aggregating opened up where I could easily conquer my problems without even needing to use a formula. I could weep for the years of frustration and struggle seeing how easy you made this for me, in a few minutes. It's not just the easy to follow logic and excellent presentation but also your tone of voice that reassures me at every step that "This is easy I can do it". Thank you.

[CANONinDEVIN]

Thank you! I could not figure out an error encountered after updating a transform sample file, but you addressed it right away w/ removing the "Changed Type" step.

[shadrackawunyo]

You are a gifted instructed. This video was loaded last week but I was not really enthused to watch. The reason is I have some knowledge on this subject already and do apply them often. I decided to watch it today and was blown out. You have an amazing way of teaching even familiar subjects clear enough to impact further knowledge and deeper understanding. I am definitely signing up for your PQ course

[phamhuyson]

Perfect! Everytime I got trouble, searching google and reach your video, my solution was there. I must love you Leila!

[sazopro]

You saved my life today! thank you so much! I'm a beginner in Excel and had work to do with a bunch of workbooks. Power Query made it easy!

[jeanpoulin]

Awesome, the Power Query course is coming!!!! I just can't wait to learn it from you Leila!!!

[Morgenj]

I always enjoy coming back to your videos if I need a refresher. You're an absolute superstar

[alexandriav]

I will forever go to your videos first when it comes to anything Excel related! I even watched your video on the Bing Chat and loved it! You're an amazing teacher and make your videos easy to follow and understand.

[joescanlonh]

I've been looking for this exact tutorial, to combine non table formatted data sets from multiple file with bare cells above the excel data headers. This will save me so much time, Thank you so much. Cheers!

[CJAKS]

This is the best video on Youtube that explains how to do this. Thankyou for this!

[myprovenacademy]

Leila is an awesome trainer. This looks like powerBI implementations tool

[Erethras]

Thank you. I had watched this video a million times, but finally came in handy at work. It has saved me so much time.

[jmoralesg]

Thank you Ms. Leila. Thanks a lot to help people to do better their office jobs.

[stephenstephen]

Great video as usual Leila. You're the best! Thanks!!

[cathyb]

Leila, you absolutely blow me away. I had no idea this was even possible.

[MrSupernova]

Pretty cool! I'm going to do this at work with about a 12 or so files that I must transform and combine every quarter. Currently, it takes upwards of 1 hour to do manually (its a lot of data). But I think if I use this method and save a template I can just refresh every quarter once I receive all the files for the project.

[YH-gphd]

Thank you! I had to pull together a few sale reports for a customer today, had find this tool very useful. However, I might have to practice this a few times to get it to work.

[dhanushthakral]

In love with your teaching and your sweet voice as well.... Enrolled for your excel