Import Multiple Excel Files with Multiple Sheets in Excel
Вставка
- Опубліковано 14 жов 2024
- In this video, we import multiple files that contain multiple sheets from a folder into one Excel table.
This is a hugely powerful technique, yet it is quick and easy to do with Power Query.
Download the files to follow along with the tutorial - www.computerga...
In this example, we have four Excel workbooks that contain six worksheets. We only need four of these sheets, so will filter two out in the process.
The data from all four sheets and all four files will be stacked into one table and then a PivotTable created from it.
Find more great free tutorials at;
www.computerga...
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 - Навчання та стиль
Searched high and low for such a clear and concise explanation to achieving this task where the data is non-uniform. I had 30 xlsx/xls files representing some annual data each with 22 sheets so not a task to attempt manually! Only problem I had was to get power query to recognise that not all sheets all had the same columns ... some had new columns introduced over the years and some columns had been discontinued but for an accurate record I wanted to keep all data. The way I achieved it in the end was to make a master list of column headings, replace the headings for the 'lead' file with the master and add dummy data in the empty columns. :) So thanks, one more subscriber found. Now to import the data (235,481 rows) into Access and start to analyse.
Nice work! Thank you. Power Query can take some fiddling to get the exact recipe you need for your situation.
Perfect, simple and just what I was looking for; so many of these youtube web cast on this subject always miss out on workbooks and only focus on a single sheet. Excellent job
Great to hear! Thank you 😊
Overwhelming, astonishing, convincing. A powerful weapon to solve problems. Thank you for this video Alan!
My pleasure, Iván. Thank you.
Brilliantly explained Power Query; much needed video on data consolidation thru PQ 👍
Thank you, Satish.
Very useful another string to add to the Excel bow. Thankyou🙏
My pleasure, Andrew 🏹
The Awesome Power Query Makes Life So Much Easier...Great Stuff Thank You Alan :)
It sure does. Thanks Darryl.
Awesome. Thank you ComputerGaga. This is very useful
Great to hear. Thanks Joshua.
@@Computergaga I have learnt a lot of excel skills from your lessons. Thanks again
Very helpful, good work! Thank you.
Thank you 😊
Very useful, i like Power Query. Thanks Alan!
I often have multiple csv files and need to bring them into 1 excel workbook but place each csv file on its own sheet. How may I do that? Thanks.
Very well presented, Alan
Many thanks, Pradeep
Excellent. many thanks youve really helped me
Great to hear. My pleasure.
Great video
Thank you, Paula.
I am your fan now!
Great! I hope I can help you further.
Nice vedio. Can i import data from several sheets of one workbook by this method?
Thank you. Sure, you can. This technique imported from a folder. But you can also do this by importing from an external workbook from the Data tab. you can select the sheets you want from a list using this method.
Thank you :)
No problem 😊
Very Good!
Thank you, Steven.
Why we need to do custom column? If we click on content the 2 arrows, it will not expand and show other columns of the sheet?
Hi, Thanks a lot for this clear video. I have Excel 2016, I can’t see as you the list of the formula. How I can get them ?
Thanks
Hi Valerie, the list you see as I type the formula I don't believe is available in Excel 2016. It is hard to remember all version differences. I believe all steps in the video are possible although the buttons may be in different places.
Computergaga Thanks a lot for your answer
Great one. Thanks.
Thank you, Chris. Hope you are well, buddy.
Computergaga Doung great. Just a mission to get the Channel up and running. But I will get there.
You certainly will. I hope to see you soon at another Excel event somewhere.
Hi Alan. Trying to download the files from the link above. Get a "Not found, error 404" error. Must be a broken address. Looking forward to following along whenever you fix the link. Thanks!!
Hi Wayne, all fixed. I typed a - instead of a . 😳
@@Computergaga Thanks Alan. Got the files, ran through the exercise, worked perfectly. Thanks for the lesson and for the sample files. Really helps with the learning to follow along and do the steps vs. just watching :)) Thumbs up!!
Absolutely. No worries. Apologies for the original error.
@3:00 Alan, what would happen if Combine or Load were chosen instead of Transform Data?
Load would load the content of the window into an Excel table (or other default load destination).
Combine would perform the merge.
By using Transform we have the opportunity to exclude files, or extract sheets etc before merging/combining.
@@Computergaga Thanks. Appreciate it.
My GetSheets column just has error on every line.
I am using macbook and I cannot find " from file" in excel
I don't use Macs so cannot confidently tell you if this can be done. Macs have been behind with Power Query updates.
Please send Excel link
I don't have these files around anymore.
... this doesnt keep the tabs separate!!!! HOOOOWWWWW :(
Sir pls help me sir maliyasia toto, 6d 6"numbers gusseing tommrow draw please reply to me
I got an Error :(
I DONT HAVE THAT OPTION TO DOWNLOAD FiLE AND THEN IT ASKS FOR DELIMITERS AND STUFFF AAHHHHH
Kindly use fonetic englush
Fantastic 😍💋 💝💖♥️❤️
Thanks 🤗