Brilliant mate. I was on the right path, but getting stuck, and this clarified exactly what I needed. I'm going to do this first in excel, letting the business areas update their own data in locked-down sheets, then load the single appended file to Power BI for an extremely lightweight and quick report.

Thank you. You saved my life. Many, many thanks Obrigado. Salvaste a minha vida. Muito, muito obrigado

THANK YOU VERY MUCH! THIS WAS EXACTLY WHAT I NEEDED TO DO!!!

I wasted almost 3 hours and finally found your video. It is simply awesome. Thank you, you made my day..:)

This was great! After watching many other videos that just confused me, I was finally able to combine multiple excel sheets from your instructions. Some of my data across sheets were redundant so I thought that after appending them (combining them together) I'd have to go through and manually remove duplicates. To my surprise, either there weren't any duplicates to start (rare) with OR this process removed the duplicates, saving me from a lot of tedious work. Superb and many thanks!

That is stunning. Thank you Alan. A truly useful function. 👍 🌟 🌟 🌟 🌟 🌟

Allan, believe me when I see that my eyes nearly popped out of my head at 9:20 !! This is excellent info and exactly what I needed to combine two sheets containing future and historical timesheets for my team.

This is exactly what i needed. It takes me 2 hours to consolidate sheets on daily basis. Thank You so much. This is so easy and time saving.

This was one of the most useful excel videos I have ever seen in my 8 years as a finance professional! Thank you!

Thank you Alan. It's been very helpful. Greetings from France.

So great, i've been trying to this sheets appending for weeks.... this tip will help me a lot a lot. thanks

Right!!!!! I went through so many videos on Power Pivots, and then I find this one, in a couple hours, I get to build a beautiful Power Pivot Table. Thank you so much !!!

Subscribed and liked. Thank you so much for a video I needed for work!

Love the guys who make tutorials straight to the point like yours. I watched for 4 min and got what I needed Thanks man big time help.

Thank you!!! I return to this video year after year when setting up my yearly budget. So appreciate it! 3:44 Directions Start

Exactly what I was looking for! Thx a lot!

Well narrated! Incredibly helpful.

Still relevant over a year later! Thank you!!

Excellent instruction, Thank you!

Thank you. I learned a lot from this. From new raw data that I need to amend until I get what I want from huge multiple of worksheets. Combined and pivot has been made my life easier.. Head up, buddy!!!!

what a godsend..been wracking brains how to achieve this. This will be fantastic for our work with multiple users workbooks. Massive thanks

you just saved my life!! ahhh thank you so so much!

Brilliant! Exactly what I was looking for. Thank you.

Excellent. Thank you. The only useful video I found here on the issue

Hi Allan, thank you: well explained. One tip: add a new column to the queries: for exmaple: "France" , in this way you can later analyze per country. As to the question of Salam Miah: you can do this in regular Pivot: add the country in page filter and then you can make a report where all countries are displayed per sheet.

You BEAUTY. Iv been trying to find a method of combining a credit card and debit card tables/worksheets to track transactions for specific categories and sub categories...Finally.

Hi ! Great video, I am wondering what you would advise in terms of extra steps to perform, should each tabs contains different columns contents while the end goal is still to combine the specific columns data from the different tabs (in my case, i have for example 20 stock on hand reports from various locations, that have various different columns) as i am only interested in getting into the master tab: the location the sku is stored, the quantity, and of course the sku code). Thanks!!

So good! You talk slowly which is awesome!

Great Knowledge, Thank u so much.

Alan - another clear and most helpful video! Question though: wouldn't it be to your advantage (in terms of using a pivot table for analysis) to include the country name (UK, France, Spain, Italy) as an additional column for each individual table and then do the steps for an Append Query? I ask because once you have set up your worksheet that has ALL tables combined and then proceed to create a pivot table, you don't have a way to differentiate between the four countries....hopefully that makes sense! Anyway, I follow your KZfaq channel regularly and find your help invaluable. Thank you!!

I spent about 2 hours trying to figure out how to merge multiple tables, thank you soooooooooooo much!

Thank you! Thank you! Thank you! Worked like a charm!

Very useful Trick!

Great tip Alan, many thanksCheersMohideen

Excelent explanation!. Really usefull.

This is exactly the same way you do it in PBI!! Thanks.

Thank you it helped alot

Fascinating!

Extremely useful! Thanks :-)

Thank u Sir Now i am totally satisfied with this👍👍👍

Thanks for solution that I was searching for these days.

Great example! my version of Excel exactly - thanks

Excellent teacher.... Love you..... And thanks a lot

Thank u sir very interesting ,

I can't thank you enought for this tutorial.

Thanks very much, you are a hero, video is simple, briefly and clear.

This is very informational. Your teaching skill is phenomenal and I love your accent!

excellent exactly what I where looking for!

THANK YOOOOOOOOU 🖤 anyone using keynotes in Revit will understand how important and helpful this video is

This was incredibly helpful! I am having a problem with long account numbers turning into scientific notation (E+) though. I went to the original source data and added an apostrophe before the account numbers but the account numbers are still showing as E+ numbers. All cells are formatted as text. Any ideas for how to fix this?

Thank you very much, i have been looking for this for a while, you helped alot.

