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

@Computergaga

4 жыл бұрын

And you have made mine. Thank you Kratika.

@mayascript4229

4 жыл бұрын

@@Computergaga Flirting continues 😁😂

@ronaldreddy63

3 жыл бұрын

Also check out excellisfun KZread channel. I reckon its one of the best

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.

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.

@Computergaga

Жыл бұрын

Awesome! Thank you, Charles.

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.

@Computergaga

Жыл бұрын

You're welcome.

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

@Computergaga

5 жыл бұрын

You're welcome.

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!

@Computergaga

2 жыл бұрын

You're very welcome! Happy to help, LaVera.

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

@Computergaga

5 жыл бұрын

Great to hear. Thank you.

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 !!!

@Computergaga

3 жыл бұрын

Glad I could help Iscia.

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

@Computergaga

5 жыл бұрын

Thank you Salim.

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

@Computergaga

5 жыл бұрын

You're very welcome Anne. Great to hear. I just uploaded a new video about appending data from multiple worksheets - kzread.info/dash/bejne/kamFptqzfLO0ktI.html

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.

@Computergaga

3 жыл бұрын

Brilliant! Happy to be able to help.

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

@Computergaga

5 жыл бұрын

Você é muito bem-vindo. Obrigado pelos comentários.

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

@Computergaga

5 жыл бұрын

My pleasure. Thank you.

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

@Computergaga

5 жыл бұрын

You're very welcome. 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!!!!

@Computergaga

3 жыл бұрын

Awesome! Thank you, Roslan.

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

@Computergaga

Жыл бұрын

You're so welcome! I am glad that it helps.

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

@Computergaga

5 жыл бұрын

My pleasure.

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

@Computergaga

3 жыл бұрын

You're very welcome 👍

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.

@Computergaga

4 жыл бұрын

Great! Happy to help.

Exactly what I was looking for! Thx a lot!

@Computergaga

4 жыл бұрын

Excellent. My pleasure Chris.

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

@Computergaga

5 жыл бұрын

Thank you Mudasir.

Well narrated! Incredibly helpful.

@Computergaga

4 жыл бұрын

Awesome! Thank you very much Siri.

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

@Computergaga

Жыл бұрын

You're very welcome! Thank you for your comments.

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

@Computergaga

4 жыл бұрын

Wow, thank you 😊

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

@Computergaga

4 жыл бұрын

You're very welcome. Thank you Daysi.

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

@Computergaga

3 жыл бұрын

Glad it helped, Mike.

Thanks for solution that I was searching for these days.

@Computergaga

3 жыл бұрын

You're welcome. Happy to help.

Excellent instruction, Thank you!

@Computergaga

4 жыл бұрын

Thank you very much Donna.

So good! You talk slowly which is awesome!

@Computergaga

5 жыл бұрын

Thank you Izabel.

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!!

@Computergaga

7 ай бұрын

Great to hear. Thank you very much for your comments.

Still relevant over a year later! Thank you!!

@Computergaga

4 жыл бұрын

Excellent!

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

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.

@Computergaga

6 жыл бұрын

Thanks Bart.

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

@Computergaga

4 жыл бұрын

Great to hear. Thanks Carlos 👍

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

@Computergaga

2 жыл бұрын

My pleasure. Thank you 👍

Extremely useful! Thanks :-)

@Computergaga

4 жыл бұрын

You're welcome Alex. Thank you for watching.

I can't thank you enought for this tutorial.

@Computergaga

Жыл бұрын

You're very welcome!

Excelent explanation!. Really usefull.

@Computergaga

4 жыл бұрын

Thank you Miguel.

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 KZread channel regularly and find your help invaluable. Thank you!!

Great tip Alan, many thanksCheersMohideen

@Computergaga

6 жыл бұрын

Cheers Mohideen.

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!!

Very useful Trick!

@Computergaga

6 жыл бұрын

Thanks Learn Excel.

Great Knowledge, Thank u so much.

@Computergaga

3 жыл бұрын

You're welcome. Thank you.

Fascinating!

@Computergaga

5 жыл бұрын

Thanks M. Raed Al Lulu.

Great example! my version of Excel exactly - thanks

@Computergaga

3 жыл бұрын

Thank you, Michael.

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

@Computergaga

Жыл бұрын

Awesome! You're very welcome.

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

@Computergaga

4 жыл бұрын

You're welcome Larah, thank you.

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

@Computergaga

Жыл бұрын

You're welcome. Thank you very much.

excellent exactly what I where looking for!

@Computergaga

Жыл бұрын

Awesome! Happy to help.

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

@Computergaga

4 жыл бұрын

Excellent! My pleasure Fuad.

Thank u sir very interesting ,

@Computergaga

5 жыл бұрын

You're welcome Suresh.

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

@Computergaga

3 жыл бұрын

😂 Thanks, Rahul.

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

@Computergaga

4 жыл бұрын

Never too late to save a life. Happy to help.

Thank you it helped alot

@Computergaga

5 жыл бұрын

Fantastic! Great to hear.

great well explained!

@Computergaga

2 жыл бұрын

Thank you, Francis.

Many thanks

@Computergaga

5 жыл бұрын

You're very welcome.

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

@Computergaga

3 жыл бұрын

Awesome!! Happy I was able to help.

U are a life saver ❤️

@Computergaga

3 жыл бұрын

😊

im about to cry, thanks sooooooooooooooooooooooooooooo much

@Computergaga

4 жыл бұрын

You're very welcome Sayed.

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

@Computergaga

Жыл бұрын

😊😂 awesome!!

Nice! Thanks

@Computergaga

3 жыл бұрын

You're welcome. Thank you.

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?

