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 of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.