MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Merge Workbooks


April 17, 2017 - by Bill Jelen

Merge Workbooks

How to merge data from several Excel workbooks. Say that you have a folder full of Excel files. Each has one worksheet with a differing number of rows. You can import all of the files in the folder to a single Excel workbook.

Watch Video

  • Revisiting the Clean Data with Power Query podcast # 2037
  • Power Query can now combine all Excel files in a folder.
  • Improved: They automatically delete the headers from all but the first file.
  • You choose which file to use as the Sample file.
  • Choose which Worksheet, Table, Named Range to import
  • Use the query editor to do any transforms
  • Close & Load to combine all files
  • Later, refresh the query to have it update

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2077 merge workbooks hey welcome back to
  • MrExcel Podcast I'm Bill Jelen just
  • 40 episodes ago talking about cleaning
  • data with power query how to take a
  • whole bunch of CSV files and bring them
  • into a single excel file well this
  • feature has just gotten dramatically
  • better so what I have here today is a
  • folder and in this folder are a whole
  • series of monthly Excel files 22 of them
  • for almost two years I need to combine
  • all these files now these are all have
  • the same column layout all right so they
  • all have nine columns all have the same
  • headers and so on alright so we're gonna
  • combine all this without a macro if
  • you're in Excel 2016 you're gonna come
  • to the data tab to get in transform data
  • if you're in Excel 2010 or 2013 you're
  • gonna go out and download a great
  • product for Microsoft called power query
  • you can download the whole thing and
  • have it installed in less than a minute
  • alright so we're gonna come here to get
  • data from a file but not from a single
  • workbook we want to get it from a folder
  • like this alright here they are they
  • asking you to browse to the folder but
  • I'm just gonna click in there and paste
  • ctrl V because I pasted the directory
  • click OK
  • so they showed me a preview of the data
  • I have I open this little combined
  • drop-down here and choose combine and
  • edit all right and then the step here is
  • that we're gonna choose one file that's
  • a good representative file alright and
  • they're just choosing the first one that
  • happens to be alphabetically it doesn't
  • matter which one you choose and then
  • this sample query
  • that we're building from the first file
  • that we choose in this case I want sheet
  • 1 if you have multiple sheets you could
  • say sheet - or she called income
  • statement or the name range or a table
  • and they show me what it looks like and
  • I click OK now there is a lot going on
  • in this screen what they've actually
  • done here is they've created a sample
  • query same query and then created that
  • as a function and run that function
  • against everything in the folder they
  • add a new column that tells me where the
  • file came from and in this particular
  • case I already know where it came from
  • because we have a date filled out here
  • so I don't need that I'm going to right
  • click and remove
  • and maybe there's some data in there
  • that I don't need maybe this data is
  • going to go to a customer saw I don't
  • want to show the profit so I can remove
  • that column and even remove the cost
  • column and maybe there's some annoying
  • things like you know they use bad
  • headers and I want to rename these
  • headers so right-click and rename we'll
  • call it QT Y for quantity maybe things
  • are in the wrong sequence maybe I want
  • to take this item and move it to another
  • spot
  • all right just all these little annoying
  • things that you might possibly have and
  • of course over here they're doing all
  • the applied steps and then you can
  • continue to do any power query things
  • that you need to do in this case it's
  • just simple I just want to get the data
  • in so I'm going to close and load a few
  • seconds go by and there it is I have my
  • final let's just sort this here by date
  • you see there we go from January of 2018
  • all the way out through October of 2019
  • how cool is that now here's the amazing
  • amazing thing about this let's say that
  • something changes in our folder so back
  • out here in the month files I get two
  • new files pasted so now there's two more
  • records here two more Excel files that I
  • have to combine just come back to my
  • excel file which currently has five
  • hundred sixteen rows find the months
  • files and click the refresh button and
  • that quickly I now have the new records
  • from November and December power query
  • is an amazing new tool free for Excel
  • 2010 free for Excel 2013 it was
  • beautiful when they could combine CSV
  • files but now that they can combine
  • Excel files as well just phenomenal so
  • I've updated my book power excel with
  • MrExcel the 2017 edition includes
  • power query power bi and all the new
  • tools check it out click that I on the
  • top right hand corner all right wrap up
  • of this episode we're revisiting the
  • clean data with the power query podcast
  • number 2037
  • where we combine multiple CSV files we
  • can now use that same tool an improved
  • version of that tool to combine all
  • Excel files in a folder another
  • improvement the change from 2037 they
  • automatically delete the headers from
  • all but the first file
  • and promote those headers in the first
  • file you choose which file to use as the
  • sample file in my case I use January
  • work choose which worksheet table are
  • name range to import and then do any
  • transforms in the query editor we click
  • close and load it will combine all the
  • files and then if you get more files
  • dropped into the folder or some files
  • taken away or some files changed just
  • refresh the query and it will go do all
  • of those steps again power query and
  • absolutely amazing tool I want to think
  • we're stopping by will see you next time
  • for another net cast from MrExcel

Download File

Download the sample file here: Podcast2077.xlsm

Title Photo: Free-Photos / Pixabay


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.