thank you so much for your instructional video. After updating the tab name to be consistent across different workbooks, the consolidation worked! massive thanks to you!
thank you hugely for this video. i've been trying to find a solution for this for 2 years. consolidate, add ons, etc. did not work for the data i have. this works just fine (with a few modifications). thank you very much!
Hello, Can we not consolidate the data in the same Excel workbook? And the consolidated sheet dynamically updates when data is added / changed to other sheets
Thank you for this video! After figuring out how to hide rows, I was able to (finally) import data from 5 worksheets and have a functioning query! 🙌🙌🙌🙌
Man found this to late as last week I had to combine 10 workbooks with 30 sheets each. So had to rewrite my older macros that combined the sheets and added a loop for the workbooks in a folder. Which took me a lot longer then using a combination of this method.
If I make a table in a single sheet on a new file retrieving the data from a file with multiple sheets (one table per sheet with the same structure) and then I receive a new updated file with many more rows in every table, what can I do? If I substitute the previous file with the new one, in the same directory and with the same name, can I use the "refresh data" button? Will it work? Or do I have to do all this procedure again? Thank you for your video, not even Microsoft guide could understand I needed to do this exact thing you showed
Really helpful thanks! I have a folder with a lot of file and every day i have new files, can i load only the new files that i never loaded knowing that old file will not change and doesn't need to be refreshed every time i load a new file 😊 ?
Hiya, can you help me. I have a sheet with 20,000+ items listed. Lets call it the master sheet. From the master sheet I have saved a copy to work on different series within it and used filters to only work on what I need. I need to merge the second sheet into the master and it needs to slot back into its original order. Will this method work? Not sure if I have explained this very well. TIA
I have 40 worksheets I'm trying to combine. When I get to the step of expanding data the result is a lot of "null" cells that should have data in them. Any thoughts?
this is a good vid, BUT it is unclear to me if the 4 regions are 4 sheets in a workbook called Regions or 4 separate files. Could you pleas clarify? I unzipped the download, but that didn't match what you had. Please Clarify. Thank you
I would like to know how to have multiple tabs but have a summary page with all the data calculated together. I looked at this but it is not helping me obtain my objective.
trying to combine multiple worksheets but I dont have a get data button, using excel 2016. My only options are Get from Access, From web, From text, SQL server, etc etc but nothing that lets me choose from a folder.
I have a query, but the source data is spread across multiple tabs and multiple worksheets and the tabs names can not be changes is there something i can do?
How to create a reference to the same cell range on multiple worksheets? Not everything in the spreadsheet, but only certain cells data into one. I need to create a list from hundreds of quotes sent to clients. I only need to pull their info and quote number to one list in excel in order to keep track of the quotes sent out. No one bothered to keep track before I started the job and I would like to implement this for myself.
Although helpful, this video just goes a bit too fast and I think covers quite a lot in a short time. I got lost at the "Applied Steps" stage in Power Query. If you simply want to merge lots of worksheets into one, without changing any columns etc etc, it would be really helpful, and possibly a clearer structure, if the tutorial simply explained that, before moving onto more complex things, such as performing the same operation but removing columns, etc.
I am trying to achieve the same but the problem is that my source file also is generated from a system and thus source file have random number of worksheets.
Please make a video on this. I have a sheet which contains data in rows and columns. And there are 4 blank rows after every data row and I want to fill the 1st blank row with the data of sheet 2 and 2nd row with sheet 3 and 3rd row with sheet 4 and 4th row with sheet 5 please make a video it's getting very difficult
Hi Jamie. I am having the problem with asking the correct question and I’m trying to reverse engineer stuff. Not working. I am so frustrated that I think because I’m not “subscribed” to Excel that MS has my Excel omitting or retarding its abilities. Or I’m stupid which I’m not. I have 200 worksheets filled out as “Form Sheets” and I’d like to make a seperated Sheet and pull the forms from all 200 in a common column so I can compare each sheets items. I miss dBase. That’s what I want to do and just can’t figure out what they changed what I want. Every query is like trying to combined the sheets, if I use Concat then I have F2+F9 each cell (4000)… I may as well type it at that rate. Macro I did went stupid half the time (where I get suspicious) It’s like taking a dBase form file and turning it back into data file I feel like I should’ve done it as data setup now but it’s about 8 pages per sheet and I’m trying to save my keystrokes tho I’m getting close to where I should’ve stopped. I didn’t get away from Excel for that long that the changes are stunting me…
I was trying to combine 10 files each with 5 sheets, my target is to get the info onto one file on one sheet BUT the table must be in the order of each file. FAILED !!!! Any advice, please... 🙏
The problem is that THAT merge is UNUSABLE once you move that result excel file to another pc - share it.... as the source will not be found anymore. useless ... viva la Google Sheets !!!
@YGX29 which is done automatically every time without asking or having an option to opt out? And then the very one reason to have the data being updated is gone? Pathetic... Go Google, that's the way. Microsoft doesn't care
The subtitles appearing at the bottom of the video tutorial get in the way of the actions being performed and renders the video useless. Please remove. Thanks!
Subtitles are a UA-cam feature which means you'll have to hover your mouse pointer near the bottom right 1/3 of the youtube display . You should see an icon that looks like a sprocket ( left click and) that opens the settings menu. you can turn off the computer generated caption/subtitles with additional left mouse clicks .
I had to do some slight of hand to get my particular use case to work, but your instructions helped me down the path. Thanks!
Glad it helped!
Thanks!
Welcome!🙏
thank you so much for your instructional video. After updating the tab name to be consistent across different workbooks, the consolidation worked! massive thanks to you!
This is a wonderful tutorial. I was working on a project for combining data manually, which was a long and tedious process.
Super helpful! I had to merge 52 weeks of csv files (1 per week), so this helped massively. Thank you.
great video!! This is a super useful tool!
This is so much more simple than the Pivot tables I have been using. Thank you!
Thank you for your clear and quick instruction. So nice to not have to listed to a ton of jibberish, first. Thanks again!
Love it, thank you so much! You safed my day!
Brilliant 👏
Thank you . I enjoyed your class.
wouah! thank you!!!
Amazing! Just combined 250k lines that were initially spread across 28 tabs. Great help, thanks very much.
great thanks for sharing! Very useful!
thank you hugely for this video. i've been trying to find a solution for this for 2 years. consolidate, add ons, etc. did not work for the data i have. this works just fine (with a few modifications). thank you very much!
Hello,
Can we not consolidate the data in the same Excel workbook? And the consolidated sheet dynamically updates when data is added / changed to other sheets
Thanks a lot. It’s really really helpful! ❤
Thanks 🙏
Thank you so much for the straightforward and clear tutorial!
It works! Saved a lot of time. Thank you so much!!!
Great and simple explanation
Thanks for the wonderful guide! It works!
Very helpful and elaborate! Thank you for teaching and saving my time! :)
very good explanation
Thank you very much for your tutorial. Will it work for rows more than 1 million?
Thank you for this video! After figuring out how to hide rows, I was able to (finally) import data from 5 worksheets and have a functioning query! 🙌🙌🙌🙌
Could you please tell me which office you used this to combine multiple sheets into one sheet ?
Woooo thank you so much!!! It is almost as Power BI. I'm. going to $$$ for that.
thanks a lot sir.
Man found this to late as last week I had to combine 10 workbooks with 30 sheets each. So had to rewrite my older macros that combined the sheets and added a loop for the workbooks in a folder. Which took me a lot longer then using a combination of this method.
super helpful
Adorable ❤
Thanks for the clear demo and explanation. Very helpful 🙏🙏🙏
Thanks for the valuable video, done subscribe. How could we do if the data contains pictures in product, how PQ handle data contains picture
Is there a way to add additional data to certain fields based on another excel file?
I tried to do this following the video guide but I keep getting error message via Query "column data of the table wasn't found"
If I make a table in a single sheet on a new file retrieving the data from a file with multiple sheets (one table per sheet with the same structure) and then I receive a new updated file with many more rows in every table, what can I do? If I substitute the previous file with the new one, in the same directory and with the same name, can I use the "refresh data" button? Will it work? Or do I have to do all this procedure again?
Thank you for your video, not even Microsoft guide could understand I needed to do this exact thing you showed
Really helpful thanks!
I have a folder with a lot of file and every day i have new files, can i load only the new files that i never loaded knowing that old file will not change and doesn't need to be refreshed every time i load a new file 😊 ?
Thank you
Many thanks
I added some columns to the source files in order to get additional detail, how do I get these columns to carry over to the power query?
you gonna save my day :D
Hiya, can you help me. I have a sheet with 20,000+ items listed. Lets call it the master sheet. From the master sheet I have saved a copy to work on different series within it and used filters to only work on what I need. I need to merge the second sheet into the master and it needs to slot back into its original order. Will this method work? Not sure if I have explained this very well. TIA
Will formulas come over or will that create an error?
Thanks Jamie.
You’re welcome!
I have 40 worksheets I'm trying to combine. When I get to the step of expanding data the result is a lot of "null" cells that should have data in them. Any thoughts?
Use filter
this is a good vid, BUT it is unclear to me if the 4 regions are 4 sheets in a workbook called Regions or 4 separate files. Could you pleas clarify? I unzipped the download, but that didn't match what you had. Please Clarify. Thank you
I would like to know how to have multiple tabs but have a summary page with all the data calculated together. I looked at this but it is not helping me obtain my objective.
Is there a way to do this, but have is different colors? E.g. West ones are green; east ones are blue; south ones are red
possible to do this in excel 2016?
trying to combine multiple worksheets but I dont have a get data button, using excel 2016. My only options are Get from Access, From web, From text, SQL server, etc etc but nothing that lets me choose from a folder.
It's new query button for the required data button
This query seems to be rounding numbers. Any idea how to stop the rounding?
I have a query, but the source data is spread across multiple tabs and multiple worksheets and the tabs names can not be changes is there something i can do?
How to create a reference to the same cell range on multiple worksheets? Not everything in the spreadsheet, but only certain cells data into one.
I need to create a list from hundreds of quotes sent to clients. I only need to pull their info and quote number to one list in excel in order to keep track of the quotes sent out. No one bothered to keep track before I started the job and I would like to implement this for myself.
Any way to combine multiple excels in multiple tabs in one workbook?
how do you do this in the same work book?
Although helpful, this video just goes a bit too fast and I think covers quite a lot in a short time. I got lost at the "Applied Steps" stage in Power Query. If you simply want to merge lots of worksheets into one, without changing any columns etc etc, it would be really helpful, and possibly a clearer structure, if the tutorial simply explained that, before moving onto more complex things, such as performing the same operation but removing columns, etc.
I am trying to achieve the same but the problem is that my source file also is generated from a system and thus source file have random number of worksheets.
Please make a video on this. I have a sheet which contains data in rows and columns. And there are 4 blank rows after every data row and I want to fill the 1st blank row with the data of sheet 2 and 2nd row with sheet 3 and 3rd row with sheet 4 and 4th row with sheet 5 please make a video it's getting very difficult
Can I use vlook up formula
Literally sitting at my desk in shock....Combined over 20k lines. I could cry rn
Hi Jamie. I am having the problem with asking the correct question and I’m trying to reverse engineer stuff. Not working.
I am so frustrated that I think because I’m not “subscribed” to Excel that MS has my Excel omitting or retarding its abilities. Or I’m stupid which I’m not.
I have 200 worksheets filled out as “Form Sheets” and I’d like to make a seperated Sheet and pull the forms from all 200 in a common column so I can compare each sheets items.
I miss dBase. That’s what I want to do and just can’t figure out what they changed what I want.
Every query is like trying to combined the sheets, if I use Concat then I have F2+F9 each cell (4000)… I may as well type it at that rate.
Macro I did went stupid half the time (where I get suspicious)
It’s like taking a dBase form file and turning it back into data file
I feel like I should’ve done it as data setup now but it’s about 8 pages per sheet and I’m trying to save my keystrokes tho I’m getting close to where I should’ve stopped.
I didn’t get away from Excel for that long that the changes are stunting me…
Can someone show how to do this on a Mac? It does not match.
I was trying to combine 10 files each with 5 sheets, my target is to get the info onto one file on one sheet BUT the table must be in the order of each file. FAILED !!!! Any advice, please... 🙏
I do not have commands Get data and From file.
What if there is an error?
The problem is that THAT merge is UNUSABLE once you move that result excel file to another pc - share it.... as the source will not be found anymore. useless ... viva la Google Sheets !!!
Maybe, we have to break the connection?
@YGX29 which is done automatically every time without asking or having an option to opt out? And then the very one reason to have the data being updated is gone? Pathetic... Go Google, that's the way. Microsoft doesn't care
Note that this doesn't work on MacOS version of Excel
The subtitles appearing at the bottom of the video tutorial get in the way of the actions being performed and renders the video useless. Please remove. Thanks!
You’re able to turn them on or off.
Subtitles are a UA-cam feature which means you'll have to hover your mouse pointer near the bottom right 1/3 of the youtube display . You should see an icon that looks like a sprocket
( left click and) that opens the settings menu. you can turn off the computer generated caption/subtitles with additional left mouse clicks .