Subscribed

Thanks awesome

@Computergaga

4 жыл бұрын

Thank you Syed.

Thank you.

@Computergaga

Жыл бұрын

Thank you, Kamana.

Thank you

@Computergaga

4 жыл бұрын

You're welcome Shubham.

Very useful

@Computergaga

5 жыл бұрын

Thank you Waris.

This is awesome, many thanks!

@Computergaga

5 жыл бұрын

You're very welcome Joshua.

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 :)

@Computergaga

4 жыл бұрын

Sure. You would have other queries/tables reference this master table (lookup referencing queries in Power Query). And these queries can then be filtered to only show what you want. There is also the new FILTER function that can help with this in Excel 365 - kzread.info/dash/bejne/nZiglc-Hddyvgqg.html

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?

Thanks

@Computergaga

4 жыл бұрын

You're very welcome.

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.

@Computergaga

4 жыл бұрын

You're very welcome Heather. You might like this technique more as it includes the names column - kzread.info/dash/bejne/kamFptqzfLO0ktI.html

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.

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!!

@Computergaga

3 жыл бұрын

Thank you. You would want to reverse this process if you need it the other way around. You can create multiple tables/queries that reference the big query. Then filter each one so that it only shows the info you want. Any changes to the big one, when refreshed would then update the other queries.

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?

@Computergaga

4 жыл бұрын

You would need the VBA I think to refresh them automatically Megan.

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

@Computergaga

Жыл бұрын

Sure. This example works as the data is within the same sheet. If the sheets were from an external workbook, then we would need to consider the file path and access when sharing.

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.

@Computergaga

4 жыл бұрын

Yes, instead of an Append Query, you want a Merge Query.

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!

@Computergaga

Жыл бұрын

You're very welcome, Kathleen. The dropdown options should remain. I believe you are referring to Data Validation drop downs and they should remain.

thanks ra Alan

@Computergaga

4 жыл бұрын

My pleasure Raj.

u r great

@Computergaga

3 жыл бұрын

Thank you, Wesam 😊

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?

@Computergaga

6 жыл бұрын

Absolutely correct. VBA is more than capable of that. Power Query is also capable, but not the approach shown in this video. This performs it on those specific sheets.

@KineticGas

5 жыл бұрын

@@Computergaga do you have a y vid that shows power query for when new sheets are added, I plan to use method as a master sheet for 20 sheets in one workbook that I have, each sheet represents a month/year and each sheet has a running balance, if I were to consolidate all those sheet into a master sheet using this method it could mean I could make up a another sheet that shows a balance by always retrieving the last numbers on a certain row where the running balance displays in the cell row. Only issue I see is when I need to add a new sheet for the new month/year. Would appreciate some help with this.

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

@Computergaga

4 жыл бұрын

You're very welcome Akash. Thank you.

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

@Computergaga

4 жыл бұрын

Thank you. I don't have this file anymore, but you can just create a few sheets for practise. They don't have to be large, just a few rows to get the techniques down.

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

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

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.

@geethareddy2213

3 жыл бұрын

Same issue for me.Please let me know if you find the solution

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

@Computergaga

4 жыл бұрын

Thank you, Nemanja. I don't fully understand your question. I cannot picture the scenario.

@nemanjailic8950

4 жыл бұрын

@@Computergaga For example, you have 1000 numbers in one column and you want to analyze them to find some patterns, such as consecutive numbers, duplicated numbers, etc.

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.

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. :-)

@Computergaga

2 жыл бұрын

Thanks, Milan. You can edit an existing Append query to add a sheet. Open the PQ Editor and select the query. In the Applied Steps on the right, click the gear icon next to the Append step to edit it and add the additional sheets to append. Hope that helps.

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 🙏

@Computergaga

4 жыл бұрын

Yes it is very helpful. Can speed up regular processes.

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

@Computergaga

Жыл бұрын

Thank you. You can copy the file just fine. The issue with refreshing is probably an issue with the filepath that gets coded into the Source step. The recipients of your file would need to edit this to their filepath to the files.

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

@Computergaga

3 жыл бұрын

Thank you, Debbie. Don't be scared. Power Query is fantastic. I have a couple of videos on your suggestion. Here is one - kzread.info/dash/bejne/a6Ce09Gzf7mrh7w.html

@debbiefagan3383

3 жыл бұрын

@@Computergaga Thank you so much. I will take a look :)

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.

@Computergaga

4 жыл бұрын

Thank you. I have this video on importing multiple files from a folder - kzread.info/dash/bejne/a6Ce09Gzf7mrh7w.html

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

@Computergaga

4 жыл бұрын

Great to hear Flor. I do not have the files, sorry. You ca use any small workbook to practise though.

Sir please explain how to decrease excel memory

nice

@Computergaga

4 жыл бұрын

Thank you, Vijay.

Great tutorial, where Can i find the data ?

@Computergaga

Жыл бұрын

Thank you, Pierre. The data is not provided for this video. I just did a search on my system and looks like I've removed the file. But you can practise this with any sheets. Any dummy data would work for practise.

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?

@Computergaga

5 жыл бұрын

On one of the steps you have the opportunity to pick what columns, so you could not show the extra ones. Equally you could decide to. The tables which do not have these extra columns will have black cells in that column.

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

@Computergaga

5 жыл бұрын

Sure, we could use Excel.CurrentWorkbook. This will bring all the tables of the workbook and append them. You can then use the query to filter out any that you would not want included.

same method can be applied to combining multiple datasource text or csv files together using connections.

@Computergaga

Жыл бұрын

Absolutely. Once they are connected and we have them as a query, anything can be appended. Thanks, Harjit.