How to Combine Multiple Excel Sheets or Workbooks Seamlessly

hi I'm Jamie and welcome to teachers Tech today I  want to show you the best and quickest way to take   multiple sheets in Excel or workbooks and combine  them into one sheet we're going to go through this   step by step this is going to save you a lot of  time let's get started right away if you want to   follow along with today's tutorial I'll put a link  to this down below in the description so you can   download these files and follow along with me so  first of all let's take these four sheets that I

have of these different sales numbers and there's  about 880 total entries here and we're going to   bring them all together into one now first of all  I'm going to go to file and I'm going to create a   blank workbook to bring this into now the first  step we're going to go up to data here and we're   going to go get that data from a file and it's  going to be from an Excel workbook those four   sheets are in one workbook so I'm going to select  this and where do I have it saved well I just have   it on my desktop in this folder here and this is  the file I'm going to import this in when this   loads up you can see the four tables that are here  and if I click on each one you can get a preview I   want to bring them all in I'm going to just select  one of these and I'm going to show you how to make   sure that you're working with all of them so once  you have one of them selected go down to here and   click transform data this is where you're going  to start to shape how things look you'll notice   then that the power query editor opens up if I  look up at the top it says e sales that's because   that's the one I had selected but I want to have  everything so the first step is going to be is to   go back my applied steps to the source and one  thing nice about the power query editor every   time I do something it applies a step but we can  hit the X and go backwards so when I go back to   the source here I see I have the different sales  here I have North South West and East now I don't   need all these columns right here so I'm going to  go ahead and delete some I could just rightclick   and select multiple or remove or I could go  up and choose a column and this allows me if I   had lots I could unselect and just pick the ones  I want and hit okay so now I'm down two but within   within the data there's more there I just have to  expand it so if I click the expand here are all   the columns inside that data and I'm just going  to hit okay now if I just scroll across you can   see all the information is across in the columns  and if I scroll down north south west east but I   need to clean some things up here notice have some  null uh some empty blank ones I don't want them I   have date in here and I don't want these headers  across to be called just data column 2 a quick   way to change this is to just click right here use  first row as headers when I do that notice now it   just kind of copied those all up and you can make  some quick changes because in this case I don't   want it to be North sales so I'm just going to  double click in here and I'll just type all sales   for this example the next thing I want to caim  clean up is I can filter out so in this column   right here I pointed out the null and the date we  can go up to the filters and choose uncheck what   we don't want so I don't want the null in there  and I don't want that date in there because every   time I bring in a new entry that's the top header  and I don't want that uh I'm going to hit okay so   that's at the top of each table now if I look at  this if I go down you should see that I have 80   entries and I've cleaned this up if you want to  learn more about the power query editor I have   a more detailed tutorial about that I'll put the  link right up in above in the card and down below   uh in the description so this is clean enough for  what I want I kind of have my 80 entries I'm going   to go up to the top left hand corner right here  and just click close And load so here's the data   inside that Excel spreadsheet it's on E sales  I could change this if I want so I just have   to double click and again I could put all sales  in here if I scroll down we have South we have   West East all into one so it took all those and  combined it and that was a very quick way to do   it if I look over at the query over here it says  uh 80 rows loaded if I double click on this it   actually opens back up into the power query editor  but again check out my other tutorial about that   but what happens if we get more data let me show  you how we can refresh this and this will update   now I'm just back in the original file that I  showed you at the very beginning that we merged   all these from so let's say in North sales I'm  going to go and just uh copy some of these down   so I'm just going to copy about four more entries  and put it in I'm going to go ahead and hit save   now when I just move back to the other sheet that  we have opened right here it's still at 80 notice   but if I go back to it right click and hit refresh  look now it says 85 and we can do this with just   adding another sheet as well so let's say now  we want to combine the data of four different

workbooks into one the process is very similar  but before we start doing this I just want to   point out with these different workbooks and I  have them open right here you need to make sure   that the schema is set up the same so you need  consistent column headers data types and number   of columns you can have the columns in different  order but they have to be named the same make   sure that your tabs down below all have the same  name as well so you could run into problems if   the different if there's some different names uh  on each of these let's go ahead and open a blank   Excel workbook inside my blank workbook I'm going  to go back to my data tab here go to get data from   file I'm not going to go from workbook this time  I'm folder when this opens up I   select the folder that contains my workbooks make  sure in that folder you only keep the workbooks   that you're using don't add other things into it  I'm just going to click open and now it shows me   the different files that are in there so you can  see there's East sales north south and west uh to   keep things simple this time I'm just going to go  to combine down below combine and load so this is   going to load those four things up into uh this  sheet that we had before I just have to select   sales and then I go ahead and hit okay this brings  it into my uh workbook here the query is created   and if I scroll down you can see uh that all the  information I have all my entries in it so what   happens if things get updated and you need to  add another sheet so we have four uh so this is   connected to that regions folder if I go and grab  this and just drop it in here so now I have this   Central sales in here and everything is set up  in that one the same if I just Cod it here again   under data you'll see that there is a refresh all  and if I refresh it went from 80 to 100 rows so   just like that I could add another uh workbook  and but Mak making sure that the schema is the   same it will pick that up when you refresh it and  you have all that data inside this one sheet as   well I hope these two methods that I showed you  will work smoothly and quickly for you thanks   for watching this time on teachers Tech I'll see  you next time with more Tech tips and tutorials