This was incredibly helpful! Do you have any videos on how to use this new master table to add new data to all the source tables? I'd like to use this consolidated view to add in quote and delivery information without having to go to each table individually. Thanks :)

Thank you for posting this. It solved an issue I was having and I am grateful indeed. Happy, Safe, and Prosperous New Year Sir.

This video should be kept in archives even after humanity ends. That's how much helpful it is !!

Many thanks

great well explained!

I know i am two years late, but you have saved my life - thank you!!

Thank you. Really easy to listen to. I struggle with my attention but your style and voice is engaging enough for me to take this information in. Thanks!!

Nice! Thanks

Thank you for this video helped s0lve a problem i had been battling with for days

This video was very helpful, thank you so much! one further question - is there a way to also add a column with the name of the worksheet or Table that the data are pulled from in the Append/combined version? Eg, an extra column that would show the row was from France or Spain, etc.

Thanks awesome

Subscribed

This is a great video thank you! I am curious if anyone know if there is a way to set this up so I can edit the information on the combined table and have it make those changes to the original tables? thanks so much!!

Thank you.

U are a life saver ❤️

Thank you

Very useful

Thanks so much! It worked hot-doggie!!!!

This was super helpful! Quick question: does this work when I send the file to other users?

im about to cry, thanks sooooooooooooooooooooooooooooo much

Hello, this is perfect, exactly what I was looking for. I am switching over from using Google Sheets and I am struggling a little with some of these minor things like combining sheets into 1 which is very simple in Google. I am wondering if there is a way to have your combined worksheet Refresh automatically or would i need to do a VBA code to do that?

Thank you for your video! I learned something again. My next problem, I have 60 sheets, very time consuming to convert them all to table and transform the data. Any help on this? Thanks.

Thank you so much for your videos, they have helped me immensely. Question regarding Append in Power Query. I have a template where I utilize a dropdown list in several columns. I noticed when I change the worksheet to a table, that option disappears. Is that correct? Is there a way to keep the drop down options? Thank you in advance for your help!

Thanks

Thank you for this video. I don't think the power query would be able to refresh the data if another worksheet was added, say for Sweden but VBA could would work, correct?

This is awesome, many thanks!

Great video. Thank you. I have a very large excel file that I would like to append small amounts of data to without opening the large file. I process monthly data and then would like to save it to a file that contains all data. Up to now, I open the large file and manipulate it, which is time consuming. Is there a way to do that?

Thank you! This is very useful. I have a question please. Is it possible to append data coming from other tabs into columns instead of rows? The presentation of my data is different in that each tab has data that needs to be presented in columns horizontally across one sheet rather than being appended as additional rows. I have total of 488 tabs relating to 488 sites so I need to present 488 columns in one worksheet. Can I achieve that with this query or can you recommend another? Thank you.

This is great video.Is it possible to download these worksheets for practice.

Great videos! Is there a way to find/search sequential numbers in one column?

u r great

Hello, do you have the files for this video? The tutorial has been very helpful, thanks!

Hi Alan, Thank You so much, very very useful video.

Sir please explain how to decrease excel memory

Hi Allan, this was really great! do you have any video with explanation how we could add new sheet to existing query? i am struggling right now with it and any support would be more than welcome. :-)

How to do the opposite of what you did here? Please show how to create different tab by country from "all country" tab. Thanks

thanks ra Alan

Subject is very good, let me test and come back to you, if successful and I expect, will save time especially when you did this with tenths of sheets hugely data 🙏

nice

Superb Is it possible to do same for multiple workbooks in a folder those are having multiple sheets. If new data added in existing workbooks or new more workbooks added, will it automatically update.

Great video - been scared to use PowerQuery. Can you please do a video on combining workbooks with PowerQuery? Thank you.

Thanks a lot. It is working great!. Question. How can i share this file with others. I tried copying the file into the others computers, but got an error refreshing data. Am I missing somthing? Thanks in advance

Hi Good explaination. Can u please share a video which shows how to append from different sheets of diffrent worksheets from different workbooks not from different worksheets from same work book.. imagine in your example if france sales data is in combine -sheets.xlsx and spain sales data is in Spainasales_combinesheets-sheets.xlsx

Is there any way that whenever we add new table in excel work it must include in the master query?

Thank you for this nice video , you have described how to combine files under each other having same column headers. But how can i combine files having same rows , beside each other ??

Hi, I have one inquiry, do we can apply the same steps if the sheets have not the same columns i.e. some of the sheets have more columns?

Thanks, how to add the country column in the appended list from the table names, so that it will be easy to see which sales is related to which country.

So today i learned something new about the Append Query feature. At first, i thought this was beneficial because it connects tables together. However, now i see it does a little bit more than just that. It creates value in the sense that once we have an append query set up, we could use it to save time. We could save time with it by removing the reoccuring step of having to rearrange a table due to differences in how columns are aligned. Again, this shows how using power query could be thought of as a more straight forward or user friendly approach to lookup. With a lookup function, i could have it extract data from specific columns. The issue with this is that if i am using column numbers as my return array, there's a very high risk of getting the wrong info returned to me in the event that the column number changes. however, when i use named labels, could i reduce the liklihood of incorrect data. It seems like the append query feature reduces that need to constantly have to rearrange the columns without having to learn the complex lookup functions.

so I all these worksheets are in different workbooks, then would you use same process..please advise.