Combine Multiple Worksheets into One with Append Query - Excel

hello and welcome to this video tutorial from computer Garga comm and in this video we are going to look at how to use the append query in excel to combine multiple worksheets into one now this is one of the most common questions I get on my training is how to combine or to consolidate multiple sheets and what we're going to see in this video is one of the best methods there are many techniques we can use but this is one of the best so in this example I've got 4 worksheets each one with a country name at France Spain you came Italy and I'm imagining that we receive this sales data from those regions it may be from an external source these these tables are connected to that source so they're updating regularly that we would like to consolidate them into one sheet for our analysis now on the sheets called Spain UK Italy they are all formatted as a table so I can see I have two design tab at the top here and this table is called Spain for obvious reasons and that's already been done and normally when you receive data from an external source in the more recent versions of Excel they are automatically put into these tables and that's not necessary but there is an advantage to that they all more dynamic and they also expand and a few other benefits that this videos not going to be long enough to talk about now the one called France at the moment is not because I wanted to demonstrate how you can do that so if I just click somewhere in this I was going to call it a table then but in this list and even the Home tab if I click format as table I choose a format it's got headers yes and then I'll go in name the table in the top left here as France so to repeat it's not important that they're in a table as in light necessary we can combine these sheets with data from a named range or from just a normal range of sales but there are you know a few advantage to the table and it becomes recommended so that's all done let's get on with the combining so firstly we will need to load each of these four tables as a query we're going to have to create a connection and then we can run the append query on them so please don't be misled into thinking we will need to do all the steps of this video every time we want to do this it depends how you get your data but in this video we're imagining that these four tables are connected to an external source and they are updating as data changes and because of that our pinned query can also be refreshed with the click of a button so over the long term this is going to be very simple and very quick to update it will not be as long a process as it will be here so let's click the data tab at the top where I have the get and transform data group and I will click on from table range in there because we have this in the table and it will load the query editor now if you're on a previous version of Excel you may see this referred to as power query your data tab might look a bit different to mine it's an area of Excel that's changed a lot over the recent years of recent versions so I'll try and put a link in the description of this video to a page and a Marx website where I explain about power query and about getting transform and how it's changed and what it is because the marvelous tool but please bear in mind any subtle changes in the appearance of our screen right now in the query editor though I can see it's a France on the right I've got my clothes and load to button I'll used a drop-down and I'm going to select close and load - and I'm just going to load it as a connection so I'll choose only create connection I'll click OK that establishes that connection net query and it will appear on the right hand side there it is and I'm going to repeat these steps for Spain UK and Italy okay so I've got my four connections loaded on the right hand side you can see them in the queries and connections at pane here so now also on the data tab if I click on get data combined queries and I'm going to go for the append query that will open up a window and from here we're going to select each of the tables that we want to combine now we've got four tables in this demonstration so I'll need to select three or more tables and then we get this really nice at window to load them in it wasn't quite as simple as this in versions gone by I can simply slipped each of these hold down the ctrl key on my keyboard here click on add to say that I want all four of those and then click OK that's now going to load up the query editor for this pendant on the right hand side we've got a name for this query so I'm going to change that name from append 1 and call it and let's call it all countries we've got the opportunity here if you wish to format any columns or change any column headers anything that you might not be happy with but with this data coming from those four tables I'm gonna make the assumption that it's all okay and it's all done for me right now I shall just choose close and low - I'm going to load this into a new worksheet in this workbook just getting the data now to come into it and here it is we have a table so I could always rename that table if a wish to have used the name all countries from my query and that is all four tables appended to each other so you can see on the right hand side how its 426 rows that they've loaded if I go to sound like France for a moment I can see there are a hundred and eighty five rows and Spain there's fifty five rows you see this is a combination of all of them together so now we've done that we can easily go and perform any analysis that we need to do if I can just demonstrate for a moment that we have the French one here we have France so what did I say I said France had 185 rows so if I scroll down for a moment for France here we go 185 rows on there so in our all countries list if I come down here the last French one is ten free free one there it is ten free free one and then it starts with a cell for out bet lager one 1:03 seven which is the first one for spain now if i just go to the front sheet for a moment i'm going to put a pretend new order at the bottom let me put one Oh free free - I'll imagine that happened on the same date as the one above I think that will make life easy the product can be Scottish long breads it can be for 100 pounds whatever it may be and and let's make the salesperson me a new salesperson I've just joined a press enter.now the table that we put those in have expanded automatically that was part of the joy of using the tables if I now go back to sheet one the combination there's the end of the French one is a start of the Spain one our new one is not there but if I click on the day 2 tab and refresh all in it comes and there is the order that we just added so that's what a member being able to update these with a click of a button in the future as those four changes are those four tables Sariah changing and they're updating we can get our append query how kind of consolidated worksheet to also update in the blink of an eye with that fresh data and we can now go and use our formulas or go in insert a pivot tables and do any extra analysis we want to do with this combination of the four sheets I can come in here now and do a simple analysis on sales rep using our pivot table skills or a formula skills or whatever you've got if there's me performing badly in the salesperson list so this is one of the best techniques we can do now to combine worksheets into one there are other approaches there is there is always VBA as an option as a consolidate data feature of Excel there's vlookup you know there are options available but this is right up there now with a way of speeding up and also simplifying you know without a formula insight here is quite simple to perform at this commonly asked at Technic I hope you found this video useful please check out some that other videos on a youtube channel and come check us out at computer google.com you

Channel: Computergaga

Views: 253,514

Rating: 4.839921 out of 5

Keywords: Power Query, Combine Worksheets, Consolidate Worksheets, Append Query, Append Worksheets, Computergaga, Get & Transform, combine multiple worksheets into one, power query append, combine data in excel, append worksheets into one, append worksheets in excel, excel combine data from multiple sheets, excel power query, excel get and transform, power query tutorial, data analysis, excel 365

Id: oeQPtbFNYIw

Length: 11min 11sec (671 seconds)

Published: Fri Jun 01 2018