This is brilliant! Thank you so much. You enabled me to do something (combines multiple sheets in multiple files) that I just hadn't ever done before. Not sure I 100% understand it now, but it works! Fantastic. Thank you, again.
How about adding a new column in the combined table to show the regions? Do I have to add it manually before I combine them? Is there any automate way to adding the region data into column?
Fantastic video, had compliments from the boss. One thing though, supposing I add new columns to individual tables and then refresh the combined table and those new columns don't appear? That is the problem I am now having.
Great tutorial. I have multiple workbooks with multiple tabs. Is there a way to combine all the tabs of all the workbooks into one spreadsheet? Given that they all have the same headings.
This works pretty well with one question: I end up with duplicated columns. Ex. Sheet 1 with a table columns First Last ID Sheet 2 First Last ID When I do the query, my output creates TWO Last columns, with null data in some of them, where the source tables each only have one Last column containing data.
Any way of keeping the name of the sheet in the resulting table? (Say from your example, each row would retain the region name from the original sheet).
How about if each excel sheet represent daily data, say stock prices? So each tab has same rows, say 100 stocks. And same 4 columns - open, high, low, close. Cells values are different. What is the best way to handle this?
Thanks! it working in client version but how can I resolve in Sharepoint sheets? I think most people have already use online version in 2024. Microsoft should develop all the features/functions in client version into Sharepoint Excel Workbook.
thanks this really helps, i did not understand after consoldidating data then u added wals excle sheet, so instance i have 2 work sheets and i consolidated the data in one sheet then if i want to add the third sheet how to do it, it became little confusing . please can you make a video with say 2 worksheets which you want to combine and then want to add 3rd sheet then how to do it.
PLease If you can make a video on how to insert table in all worksheet in one go it will be very useful. thanks or if someone from the comments section can can tell me i will be very thankful
Hi Chester, could you teach us how to combine data from all sheets (each worksheet represents every Employee's assigned jobs Ex: Sheet1 for Employee A's assigned jobs, Sheet 2 Employee B's assigned jobs) into one Master sheet (All Employees' assigned jobs) in Excel 2016. Thanks a lot
I found no instruction on the web concerning keeping the cells formatting as they are in the original data sheets. I have cells with different currencies. The choice of the currencies are done by drop-down lists for each row and applied to different cells on the same row using conditional formatting. I loose the currency formatting and the conditional formatting of all cells when using Power Query. Can anybody help please?
I just made my consolidated sheet and after messing around with it a bit I found that I'd have to refresh the query/data regularly while being mindful of the tables ticked under source (idk if i just haven't explored it enough but that workaround is okay with me and still heaps of help in terms of efficiency)
You just helped me combine 67 sheets into one lovely table. THANK YOU!!!
when I go to the blank query and when I enter the format =ex or =excel don't get the drop-down as u got to select the current workbook..any fix
This is brilliant! Thank you so much. You enabled me to do something (combines multiple sheets in multiple files) that I just hadn't ever done before. Not sure I 100% understand it now, but it works! Fantastic. Thank you, again.
I have tried to do this using VBA for 2 hours, and you solved my issues... Amazing!! Thanks a lot
How about adding a new column in the combined table to show the regions? Do I have to add it manually before I combine them? Is there any automate way to adding the region data into column?
Great tutorial. I had struggled with others, and this was the one that got it done! Thanks
Oh god thank you for this...Exactly what I wanted to do but had no chance of figuring out on my own!
Fantastic video, had compliments from the boss.
One thing though, supposing I add new columns to individual tables and then refresh the combined table and those new columns don't appear? That is the problem I am now having.
Thanks for the clear and helpful video! Your explanation of the formula really helped me solve my problem
Great tutorial. I have multiple workbooks with multiple tabs. Is there a way to combine all the tabs of all the workbooks into one spreadsheet? Given that they all have the same headings.
This works pretty well with one question: I end up with duplicated columns.
Ex.
Sheet 1 with a table columns
First
Last
ID
Sheet 2
First
Last
ID
When I do the query, my output creates TWO Last columns, with null data in some of them, where the source tables each only have one Last column containing data.
I am facing a issue while running the query, it is not taking the =excel.currentworkbook command . Can you help ? @chester
My workbook has 258 sheets. Creating a tablet for all them seems pretty work intensive. Is there a better way to combine all my sheets into one?
Any way of keeping the name of the sheet in the resulting table? (Say from your example, each row would retain the region name from the original sheet).
You win! Saved me a couple hours today 😊
How about if each excel sheet represent daily data, say stock prices? So each tab has same rows, say 100 stocks. And same 4 columns - open, high, low, close. Cells values are different.
What is the best way to handle this?
This has been super helpful! Thank you so much!!!!
Why can't I like this video twice ★★★★★
Thanks! it working in client version but how can I resolve in Sharepoint sheets?
I think most people have already use online version in 2024. Microsoft should develop all the features/functions in client version into Sharepoint Excel Workbook.
Thank you!!! Exactly what I needed ❤
It's been of great help! Thank you!
thanks this really helps, i did not understand after consoldidating data then u added wals excle sheet, so instance i have 2 work sheets and i consolidated the data in one sheet then if i want to add the third sheet how to do it, it became little confusing . please can you make a video with say 2 worksheets which you want to combine and then want to add 3rd sheet then how to do it.
Thank you for this video Chester. A great tutorial!!!!
Thanks Ivan
Good day. After I import the data, and have to select the Kind button. It doesn't give my an option to select tables. Where am I making the mistake?
must save this video. thank u very much. i feel so smart :)
thank you SO MUCH this was extremly helpfull thank you
Big thanks for the tutorial!
Hi, what about combining sheets from different workbooks together in one sheet pls?
If we have nonadjusted column names, what will we do to combine them?
PLease If you can make a video on how to insert table in all worksheet in one go it will be very useful. thanks or if someone from the comments section can can tell me i will be very thankful
This was very helpful!! Thanks!!
Hi Chester, could you teach us how to combine data from all sheets (each worksheet represents every Employee's assigned jobs Ex: Sheet1 for Employee A's assigned jobs, Sheet 2 Employee B's assigned jobs) into one Master sheet (All Employees' assigned jobs) in Excel 2016. Thanks a lot
Thank you 🙂🙂🙂
Thanks very much. Exactly what I was looking for !
Chester to the rescue!
Yhoo i trying to do this but other data sources is not active
I am trying to do mine but I got null values any help pls
I found no instruction on the web concerning keeping the cells formatting as they are in the original data sheets. I have cells with different currencies. The choice of the currencies are done by drop-down lists for each row and applied to different cells on the same row using conditional formatting. I loose the currency formatting and the conditional formatting of all cells when using Power Query. Can anybody help please?
Thank you, thank you, thank you!!!!
What if it a workbook that the individual sheet are updated daily? Will the “master” all region sheet update as well?
I just made my consolidated sheet and after messing around with it a bit I found that I'd have to refresh the query/data regularly while being mindful of the tables ticked under source (idk if i just haven't explored it enough but that workaround is okay with me and still heaps of help in terms of efficiency)
Is there a way for the “master” sheet to update automatically as more data is added or removed from the tables in the other sheets?
Amazing
Thank you!!
What if you wanted to combine from multiple workbooks?
Top notch! Cheers.😃
Thank you for sharing.
Is it possible to keep the cell font color and underlines?
Thank you!!!
After all these years you would figure that there would be a quick function in the easily combine excel tabs into one tab.
WoW....!!!
fantastic, thank you!
You're very welcome!