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!
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
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?
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
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
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.
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 😊 ?
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?
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! 🙌🙌🙌🙌
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.
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…
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.
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!
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.
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.
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
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
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 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!
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
This is a wonderful tutorial. I was working on a project for combining data manually, which was a long and tedious process.
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"
Could you please tell me which office you used this to combine multiple sheets into one sheet ?
Love it, thank you so much! You safed my day!
This is so much more simple than the Pivot tables I have been using. Thank you!
Super helpful! I had to merge 52 weeks of csv files (1 per week), so this helped massively. Thank you.
Is there a way to add additional data to certain fields based on another excel file?
Thank you for your clear and quick instruction. So nice to not have to listed to a ton of jibberish, first. Thanks again!
Thank you . I enjoyed your class.
great video!! This is a super useful tool!
great thanks for sharing! Very useful!
Brilliant 👏
Thank you so much for the straightforward and clear tutorial!
Thanks for the valuable video, done subscribe. How could we do if the data contains pictures in product, how PQ handle data contains picture
Very helpful and elaborate! Thank you for teaching and saving my time! :)
Thanks a lot. It’s really really helpful! ❤
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
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
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
wouah! thank you!!!
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?
Thanks for the wonderful guide! It works!
Will formulas come over or will that create an error?
It works! Saved a lot of time. Thank you so much!!!
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.
It's new query button for the required data button
possible to do this in excel 2016?
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 😊 ?
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?
This query seems to be rounding numbers. Any idea how to stop the rounding?
very good explanation
super helpful
Thanks for the clear demo and explanation. Very helpful 🙏🙏🙏
Great and simple explanation
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
how do you do this in the same work book?
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! 🙌🙌🙌🙌
thanks a lot sir.
Any way to combine multiple excels in multiple tabs in one workbook?
Thanks 🙏
Can I use vlook up formula
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.
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…
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.
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!
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.
Woooo thank you so much!!! It is almost as Power BI. I'm. going to $$$ for that.
Amazing! Just combined 250k lines that were initially spread across 28 tabs. Great help, thanks very much.
Can someone show how to do this on a Mac? It does not match.
you gonna save my day :D
Adorable ❤
Thank you
Many thanks
What if there is an error?
I do not have commands Get data and From file.
Thanks Jamie.
You’re welcome!
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.
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
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
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... 🙏
Literally sitting at my desk in shock....Combined over 20k lines. I could cry rn
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 .