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