How to Merge Excel Files (Without Using VBA) - 4 Easy Ways

hello and welcome to merge files my name is Jeffrey I'm glad you're here let's just jump right in now let's say we have data in multiple worksheets or multiple workbooks and we want to merge all that data into a single workbook well that's what we're going to do in this video using four different methods now before we jump in let's talk about the terminology when I use the word workbook I'm talking about the Excel file that workbook that you open it usually has a file extension called xlsx and you open it in Excel and that's the workbook now within each workbook we can have one or many worksheets and those worksheets are represented by the tabs at the bottom now what we want to do is talk about four different ways to combine or merge data from different worksheets and or different workbooks first we're going to move our copy then we're going to pay special values then we'll use power query from folder and finally we'll use power query from workbook all right first step is mover copy sheet and this would be a great option if there's just a few worksheets or if this is something you do one time and not on a recurring basis basically we have have three workbooks and we want to get the data from those three workbooks into a single workbook so the first thing we do is head to the first workbook we right click on the worksheet and say mover copy and then we can say we want to move this sheet to whatever book we want and then we also have the option to create a copy if we create a copy it means it's going to leave that original worksheet intact if we don't create a copy it's actually going to move that and so the original workbook will no longer have that worksheet in this case we're just going to create a copy so we check create a copy and then we click ok and now what we see is in our merge files workbook we have a new sheet called 100 and then we just rinse and repeat right click mover copy sheet make a copy in the merge files workbook and click OK right click move or copy sheet we want to send a copy to merge files and we create a copy and we click OK and that's it that's mover copy sheet at the end of the day we have the original workbooks with the original worksheets intact because we created a copy and we've merged those so that all of these worksheets are within a single workbook now later we're going to show an easy way to take this data and combine it so it's all in a single worksheet okay so the second option is a copy paste special values and this is a good option if this is a one-time project and there's not very much data to combine and what we can do with this technique is we can be very specific about where we paste the data we can paste it so it's stacked all on top of each other or we can paste each range into its own worksheet so we have some control there but there is a little tip that I want to point out regarding this which is pasting values okay in this case we want to paste it all into one combined worksheet all right so what we want to do is we want to head to the first workbook this is the 100 workbook we select any cell in the range and we use the control a as a quick way to select all we could also select that range manually if we prefer but a keyboard shortcut is just Ctrl a it's going to select all that Ctrl C copy and then we head to our destination workbook so one option is to do a standard control V paste when we do a standard paste we may also bring in things that we don't intend we might bring in data validation rules cell formatting formulas external links names and a bunch of other funky stuff so typically if I'm going to copy paste from one workbook to another rather than doing a standard paste I'm going to do a paste special values depending on the version of excel you have you could pay special values with control shift V instead of control V you just add the shift in and when that happens what we get are the values and that is beautiful because then we don't get all the extra cell attributes now if you don't have the control shift V option what you can always do is just cruise over to paste and then go to paste values okay and that will remove any formulas and other cell attributes okay and then it's just rinse and repeat control a control C and Ctrl shift V and here I don't really want this header data so I'm simply going to delete it last step is the 101 workbook so we head over there Ctrl a control C alt tab control shift V and let's go ahead and delete this as well cool all right let's go to the next option all right next step is using power query to pull all of the data from all the workbooks in a given folder and for this to work successfully those files need to be in the same folder and they need to have something in common whether it's the same sheet name or a table name so let's go ahead and check it out data get data from file from folder then we browse to the folder that contains the files we want and click open and we see a list of the files and what we do is we say combine and load two and then on this screen we identify which sheet name we are going to retrieve for all the files and that means that the sheets that contain the data that we want to bring in have to have the same exact name whether it's the same sheet name or the same table name then we click okay and now we're going to import the data into a single table we'll throw in a new worksheet and we'll click ok and there we have it we've obtained all the data from all of the worksheets that were named sheet1 throughout all of those workbooks that were in that folder and it's been merged into a single data table pretty cool right now here's the cool thing about that technique if we were to add a new file into that folder all we'd need to do is click the refresh all button and power query will retrieve the data that's in that new file as well and similarly if we take a file out of that folder and click refresh then the results are updated accordingly now the question is what happens if we want to pick and choose maybe all of the files are not in the same folder maybe the sheets don't have the same name so in that case what we want to do is a different Power query technique so let's get into that right now all right for this last technique we're also going to use power query but we're going to use a different option and this is going to be a good fit if we need to pick and choose for example maybe all the workbooks are not stored in the same folder and maybe the worksheets have different names and you get the idea so here what we're going to do is go to data get data from file and then we pick Excel workbook and then we browse to whatever folder and file that we want at this point we get a chance to look in and we get to say from this workbook here's the sheet name and by the way if there were multiple sheets we could select select multiple items and pick multiple worksheets in this case there's only one so we pick it load two and this time rather than loading it to a table we're going to create a connection only and click OK all right and then we just rinse and repeat get data from file from workbook we browse to any folder location that we want pick any workbook and then we have the option to then say well the worksheet name in this workbook is different it's 101 and then once again we load to a connection only query and click OK and then we do this one more time from file from workbook we browse to anywhere this workbook is we select it once we open it we pick the worksheets name and click load to only create connection then click ok now at this point we have have connections to three different workbooks that can be stored anywhere we've identified the specific worksheet name which could be different in each of the workbooks and now we want to show the results so if we want to show each of the tables in their own worksheet all we need to do is right click and say load to and then click table we could have also picked table originally and then we can click OK and that's how we could set it up so that the data from each of these tables goes into their own worksheets now what if we want all of these values combined into a single worksheet for that what we do is we need to create one more query so get data combine queries and we select append from here we select three or more tables and then we just pick these tables so we want to start with 100 101 102 you could have as many as you want and we click ok now in this case it's going to stack all the data on top of each other based on the column name so this is assuming that all of the column labels or the headers Department ID account ID account name are the same and that's how it knows how to line them up so once we have this in place then we just close and load two we select table new worksheet is fine and click OK and just like that all the data from all over the place is combined and stored in a single worksheet by the way if you use Excel and you'd like to save time be sure to check out the seven time hacks video it's a collection of seven time saving techniques that every Excel user should know and so those are four different way is to really merge data from different worksheets and different workbooks hey thanks so much for joining me hopefully this helps have a great day Leo is a production of excel University [Music]

Channel: Excel University

Views: 156,285

Keywords: excel, microsoft excel, power query, excel tips, jeff lennings, excel university, excel tutoring, excel formulas, excel functions, merge excel files

Id: jg3YyYfYIAA

Length: 9min 4sec (544 seconds)

Published: Wed Nov 16 2022