MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Combine 4 Sheets


November 20, 2017 - by Bill Jelen

Combine 4 Sheets

Excel combine several worksheets into a single worksheet. Each worksheet might have a different number of records from day to day, so formulas are not the way to go. Instead, a little-known tool called Power Query will let you merge the data simply and quickly.

Watch Video

  • Doug: How to combine four sheets where each has a different number of rows?
  • Use Power Query
  • Format each worksheet as a Table with Ctrl + T
  • Rename the tables
  • For each table, new query From Table. Add a custom column for Region
  • Instead of Close & Load, choose Close & Load to... Only Create a Connection
  • Use New Query, Combine Query, Append. 3 or More Tables. Choose the Tables and Add
  • Close and Load and the data appears on a new worksheet
  • For the one table with extra column: the data shows up for only that sheet's records
  • For the one table where the columns were in the wrong order: Power Query worked correctly!
  • Easy to Refresh later

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2178 merge for worksheets today's
  • question via YouTube from Doug has this
  • situation we has four sheets where each
  • sheet is a region with sales data and
  • the number of records change monthly and
  • right now Doug is trying to use formulas
  • but when the number of rows changed that
  • becomes a nightmare right so I said hey
  • Doug can we use power query if you have
  • Excel 2010 or Excel 2013 it's a free
  • download from Microsoft or it's built
  • into 2016 and office 365 it says yes all
  • right so here's what we have we have
  • four reports the central region the east
  • region the south region and the west
  • region and each one has a different
  • number of records like here in the South
  • Region we have 72 records in the east
  • region 193 records and this is going to
  • change right
  • every time we run this report will have
  • a different number of records now I've
  • made some assumptions here first that
  • there's not a column called central and
  • then also I'm gonna be completely evil
  • here and take the South Region I want to
  • try and screw it up I'm gonna take the
  • profit column cut it and paste it how to
  • reverse those and then all right so we
  • have one where the columns are reversed
  • and then another one where we're gonna
  • add an extra column gross profit percent
  • so this is gonna be profit divided by
  • revenue in an ideal world these are all
  • shaped exactly the same but as I learned
  • recently I was doing a seminar down in
  • North Carolina if they're not all right
  • someone had a situation well you know
  • halfway through the year things changed
  • and they added a new column or move
  • columns around we were really happy to
  • see that power query was able to deal
  • with this all right so we're gonna take
  • each of these reports and make it into
  • an official table format as table so
  • that's control tea or you could use a
  • name range for me control t is the
  • easier way to go and what they do here
  • is they call this table one I'm gonna
  • rename this to be called central and
  • then we go on to East control T click OK
  • and this is gonna be called East now hey
  • on an earlier podcast I showed how if
  • these have been four separate files we
  • could have used power query just to
  • combine files but that doesn't work when
  • they're four separate or four worksheets
  • in the same same book so well there we
  • go and then
  • like this control-t a little tedious to
  • set this up the first time but boy
  • there's gonna be awesome
  • every time you have to update this later
  • on so we're going to do is we're going
  • to choose this first table central
  • region and if you're in 2010 or 2013 and
  • downloaded power query you're gonna it's
  • gonna have its own tab but in 16 in
  • Excel 2016 is actually getting
  • transformed which is the second group in
  • office 365 is now getting transform
  • which is the first group and so we're
  • gonna say they were going to create this
  • data from a table or range all right and
  • there is our data now we don't have a
  • region field and the combined files
  • would have added the region field so in
  • this case I'm just gonna add a column a
  • new custom call the headings gonna be
  • region and this one is going to be what
  • was this central right like that
  • click OK alright now here's the
  • important part when we're done this with
  • this we're gonna go home not choose
  • close and load we're gonna open the
  • drop-down close and load to only create
  • a connection click OK
  • perfect we have our connection only now
  • the next thing we have to do is repeat
  • these steps for the next three regions
  • and now that would be really a bit
  • boring to you so let's just speed up the
  • video to 10x for this
  • alright there we are for connections set
  • up now here's where we're going to do
  • the magic I'm gonna insert a new blank
  • worksheet and I'm gonna say get data
  • combine queries and I want to append two
  • queries from this workbook and I'm gonna
  • say three or more tables and the
  • available tables are Central through
  • West click Add BAM click OK and then we
  • can close and load and what we have here
  • is we have a superset of all of the
  • records in all of the tables all right
  • and where we tried to screw it up where
  • I purposely tried to screw up by
  • reversing cost of goods sold and profit
  • down in what was that that was Central
  • East South in the South Region I'll just
  • go check those right and it looks like
  • yeah generally feels right they used the
  • heading to figure it out because the
  • profit is always higher than cost of
  • goods sold and so that worked and then
  • down here in the West where we added
  • gross profit percent we actually get
  • that data for the tables that had it and
  • for the tables that didn't have it we
  • just get null which is perfect alright
  • now duck
  • here's what you're gonna do so the next
  • time that you have some more data and
  • I'll just let's create some some extra
  • records here we'll just add some ABC
  • with a date of today and all retail and
  • it's called Doug's new records and just
  • some garbage out here let's just put in
  • a hundred all the way across in the
  • interest of time okay so now because
  • this is a table the table automatically
  • expands to the new records which is
  • beautiful had they been named range I
  • would have had a redefine that's why I
  • really like the table instead of the
  • name range but we come back here to the
  • resulting workbook with 563 rows loaded
  • and I click refresh
  • and bam now I have 572 Rose loaded
  • including let's see if we can find them
  • in here
  • Doug's new records right there at the
  • end of the South Region
  • isn't that just an awesome awesome way
  • to go yes it definitely takes longer to
  • set up the first day we're up to seven
  • minutes already if I hadn't sped that up
  • to 10x but once it's set up now life is
  • gonna be super super easy from here on
  • out way this is where I usually promote
  • my own book but no this time let's talk
  • about this awesome book Emma's for data
  • monkey by Ken polls in Miguel Escobar
  • everything I learned about power query I
  • learned from this book look at the eye
  • on the top right hand corner for more
  • information about that book all right
  • wrap up topics in this episode Doug how
  • to combine four sheets where each sheet
  • has a different number of rows we can
  • use power query make sure to format each
  • worksheet as a table with ctrl T or use
  • named ranges but I prefer ctrl T rename
  • the tables from each table choose new
  • query from table add a custom column for
  • a region and then instead of close and
  • load choose close and load to only
  • create a connection do that for all four
  • queries and then new query combined
  • query append choose three or more tables
  • choose the tables and click Add
  • now some older versions of power query
  • you couldn't do three or more tables you
  • have to do two and then do another query
  • to add the third one and then do another
  • query to add the fourth one either way
  • it would be more hassle that way I'm
  • glad that they added the three or more
  • tables close and load this time close
  • and load to the worksheet and and then
  • later on if you add more data to any of
  • the four tables just go back to your
  • query and click refresh and you're good
  • to go
  • power query and amazing new feature from
  • Microsoft I love it I thank Doug for
  • saying that question it well thank you
  • for stopping by we'll see you next time
  • for another net cast from MrExcel

Download File

Download the sample file here: Podcast2178.xlsm

Title Photo: GLady / Pixabay


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

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.